CASE statement in dynamic query -- sql server programming

Dan Slaby
10/21/2005 9:52:01 PM

This query runs ok: SELECT Mission FROM tblProviders WHERE ProviderID =
'rnpo'

When I pass the @providerID and @WhichOne values dynamically to this stored
procedure:

DECLARE @ProviderID varchar(15)
SET @ProviderID = 'rnpo'
DECLARE @WhichOne int
SET @WhichOne = 1
DECLARE @QRY varchar(150)
SET @QRY = 'SELECT ' + CASE @WhichOne
WHEN 1 THEN
'Mission'
WHEN 2 THEN
'History'
WHEN 3 THEN
'Services'
END
+ ' FROM tblProviders WHERE ProviderID = ''' + @ProviderID + ''

EXEC @QRY

This ERROR Message returned:

Server: Msg 2812, Level 16, State 62, Line 16
Could not find stored procedure 'SELECT Mission FROM tblProviders WHERE
ProviderID = 'rnpo'.

Note the ' preceding the SELECT.

Any recommendations for this stored procedure?

Thanks.

Dan

Andrew J. Kelly
10/22/2005 1:26:55 AM

Add the ( ):

EXEC(@QRY)

--
Andrew J. Kelly SQL MVP

[quoted text, click to view]

sql server programming : CASE statement in dynamic query