12,077,753 members (48,444 online)
Rate this:
See more:
```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."`
Posted 21-Feb-13 22:16pm
josh-jw15.1K

Rate this:

## Solution 1

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.
Rate this:

## Solution 2

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

Top Experts
Last 24hrsThis month
 Richard MacCutchan 365 Richard Deeming 305 Sergey Alexandrovich Kryukov 243 CHill60 200 OriginalGriff 195
 Dave Kreskowiak 3,026 OriginalGriff 3,000 Richard MacCutchan 2,539 CPallini 1,722 CHill60 1,579