table return function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

table return function

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-02-14 : 02:27:50

Dear all,
can i write a table return function like this?

create function my_function(
returns @MYTABLE table(NAME VARCHAR(20),SES VARCHAR(20),CNT DECIMAL(10),MYDATE DATATIME)
insert into @mytable
select col1,col2,col3,col4 from tab1
go
select col1,col2,col3,col4 from tab2
go
select col1,col2,col3,col4 from tab3
go
select col1,col2,col3,col4 from tab4
go
return
end

am i doing correct?
what i'm expecting from this function is, i need all the data from select statements should be inserted into one table.
please guide me in this regard

Vinod
Even you learn 1%, Learn it with 100% confidence.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-14 : 02:50:27

have you refer to the BOL on the syntax ? http://msdn2.microsoft.com/en-us/library/ms186755.aspx

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-14 : 03:15:07

quote:
Originally posted by sunsanvin

Dear all,
can i write a table return function like this?

create function my_function(
returns @MYTABLE table(NAME VARCHAR(20),SES VARCHAR(20),CNT DECIMAL(10),MYDATE DATATIME)
insert into @mytable
select col1,col2,col3,col4 from tab1
union all
select col1,col2,col3,col4 from tab2
union all
select col1,col2,col3,col4 from tab3
union all
select col1,col2,col3,col4 from tab4

return
end

am i doing correct?
what i'm expecting from this function is, i need all the data from select statements should be inserted into one table.
please guide me in this regard

Vinod
Even you learn 1%, Learn it with 100% confidence.

Hi. change like above. use union all only if you are sure that tables contain unique combinational values else use union which automatically takes distinct values.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-14 : 03:28:19

Also GO cant be part of a procedure or function

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources