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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-03-02 : 07:58:39
|
| Scott writes "I have created a user defined function that returns a table variable. It works fine. Now I want to use it in a stored procedure. Essentially, I want to have an item in my where clause that looks something like:Select * from mytablewhere (nameCol in dbo.getNameList())(Assume that nameCol is a column in mytable and getNameList() returns a single row table of a type compatible with nameCol).Now, if I write the sproc as above, SQL 2000 (on Windows XP) is happy. The problem is that the where clause is much more complicated than above and sql only needs to generate the table from getNameList() once (compiler writers would say it's a function that is invariant within the where). From looking at execution plans, it doesn't appear that the query analyzer always figures that out.So, I want to generate the table before I do the select. I've tried declaring the table variable and doing a set like:declare @nameTable table( aName char(20))set @nameTable = dbo.getNameList()The sproc won't compile. And I tried Insert into @nameTable from (select * from dbo.getNameList())andInsert into @nameTable from dbo.getNameList()Neither one of these compile either. I know you can't use an EXEC to fill a declared table, but it looks like you can't use a UDF either.Am I right or am I just missing something? It seems like this makes UDFs that return table variables not very useful for stored procedures if that's the case. The only alternative I see is to move the code from the UDF into the sproc (ugly)." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-03-02 : 07:58:56
|
| You almost had it:Insert into @nameTable select * from dbo.getNameList() |
 |
|
|
|
|
|
Using UDFs that return table variables