July 2, 2007 at 4:06 pm
Is it possible to execute a dynamically generated sql string from inside a user defined table valued function?
I know I can't use EXEC sp_executesql @sql, but are there any alternatives?
Thanks in advance.
Bob
July 2, 2007 at 4:18 pm
Perhaps give an example of what you're trying to do (even if it won't work)?
July 2, 2007 at 4:33 pm
From w/in the function I'd like to pass in a string of sql and prepend and append some text and then execute it. This executed statement is used to fill a table variable. For example:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GenerateFolderNodeText]
(
@strSQL VARCHAR(MAX)
)
RETURNS
@Results TABLE
(
-- Add the column definitions for the TABLE variable here
ID INT,
NodeText VARCHAR(8000)
)
AS
BEGIN
DECLARE @sql VARCHAR(8000)
-- Temp table variable to store raw query results with an ID
DECLARE @Raw TABLE
(
ID int IDENTITY(1,1),
RawText VARCHAR(8000)
)
SET @sql = 'INSERT INTO @Raw (RawText)' + @strSQL + ' ORDER BY RawText'
EXEC(@SQL)
.
.
.
RETURN
END
July 2, 2007 at 5:08 pm
Ok, I understand this isn't possible according to BOL. Bummer!
I moved it to a stored procedure and now it works. I'll need to be careful in terms of security due to the possibility of injection attacks, etc. I'll need to specify explicit/strict execute privs.
Now that it's stored procedure I had to make a change though, In order for it to recognize the @Raw table I had to call it like this:
INSERT INTO @Raw(RawText) EXECUTE sp_executesql @strSQL
Bob
July 2, 2007 at 6:03 pm
this is very dangerous from a security perspective. anyone with perms to execute this proc can wreak all sorts of havoc.
you might want to read this: http://www.sommarskog.se/dynamic_sql.html
---------------------------------------
elsasoft.org
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy