CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Mursa Catalin
Mursa Catalin

Reputation: 1449

Use variables from procedure in dynamic query

DECLARE @a NVARCHAR(10) ,
    @b NVARCHAR(100)

SET @a = 'value'
SET @b = ' PRINT @a '

EXEC sp_executesql @b

i would like as a result : 'value'
but i get the error Must declare the scalar variable "@a"

i need to log from SQL the parameters which was called the SP

i need this because inside SP i create the dinamic query from INFORMATION_SCHEMA.PARAMETERS

my procedure : 

ALTER PROCEDURE marianTest
@a INT,
@b NVARCHAR(121)
AS
BEGIN
DECLARE @query NVARCHAR(max)
SET @query = dbo.ufn_admin_Ex(cast(OBJECT_NAME(@@PROCID) AS nvarchar(100)))
EXECUTE sp_executesql  @query

where @query is like this :

declare @callSP nvarchar(max) = 
'DECLARE @a int = ' + CASE  WHEN @a IS NULL THEN 'NULL' ELSE    convert(nvarchar(15),@a)     END + CHAR(13)
+', @b nvarchar(121) = ' + CASE  WHEN @b IS NULL THEN 'NULL' ELSE + '''' + @b+   ''''     END + CHAR(13)

EXECUTE [dbo].[usp_admin_traceError] @callSp

thanks
Marian

Upvotes: 0

Views: 270

Answers (4)

t-clausen.dk
t-clausen.dk

Reputation: 44356

I believe this is what you are looking for:

DECLARE @a NVARCHAR(10) , @b NVARCHAR(100)  
SET @a = 'value' 
SET @b = ' PRINT @a '  
EXEC sp_executesql @b, N'@a nvarchar(10)', @a

Result:

value

Upvotes: 3

Bridge
Bridge

Reputation: 30721

You're not specifying the parameters on

exec sp_executesql

See http://msdn.microsoft.com/en-us/library/ms188001.aspx.

Upvotes: 2

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

@b can't magically see @a unless you introduce them somehow.

Try before the exec:

SET @b = 'PRINT ''' + @a + ''';

Or you can use sp_executesql this way instead:

DECLARE @b NVARCHAR(100);

SET @b = N'PRINT @a';

EXEC sp_executesql @b, N'@a NVARCHAR(10)', @a;

Upvotes: 2

Ocaso Protal
Ocaso Protal

Reputation: 20267

If you want to use parameters/variables in your dynamic SQL you should use sp_executesql with parameters. See The Curse and Blessings of Dynamic SQL for an excellent introduction to dynamic SQL.

Upvotes: 1

Related Questions