StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You can't access Temporary Tables from within a SQL Function. You will need to use table variables so essentially:</p> <pre><code>ALTER FUNCTION FnGetCompanyIdWithCategories() RETURNS @rtnTable TABLE ( -- columns returned by the function ID UNIQUEIDENTIFIER NOT NULL, Name nvarchar(255) NOT NULL ) AS BEGIN DECLARE @TempTable table (id uniqueidentifier, name nvarchar(255)....) insert into @myTable select from your stuff --This select returns data insert into @rtnTable SELECT ID, name FROM @mytable return END </code></pre> <h1>Edit</h1> <p>Based on comments to this question here is my recommendation. You want join the results of either a procedure or table valued function in another query. I will show you how you can do it then you pick the one you prefer. I am going to be using sample code from one of my schemas, but you should be able to adapt it. Both are viable solutions first with a stored procedure. </p> <pre><code>declare @table as table (id int, name nvarchar(50),templateid int,account nvarchar(50)) insert into @table execute industry_getall select * from @table inner join [user] on account=[user].loginname </code></pre> <p>In this case you have to declare a temporary table or table variable to store the results of the procedure. Now Let's look at how you would do this if you were using a UDF</p> <pre><code>select * from fn_Industry_GetAll() inner join [user] on account=[user].loginname </code></pre> <p>As you can see the UDF is a lot more concise easier to read, and probally performs a little bit better since you're not using the secondary temporary table (performance is a complete guess on my part).</p> <p>If you're going to be reusing your function/procedure in lots of other places, I think the UDF is your best choice. The only catch is you will have to stop using #Temp tables and use table variables. Unless you're indexing your temp table, there should be no issue, and you will be using the tempDb less since table variables are kept in memory. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload