Menu
I would like to get count of specific records. So my query will look like the following...
SELECT ID, NAME, (SELECT...) AS UserCount // Stmt1 FROM MyTable The issue is that, 'Stmt1' is a complex statement and it cannot be written as innerquery. Well, I can use functions, but the statement includes 'CREATE TABLE' so I get the following error message
Cannot access temporary tables from within a function.
What is the best way to accomplish the task ?
334
So in such cases, SQL Server provides us with a feature called temporary tables which can be created at runtime and used to store the data temporarily. They can be used to create a workspace for processing the data inside stored procedure or functions. They can be used for complex joins.
You cannot use TEMP table (with # sign) in functions. But you CAN use Table variable (Declare @vTable Table (intcol int,...)) in functions. The limitation is that you CANNOT create index on table variables.
Temporary tables are stored in tempdb. They work like a regular table in that you can perform the operations select, insert and delete as for a regular table. If created inside a stored procedure they are destroyed upon completion of the stored procedure.
This means you can create temporary and non-temporary tables with the same name within the same schema. However, note that the temporary table takes precedence in the session over any other table with the same name in the same schema.
You can use user defined table type to solve your problem.
You just create a table variable like
CREATE TYPE [dbo].[yourTypeName] AS TABLE( [columeName1] [int] NULL, [columeName2] [varchar](500) NULL, [columeName3] [varchar](1000) NULL ) GO and you can declare this table variable in your function like
CREATE FUNCTION [dbo].[yourFunctionName] ( @fnVariable1 INT , @yourTypeNameVariable yourTypeName READONLY ) RETURNS VARCHAR(8000) AS BEGIN SELECT ................. FROM @yourTypeNameVariable WHERE ........ RETURN @r END On your procedure you can declare your table type like
DECLARE @yourTypeNamevaribale AS yourTypeName And you can insert values to this table like
insert into @yourTypeNamevaribale (col,col,..)values(val,val,..) pass this to your function like
dbo.yourFunctionName(fnVariable1 ,@yourTypeNamevaribale ) please go for this method, thank you
115
Yes you can not use #temp table.
As you are using SQL Server 2008, why don't you use table variable instead of #temp tables? Give it a try.
33
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
