create procedure p_1
@tempsql varchar(4000)='',
@ifrepeat int output
as
declare @sql nvarchar(4000)
set @sql='if exists(select distinct 1 from authors '+@tempsql+')'
set @sql=@sql+' set @ifrepeat=1'
set @sql=@sql+' else set @ifrepeat=0'
exec sp_executesql @sql,N'@ifrepeat int output',@ifrepeat output
godeclare @s int
exec p_1 'where 1=2',@s output
select @s as 'result'
exec p_1 '',@s output
select @s as 'result'
/*
result
-----------
0(所影响的行数为 1 行)result
-----------
1(所影响的行数为 1 行)
*/
@tempsql varchar(4000)='',
@ifrepeat int output
as
declare @sql nvarchar(4000)
set @sql='if exists(select distinct 1 from authors '+@tempsql+')'
set @sql=@sql+' set @ifrepeat=1'
set @sql=@sql+' else set @ifrepeat=0'
exec sp_executesql @sql,N'@ifrepeat int output',@ifrepeat output
godeclare @s int
exec p_1 'where 1=2',@s output
select @s as 'result'
exec p_1 '',@s output
select @s as 'result'
/*
result
-----------
0(所影响的行数为 1 行)result
-----------
1(所影响的行数为 1 行)
*/
源码如下:
PROCEDURE sp_ifDataDictionaryNameRepeat
@dataName varchar(100),
@ifRepeat char(1) outputAS
declare @ud_tmp varchar(1000)
select ifRepeat='0'
set @ud_tmp='if exists(select distinct 1 from HPDAD where '+@dataName+')'
set @ud_tmp=@ud_tmp+' set @ifrepeat="1"'
set @ud_tmp=@ud_tmp+' else set @ifrepeat="0"'
print @ud_tmp
exec sp_executesql @ud_tmp,N'@ifrepeat int output',@ifRepeat output
GO
错误提示:
服务器: 消息 214,级别 16,状态 2,过程 sp_executesql,行 25
过程需要参数 '@statement' 为 'ntext/nchar/nvarchar' 类型。
@dataName varchar(100),
@ifRepeat char(1) outputAS
declare @ud_tmp nvarchar(1000)
select ifRepeat='0'
set @ud_tmp='if exists(select distinct 1 from HPDAD where '+@dataName+')'
set @ud_tmp=@ud_tmp+' set @ifrepeat="1"'
set @ud_tmp=@ud_tmp+' else set @ifrepeat="0"'
--print @ud_tmp
exec sp_executesql @ud_tmp,N'@ifrepeat int output',@ifRepeat output
GO
服务器: 消息 214,级别 16,状态 2,过程 sp_executesql,行 25
过程需要参数 '@statement' 为 'ntext/nchar/nvarchar' 类型。=============>使用sp_executesql时参数要使用unicode字符变量'ntext/nchar/nvarchar'三种类型,或unicode字符常量N'字符常量'
你要把 declare @ud_tmp varchar(1000)==>改为 declare @ud_tmp nvarchar(1000)