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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply