CASE statement in dynamic query

22 Oct 2005 4:52 AM

Dan Slaby

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

22 Oct 2005 5:26 AM

Andrew J. Kelly

Add the ( ):

EXEC(@QRY)

--
Andrew J. Kelly SQL MVP

Show quote

"Dan Slaby" <dsla***@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
>
>