March 1, 2004 at 2:18 pm
Hi,
I am trying to figure out how I can execute sp_executesql inside a function.
The purpose of that function is to accept table name, column name and PK of table as input parameter and it will return the value. I plan on using sql_variant as the data type so that I can browse the col of any table (and save on some joins)
(I have already implemented this logic in a stored proc. I am very curious to know if we can implement this (executing dynamic sql) inside of a function.
example -
declare @tbl varchar(30), @col varchar(30), @pkcol varchar(30), @pk varchar(6), @sql nvarchar(255)
select @tbl = 'z_prop', @col = 'sysdate', @pkcol = 'property', @pk = 'AZ083'
select @sql = 'Select ' + @col + ' from ' + @tbl + ' where ' + @pkcol + ' = ''' + @pk + ''''
--select @sql -> Select sysdate from z_prop where property = 'AZ083'
exec sp_executesql @sql
Thanks in advance.
March 1, 2004 at 2:52 pm
sp_executesql CAN'T be used inside a UDF !
what are you trying to accomplish?
Save some joins aren't you doing it inside the function
I believe you may want to create views instead
* Noel
March 1, 2004 at 2:52 pm
It appears that you can't.
You can execute extended stored procedure inside a function and, even though sp_executesql is an extended stored procedure (despite its name), it still generates the message "only functions and extended stored procedures can be executed within a function".
Which leads me to believe that sp_executesql may itself be calling one or two standard stored procedures.
Cheers,
- Mark
March 1, 2004 at 2:57 pm
i am trying to optimize by saving on joins. yes, I am creating several views, and i know I cannot execute sql_executesql inside an UDF. I was wondering if there was a workaround.
March 2, 2004 at 3:32 am
There is one more thing to consider.
The batch and the dynamic statement both run in different scopes. While the dynamic for example has access to tables created in the batch, the batch has no access to object and/or data created in the dynamic statement.
This might also be a point.
However, maybe you can post the whole thing you have so far along what you want as the result, so we might figure out a workaround or another solution.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply