Dynamic SQL - Function

Question

Post reply

Dynamic SQL - Function

CooLDBA

Hall of Fame

Points: 3301
More actions
April 12, 2006 at 4:53 pm

#112988

This is my script:
declare
@DatabaseName varchar(50), @MatchedCompanyID int
-- fnMatchCompany is a function
SELECT
@DatabaseName = DatabaseName FROM dbo.vDatabase
-- (209 row(s) affected)
set @exec = 'set @MatchedCompanyID = '+@DatabaseName+'.dbo.fnMatchCompany('''+@CompanyName+''', '+@ZipPostalCode+', '+@Phone+')'
exec
sp_EXECUTESQL @exec
Above script won't work!
Is there a way I can put the result value after I called the function to put in a variable?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 1

Use the full syntax for sp_executesql to pass parameters in and out.

declare @z varchar(150)

exec

sp_executesql N'set @x = @a + @b-2 + @C',

N'@a varchar(50), @b-2 varchar(50), @C varchar(50), @x varchar(150) OUTPUT',

@a = 'Bippety ', @b-2 = 'Boppety ', @C = 'Boo', @x = @z OUTPUT

print

@z

Bippety Boppety Boo

Mike C SSC-Insane Points: 23224 More actions · Answer 2

Just wondering, but why put it in a dynamic SQL statement?

First thing, you're using sp_executesql with VARCHAR values. You should be using NVARCHAR. Next, you're using sp_executesql but not using parameterization. This opens you up to SQL Injection. If you're not going to use parameterization, then at least do some sort of check on the variables.

The problem you're hitting (like the previous poster who had a similar issue) is scope. If you really want to put something like this in a dynamic SQL statement and retrieve the results, you'll need to use a temporary table which can be created outside the scope of the dynamic SQL but accessed from within. Here's a simple example:

-- Declare the variables

DECLARE @sql NVARCHAR(2000)

DECLARE @like_criteria NVARCHAR(2000)

DECLARE @database NVARCHAR(2000)

-- Assign the variables

SELECT @database = 'master'

-- Very simple variable clean-up, to prevent SQL Injection

SELECT @database = QUOTENAME(REPLACE(@database, ';', ''))

-- Create a temp table. This will be accessible from within the

-- dynamic SQL

CREATE TABLE #temp

(

[text] NVARCHAR(4000)

)

-- Build the dynamic SQL statement

SELECT @sql = N'USE ' + @database + '; INSERT INTO #temp SELECT [text] FROM dbo.syscomments WHERE [text] LIKE @param1'

-- Set our parameter value

SELECT @like_criteria = N'create%'

-- Execute the parameterized SQL

EXEC dbo.sp_executesql @sql, N'@param1 NVARCHAR(2000)', @param1 = @like_criteria

-- SELECT the result from the temp table

SELECT * FROM #temp

-- DROP the temp table

DROP TABLE #temp

Another possible alternative in your situation might be to create a stored procedure that runs your command in the master database, use sp_MS_marksystemobject procedure to mark your SP as a system object, and pass it the proper parameters. This will cause it to run under the context of the database from which it is called (i.e., you will not need to specify the database name in your query; it will run under the context of the current database).