sp_executesql within a stored proc? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sp_executesql within a stored proc?

Author

Topic

redi311
Starting Member

3 Posts

Posted - 2008-06-16 : 12:48:06

I have the following code which works, but every time i try to put a procedure around it to pass in the 3 variables I set in this code manually it bombs on me.

~SQL~
USE [myDb]
-- variable declarations
DECLARE @sql nvarchar(max)
DECLARE @params nvarchar(max)
DECLARE @tableName nvarchar(50)
DECLARE @personId nvarchar(36)
DECLARE @encId nvarchar(36)
DECLARE @prov_reviewOUT nvarchar(1)
DECLARE @prov_opened_templateOUT nvarchar(1)

--TEST:: add input values
set @tableName = 'master_im_'
set @personId = '9B9192AF-97E1-4FD6-8279-362E4944ECAC'
set @encId = '63570609-529B-4488-BBBF-E039A8F10D92'

-- build SQL String for dynamic SQL query
SELECT @sql = N'SELECT @prov_reviewRET = provider_review, @prov_opened_templateRET = prov_opened_template
FROM dbo.' + quotename(@tableName) + ' WHERE person_id = @personId AND enc_id = @encId'

-- build parameters for sp_executesql
SELECT @params = N'@personId nvarchar(36), ' +
N'@encId nvarchar(36), ' +
N'@prov_reviewRET nvarchar(1) OUTPUT, ' +
N'@prov_opened_templateRET nvarchar(1) OUTPUT '

-- execute dynamic sql string
EXEC sp_executesql
@sql,
@params,
@personId,
@encId,@prov_reviewRET = @prov_reviewOUT OUTPUT,
@prov_opened_templateRET = @prov_opened_templateOUT OUTPUT

select @prov_reviewOUT, @prov_opened_templateOUT

~endSQL~

works fine every time...

but when i do this:
~sql~
create procedure svsp_test
@tableName nvarchar(50),
@personId nvarchar(36),
@encId nvarchar(36),
@prov_reviewOUT nvarchar(1) OUTPUT,
@prov_opened_templateOUT nvarchar(1) OUTPUT
AS

-- variable declarations
DECLARE @sql nvarchar(max)
DECLARE @params nvarchar(max)

-- build SQL String for dynamic SQL query
SELECT @sql = N'SELECT @prov_reviewRET = provider_review, @prov_opened_templateRET = prov_opened_template
FROM dbo.' + quotename(@tableName) + ' WHERE person_id = @personId AND enc_id = @encId'

-- build parameters for sp_executesql
SELECT @params = N'@personId nvarchar(36), ' +
N'@encId nvarchar(36), ' +
N'@prov_reviewRET nvarchar(1) OUTPUT, ' +
N'@prov_opened_templateRET nvarchar(1) OUTPUT '

-- execute dynamic sql string
EXEC sp_executesql
@sql,
@params,
@personId,
@encId,
@prov_reviewRET = @prov_reviewOUT OUTPUT,
@prov_opened_templateRET = @prov_opened_templateOUT OUTPUT

~endsql~

and then run the proc like this:

declare @prov_reviewOUT nvarchar(1), @prov_opened_templateOUT nvarchar(1)
exec svsp_test 'master_im_', '9B9192AF-97E1-4FD6-8279-362E4944ECAC', '63570609-529B-4488-BBBF-E039A8F10D92', null, null
select @prov_reviewOUT, @prov_opened_templateOUT

I always get null returned. If i put a select statement into the stored proc create code as the last line, it shows the correct values, but trying to read them from the sp in the create procedure statement always returns null. Can someone help me figure out what I'm doing incorrectly please?

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 12:57:45

Try like this:-

declare @prov_reviewOUT nvarchar(1), @prov_opened_templateOUT nvarchar(1)
exec svsp_test 'master_im_', '9B9192AF-97E1-4FD6-8279-362E4944ECAC', '63570609-529B-4488-BBBF-E039A8F10D92', @prov_reviewOUT, @prov_opened_templateOUT
select @prov_reviewOUT, @prov_opened_templateOUT

redi311
Starting Member

3 Posts

Posted - 2008-06-16 : 14:31:27

declare @prov_reviewOUT nvarchar(1), @prov_opened_templateOUT nvarchar(1)
exec svsp_test 'master_im_', '9B9192AF-97E1-4FD6-8279-362E4944ECAC', '63570609-529B-4488-BBBF-E039A8F10D92', @prov_reviewOUT OUTPUT, @prov_opened_templateOUT OUTPUT
select @prov_reviewOUT, @prov_opened_templateOUT

it didn't work until i put OUTPUT after the variable names in the caller.

Looks like im good at this point...

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 14:42:26

quote:
Originally posted by redi311

declare @prov_reviewOUT nvarchar(1), @prov_opened_templateOUT nvarchar(1)
exec svsp_test 'master_im_', '9B9192AF-97E1-4FD6-8279-362E4944ECAC', '63570609-529B-4488-BBBF-E039A8F10D92', @prov_reviewOUT OUTPUT, @prov_opened_templateOUT OUTPUT
select @prov_reviewOUT, @prov_opened_templateOUT

it didn't work until i put OUTPUT after the variable names in the caller.

Looks like im good at this point...

Thanks!

missed OUTPUT while posting. Glad that you spotted it out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources