日期:2014-05-18  浏览次数:20541 次

关于sp_executesql问题
select * from view_IMAR_P_ORDER_NUMBER_GCkz where sid=1583
能查出一条记录

exec sp_executesql N'select * from view_IMAR_P_ORDER_NUMBER_GCkz where @field=@value',
N'@field nvarchar(200),@value nvarchar(200)',@field=N'sid',@value=N'1583'
查不出记录

请解

------解决方案--------------------
SQL code
declare @sql nvarchar(1000),@field nvarchar(200),@value nvarchar(200)
select @field=N'id',@value=N'1'
set @sql= N'select * from tb where '+@field+'='''+@value+''''
exec sp_executesql @sql

------解决方案--------------------
字段名,表名,数据库名之类作为变量时,需用(execute),参数变量值作为变量时才可用sp_executesql
SQL code

--这个应是有结果的
exec sp_executesql N'select * from view_IMAR_P_ORDER_NUMBER_GCkz where sid=@value',
N'@value nvarchar(200)',@value=N'1583'
--或者
declare @sid varchar(20),@value varchar(20),@sql nvarchar(max);
select @sid='sid',@value='1583'
select @sql='select * from view_IMAR_P_ORDER_NUMBER_GCkz where '+@sid+'='''+@value+''''
exec(@sql);

------解决方案--------------------
SQL code

declare @str nvarchar(1000),
        @field nvarchar(200),
        @value nvarchar(200)
set @field=N'sid'
set @value=N'1583'
set @str=N'select * from view_IMAR_P_ORDER_NUMBER_GCkz where '+@field+'='+@value
exec sp_executesql @str

--这种拼接问题楼主在执行之前把拼接的语句打印出来看看正确与否

------解决方案--------------------
SQL code

declare @sql nvarchar(1000)
        , @paramter  nvarchar(1000)
        , @field nvarchar(10) = 'sid'
        , @filedvalue nvarchar(10)

set @sql = 'select * from view_IMAR_P_ORDER_NUMBER_GCkz where ' + @field + '=@value'
set @paramter = '@value nvarchar(200)'
set @filedvalue = '1583'

exec sp_executesql @sql, @paramter ,@value=@filedvalue;