CODEWITHSUNDEEP
sqlsql-server-2012
Tom Cruise
Tom Cruise

Reputation: 1415

how to assign a function return value in storedprocedure?

I have a function ufnGetTVP and needs to assign the return value to a table variable in stored procedure.

I have tried the below code but getting error. I couldnt find any relevant articles to fix this issue.

create proc testfunc( @input varchar(50))
as 
begin
DECLARE @mt TABLE
set  @mt = select * from ufnGetTVP(@input)

end

It will be helpful , if someone could identify what goes wrong here.

Upvotes: 0

Views: 47

Answers (3)

Sankar
Sankar

Reputation: 7107

You can use Temp tables also, You don't have to worry about column definitions.

create proc testfunc( @input varchar(50))
as 
begin

    select * into #temp from ufnGetTVP(@input)

    select * from #temp

    IF OBJECT_ID('tempdb..#temp') IS NOT NULL drop table #temp

end

Upvotes: 2

Juan
Juan

Reputation: 3705

Have you tried inserting into the variable?

Also declare the columns on your table to match the function:

DECLARE @mt TABLE (Column1 INT, Column2 Int...)

INSERT INTO @mt
SELECT Column1, Column2, ... FROM ufnGetTVP(@input)

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82504

First, you need to include the structure of the table you are declaring:

DECLARE @mt as TABLE
(
    <columns list>
)

Second, you can't use set, you need to use insert:

INSERT INTO @mt (<columns list>)
SELECT <columns list> FROM dbo.ufnGetTVP(@input)

Upvotes: 1

Related Questions