daynamic选择查询执行问题 [英] daynamic select query execute problem

问题描述

这是我的存储过程..我动态地给列名称

  @ datafield   varchar （ 30 ）  -   >我输入的此列名称 
  AS  
  BEGIN  
  SELECT  cust_id  FROM  custm_master 其中 < span class =code-sdkkeyword> @ datafield  =  1  
  END  
 
  exec  cust_search '  cust_id '   -   >列名 

i收到错误：

转换失败何时转换 nvarchar 值'  [cust_id]'  数据类型  int 。

解决方案

你不能这样做：参数替换是在不同的阶段完成的SQL处理，这样就无法工作了。你要做的是创建一个字符串并执行它：

  DECLARE   @ CMD   VARCHAR （MAX）
  SET   @ CMD  = '  SELECT cust_id FROM custm_master WHERE' +  @ datafield  + '  = 1' 
  EXEC （ @ CMD ）

试试这种方式 -

  @ datafield   varchar （ 30 ）  -   >我输入的此列名称 
  AS  
  BEGIN  
 声明  @ SQL   varchar （< span class =code-digit> 8000 ）
 声明  @ val   varchar （ 100 ）= '  1' 
  set  @ SQL = '  SELECT cust_id FROM custm_master where' +  @ datafield  + '   =''' + @ val + '  ''' 
  exec （ @ sql ）
  END  

here is my stored procedure .. i give column name dynamically

@datafield varchar(30) --> this column name which i enter
AS
BEGIN
SELECT cust_id  FROM custm_master where @datafield = 1
END

exec cust_search 'cust_id' --> column name

i got an error :

Conversion failed when converting the nvarchar value '[cust_id]' to data type int.

解决方案

You can't do it like that: The parameter substitution is done at a different stage of the SQL processing, so that won't work. What you have to do is create a string and execute that:

DECLARE @CMD VARCHAR(MAX)
SET @CMD = 'SELECT cust_id FROM custm_master WHERE ' + @datafield + '=1'
EXEC(@CMD)

try this way-

@datafield varchar(30) --> this column name which i enter
AS
BEGIN
declare @SQL varchar(8000)
declare @val varchar(100)='1'
set @SQL='SELECT cust_id  FROM custm_master where  '+ @datafield +' = ''' + @val+''''
exec (@sql)
END

这篇关于daynamic选择查询执行问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！