Sunday, June 14, 2015

SQLSERVER Pass Variable in Dynamic SQL

create procedure dummy_ins
(
@vid nvarchar(30),
@vname nvarchar(30)
)
as
set nocount on
declare @temp_sql nvarchar(1000)
declare @symmtric_key AS [uniqueidentifier]
set @symmtric_key = KEY_GUID('Symmetric_Key1')
set @temp_sql = 'insert into dummy values (' + @vid  +','+ 'ENCRYPTBYKEY(@symmtric_key,''' + @vname+ '''))'
print @temp_sql
exec sp_executeSQL @temp_sql

exec dummy_ins '3','sdf'


ERROR:
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@symmtric_key".

PROBLEM:
print of command - insert into dummy values (3,ENCRYPTBYKEY(@symmtric_key,'sdf'))
when sp_executeSQL execute the sql query, it found "@symmtric_key" on the way so why it is throwing error.

That mean we have enclosed the variable with single quote in sql, so the variable define is unable to pass the value.

SOLUTION:
Remove the single quotes around @symmtric_key like 'ENCRYPTBYKEY(KEY_GUID('''+ cast(@symmtric_key as nvarchar(30)) +'''),'''

see below example:
create procedure dummy_ins
(
@vid nvarchar(30),
@vname nvarchar(30)
)
as
set nocount on
declare @temp_sql nvarchar(1000)
declare @symmtric_key AS [uniqueidentifier]
set @symmtric_key = KEY_GUID('Symmetric_Key1')
set @temp_sql = 'insert into dummy values (' + @vid  +','+ 'ENCRYPTBYKEY(KEY_GUID('''+ @symmtric_key +'''),''' + @vname+ '''))'
print @temp_sql
exec sp_executeSQL @temp_sql,'@p as uniqueidentifier', @p = @symmtric_key

exec dummy_ins '14','sdf'

ERROR:
cast( guid as varchar)
Msg 402, Level 16, State 1, Procedure dummy_ins, Line 11
The data types nvarchar and uniqueidentifier are incompatible in the add operator.

From above error i feel that it will not pass key is not acceptable in this way.

SOLUTION:
correct way is here to pass variable :
see below post
SqlServer Procedure expects parameter '@' of type 'ntext/nchar/nvarchar' 

or
click on below link
http://j4info.blogspot.in/2015/06/sqlserver-procedure-expects-parameter.html

No comments:

Post a Comment

web stats