CASE statement in dynamic query

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.
DanAdd the ( ):
EXEC(@.QRY)
Andrew J. Kelly SQL MVP
"Dan Slaby" <dslaby3@.comcast.net> wrote in message
news:OU%23x8Ms1FHA.1032@.TK2MSFTNGP12.phx.gbl...
> 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
>