CODEWITHSUNDEEP
sql-server-2008t-sqluser-defined-functionstable-valued-parameters
renathy
renathy

Reputation: 5355

SQL Server 2008 table with data as parameter for UDF

My question: Is it possible in SQL Server 2008 to define temporal table in stored procedure and pass it as variable to user defined function?

I need to do the following:

  1. I have procedure dbo.GetMsgCntData.

  2. This procedure is making some calculations and as a result of this procedure I have data into temporary table that is defined in this procedure (GetMsgCntData):

    -- table defined in dbo.GetMsgCntData
DECLARE @tmpTable TABLE (
    ID BIGINT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    UserType varchar(50),
    SenderID bigint,
    IsArchive bit
)

  3. So, @tmpTable contains some data. I need to run user defined function GetCnt(@status, @MsgTempTable), but I need getCnt function to access @tmpTable data. Basically I need something like this:

    -- to be written at the end of dbo.GetMsgCntData
SELECT cnt(*) total,
    dbo.GetCnt('open', @tmpTable) as opened,
    dbo.GetCnt('closed', @tmpTable) as closed
FROM @tmpTable
-- where @tmpTable is temporal table

  4. I tried to define @table in GetCnt as user defined type.

    CREATE TYPE dbo.tmp_Messages AS TABLE (
    ID BIGINT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    UserType varchar(50),
    SenderID bigint,
    IsArchive bit
)

CREATE FUNCTION FUNCTION [dbo].[GetCnt] (
    @Status nvarchar(10),
    @MsgTempTable dbo.tmp_Messages READONLY
)
....

but this gives me error message:

Operand type clash: table is incompatible with tmp_Messages

I think that my idea is simply incorrect.

Upvotes: 2

Views: 2811

Answers (2)

Dalex
Dalex

Reputation: 3625

Temporary table is not compatible with user-defined type. You should declare your temporary table as user-defined type: 

CREATE TYPE dbo.tmp_Messages AS TABLE 
(
 ID BIGINT,
 FirstName VARCHAR(50),
 LastName VARCHAR(50),
 UserType varchar(50),
 SenderID bigint,
 IsArchive bit
 )
 GO
 --function declaration 

        CREATE FUNCTION [dbo].[GetCnt] (
            @Status nvarchar(10),
            @MsgTempTable dbo.tmp_Messages READONLY
        )...
        -- table defined in dbo.GetMsgCntData
        DECLARE @tmpTable dbo.tmp_Messages;
        INSERT INTO @tmpTable(---some actions

SELECT cnt(*) total,
    dbo.GetCnt('open', @tmpTable) as opened,
    dbo.GetCnt('closed', @tmpTable) as closed
FROM @tmpTable

Please read a good article about table-valued parameters: http://beyondrelational.com

Upvotes: 2

rs.
rs.

Reputation: 27427

You can use user defined table types to achieve this. Try this

CREATE TYPE T1Type
AS TABLE (ID BIGINT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    UserType varchar(50),
    SenderID bigint,
    IsArchive bit
)

In your SP

DECLARE @tmpTable T1Type
INSERT INTO @tmpTable
SELECT * FROm TableName

In your function

CREATE FUNCTION FUNCTION [dbo].[GetCnt] (
    @Status nvarchar(10),
    @MsgTempTable T1Type READONLY
)

Upvotes: 1

Related Questions