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 )