Sunday, June 14, 2015

SqlServer Procedure expects parameter '@' of type 'ntext/nchar/nvarchar'

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

exec dummy_ins '14','sdf'


error :
Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1
Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.

Problem:
Parameter value for procedure "sp_exectesql" should be in nvarchar, parameter is '@p nvarchar(30)'
and missing prefix N

solution:
create procedure dummy_ins
(
@vid nvarchar(30),
@vname nvarchar(30)
)
as
set nocount on
declare @temp_sql nvarchar(1000)
declare @p as nvarchar(30)
set @p = 'Symmetric_Key1'
set @temp_sql = 'insert into dummy values (' + @vid  +','+ 'ENCRYPTBYKEY(KEY_GUID('''+ cast(@p as nvarchar(30)) +'''),''' + @vname+ '''))'
print @temp_sql
exec sp_executeSQL @temp_sql,N'@p nvarchar(30)', @p = @p



Related POSt
SQL server encrypt data at column level
http://j4info.blogspot.in/2015/06/sql-server-encrypt-data-at-column-level.html

No comments:

Post a Comment

web stats