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 - 2001-06-19 : 10:30:22
|
Peter writes "Hi,
I would like to build up a dynamic SQL statment, execute it, and return the results from a UDF.
SQL Server complains when I try and EXEC my SQL statement, and if I try and fake it out by placing it in a stored proc, it complains about only being able to call extended stored procs or other funcitons inside a function.
As far as I know functions may be used as a source for queries, but stored procs may not (ie: Select * from my_function(1,'potato') )
How do I get around this ?
Thanks,
Peter
P.S. I'm running SQL Server 2000 under Windows 2000 Server
Create function func_test (@id int)
RETURNS @ret_table TABLE (
someinfo int,
somemoreinfo varchar(30)
)
AS
BEGIN
Declare @stmt varchar(500)
-- in reality, my dynamic query is WAY more complex
Set @stmt = 'Select oneint,onestr from sometable where key=' + Convert(VARCHAR,@id)
--
--Exec (@stmt) wishfull thinking
--Exec sp_sqlexec is also invalid
--
-- Magic code goes here to execute @stmt and return it
-- so this function can be used in a select statment like:
-- Select * from func_test(2) group by ....... order by ...
Return
end" |
|
|
|
|
|
Dynamic SQL inside a UDF