Sql Server Function With Table Parameter and Retuning Table

0
35

Image

 

its a complex requirement for having table parameters to be passed and returned from a stored procedure

by default SQL server doent accept table for this we need to create a user defined data type, say tabletype(for here)

create type tableType as table

(

f1 int,

f2 char(1)

)

Now create a table tab1 for us to pass values in to table

create table tab1

(

f1 int,

f2 char(1)

)

Insert initial values to this table , this is needed for function demonstration

insert into tab1 values (1,’c’)
insert into tab1 values (1,’d’)

Create function

–<START>

Create function fnTab1(@t1 as tableType readonly)
returns
@tab3
table
(
f1 int,f2 char(1)
)
as
begin
insert into @tab3 select * from @t1
insert into @tab3 select * from @t1
return
end

–</END>

here things underwent are ,

first we are accepting parameter of type tableType we declared earlier,

then defining Returning parameter as table

from with in the function 2 times we are inserting the passed table in to the table variable to be returned

and finally returning the table with keyword return

 

–declaring one more tableType variable

declare @p as tabletype

–insert some values in to this table from existing table tab1

insert into @p select * from tab1

Passing and returning from Function

select * from dbo.fnTab1(@p)

Result

1 c
1 d
1 c
1 d

SHARE
Previous articleSql server Function Returning a table
Next articleDaylight Saving Time (DST)
abhilash, is a programmer by profession and a technology addict with a passion towards browsing , shopping and imaging . My views may be biased on reviews. all images and videos are copyrighted to respective owners.

LEAVE A REPLY

Please enter your comment!
Please enter your name here