CASE在sql +动态查询中 [英] CASE in sql +dynamic query

问题描述

  BEGIN  
  DECLARE   @ QRY   NVARCHAR （ 300 ）
  DECLARE   @ ORDER   INT  
  SET  @ QRY = '  SELECT * FROM [dbo]。[LIBMAST] WHERE（1 = 1）' 
  SET  @ QRY = @ QRY + '  CASE' + @ ORDER + '  WHEN' +1 + ' 然后按ACCNO的顺序按CALLNO''END'的顺序 
  END  

错误显示

转换失败 转换 nvarchar 值'  SELECT * FROM [dbo]。[LIBMAST] WHERE（1 = 1）CASE'  to  data  type   int 。   

解决方案

试试这个......你的动态查询是Wrlyly Framed。

  BEGIN  
  DECLARE   @ QRY   NVARCHAR （ 500 ）
  DECLARE   @ ORDER  INT = 2 
  SELECT  @ QRY = '  SELECT * FROM [dbo]。[LIBMAST] WHERE（1 = 1）' +  CASE   WHEN  @ ORDER = 1  THEN  '  ORDER BY ACCNO'  ELSE  '  ORDER BY CALLNO' < span class =code-keyword> END  
  PRINT   @ QRY  
  END  

问候，$ / $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $我修改了你的查询，如下所示：

  DECLARE   @ QRY   VARCHAR （ 300 ）
 < span class =code-keyword> DECLARE   @ ORDER   INT  
 < span class =code-keyword> SET   @ QRY  = N '  SELECT * FROM [dbo]。[datalog_574T] WHERE（1 = 1）' 
  SET   @ QRY  =  @ QRY  + '  CASE' + CAST（ @ ORDER   AS   NVARCHAR （ 10 ））+ '  WHEN' + CAST（ 1   AS   NVARCHAR （ 1 ））+ ' 然后按ACCNO订购按CALLNO''END'订购 

问候

Willington

BEGIN
DECLARE @QRY NVARCHAR(300)
DECLARE @ORDER INT
SET @QRY='SELECT * FROM [dbo].[LIBMAST] WHERE (1=1) '
SET @QRY=@QRY+'CASE'+@ORDER+' WHEN'+1+'THEN ''ORDER BY ACCNO'' ELSE ''ORDER BY CALLNO''END'
END

error showing

Conversion failed when converting the nvarchar value 'SELECT * FROM [dbo].[LIBMAST] WHERE (1=1) CASE' to data type int."

解决方案

Hi,

Try This... Your Dynamic Query is Wrongly Framed.

BEGIN
    DECLARE @QRY NVARCHAR(500)
    DECLARE @ORDER INT=2
    SELECT @QRY='SELECT * FROM [dbo].[LIBMAST] WHERE (1=1) ' + CASE WHEN @ORDER=1 THEN 'ORDER BY ACCNO ' ELSE 'ORDER BY CALLNO' END
    PRINT @QRY
END

Regards,
GVPrabu.

Hi Josh

You have to cast the INT to NVARCHAR. I have modified your query like belw:

DECLARE @QRY VARCHAR(300)
DECLARE @ORDER INT
SET @QRY = N'SELECT * FROM [dbo].[datalog_574T] WHERE (1=1)'
SET @QRY = @QRY + 'CASE'+ CAST(@ORDER AS NVARCHAR(10)) +' WHEN' + CAST(1 AS NVARCHAR(1))+ 'THEN ''ORDER BY ACCNO'' ELSE ''ORDER BY CALLNO''END'

Regards
Willington

这篇关于CASE在sql +动态查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！