13,001,686 members (98,953 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 21:16pm
josh-jw17.2K

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
 OriginalGriff 290 ppolymorphe 180 F-ES Sitecore 130 Bohdan Stupak 115 David_Wimbley 90
 OriginalGriff 4,857 ppolymorphe 2,437 RickZeeland 2,221 Kornfeld Eliyahu Peter 2,031 Richard Deeming 1,643