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 ?
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 )
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.
I came across this post as I started using table variables and switched to temporary tables for performance reasons only to find temporary tables couldn't be used in a function.
I would be hesitant about using table variables especially if you are playing with large result sets, as these are held in memory. See this post...
http://totogamboa.com/2010/12/03/speed-matters-subquery-vs-table-variable-vs-temporary-table/
Other alternatives would be..
In 99,99% of cases there is no need for any tricks with temp tables or subqueries, but use aggregation functions like COUNT, SUM or AVG in combination with OVER clause and (often) PARTITION BY.
I am not sure what the OP tried to achieve but I assume that the UserCount is somehow related to the values in MyTable. So there must be a way to join MyTable to whatever table that produces UserCount.
The most simple example is to show all users and the total number of users
SELECT id
, name
, user_count = COUNT(*) OVER()
FROM MyUsers
You cannot use temp tables in functions like in regular queries, however you can create "temp tables" by using table variables once you declare them.
Temporary tables don't work in table-valued functions because they involve disk I/O and introduce complexity that contradicts the expectations of a function (which should be deterministic and lightweight). Table variables, being in-memory and scoped within the function, are simpler in nature and are allowed in functions because they align with these constraints.
declare @TempTableName table(
variable1 int,
variable2 varchar(10),
variable3 decimal(4,3)
)
Later in your function, you would just refer to your table as @TempTableName.
