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
>

capturing dynamically returned integer

Hello there.
I have a dynamic SQL query (I had to use it - I swear!) that returns a
single integer value. However, I'm having trouble capturing the value in a
local variable. Any ideas what I should do with my syntax (to get it working
I mean...)?
declare @.SQLQuery nvarchar(1024)
declare @.IntegerVar integer
select @.SQLQuery = 'select max(Column1) from table1'
--execute sp_executesql @.testSQL --this returns an integer value
--the following gives errors
select @.IntegerVar = (execute sp_executesql @.testSQL)
--the following assigns 0 to @.IntegerVar rather than the query result
execute @.IntegerVar = sp_executesql @.testSQLhttp://www.aspfaq.com/2492
"len" <len@.discussions.microsoft.com> wrote in message
news:B254F6C4-E94F-48B6-BB9C-7C03066D6998@.microsoft.com...
> Hello there.
> I have a dynamic SQL query (I had to use it - I swear!) that returns a
> single integer value. However, I'm having trouble capturing the value in a
> local variable. Any ideas what I should do with my syntax (to get it
> working
> I mean...)?
> declare @.SQLQuery nvarchar(1024)
> declare @.IntegerVar integer
> select @.SQLQuery = 'select max(Column1) from table1'
> --execute sp_executesql @.testSQL --this returns an integer value
> --the following gives errors
> select @.IntegerVar = (execute sp_executesql @.testSQL)
> --the following assigns 0 to @.IntegerVar rather than the query result
> execute @.IntegerVar = sp_executesql @.testSQL|||Try,
declare @.SQLQuery nvarchar(1024)
declare @.IntegerVar integer
select @.SQLQuery = N'select @.IntegerVar = max(Column1) from table1'
execute sp_executesql @.testSQL, N'@.IntegerVar int output', @.IntegerVar outpu
t
select @.IntegerVar
go
INF: Using Output Parameters with sp_executesql
http://support.microsoft.com/defaul...B;EN-US;q262499
AMB
"len" wrote:

> Hello there.
> I have a dynamic SQL query (I had to use it - I swear!) that returns a
> single integer value. However, I'm having trouble capturing the value in a
> local variable. Any ideas what I should do with my syntax (to get it worki
ng
> I mean...)?
> declare @.SQLQuery nvarchar(1024)
> declare @.IntegerVar integer
> select @.SQLQuery = 'select max(Column1) from table1'
> --execute sp_executesql @.testSQL --this returns an integer value
> --the following gives errors
> select @.IntegerVar = (execute sp_executesql @.testSQL)
> --the following assigns 0 to @.IntegerVar rather than the query result
> execute @.IntegerVar = sp_executesql @.testSQL|||Hi Len
Try something like:
declare @.SQLQuery nvarchar(1024)
declare @.IntegerVar integer
select @.SQLQuery = 'select @.MaxVar = max(Column1) from table1'
execute sp_executesql @.SQLQuery, N'@.MaxVar int OUTPUT', @.IntegerVar OUTPUT
SELECT @.IntegerVar
John
"len" wrote:

> Hello there.
> I have a dynamic SQL query (I had to use it - I swear!) that returns a
> single integer value. However, I'm having trouble capturing the value in a
> local variable. Any ideas what I should do with my syntax (to get it worki
ng
> I mean...)?
> declare @.SQLQuery nvarchar(1024)
> declare @.IntegerVar integer
> select @.SQLQuery = 'select max(Column1) from table1'
> --execute sp_executesql @.testSQL --this returns an integer value
> --the following gives errors
> select @.IntegerVar = (execute sp_executesql @.testSQL)
> --the following assigns 0 to @.IntegerVar rather than the query result
> execute @.IntegerVar = sp_executesql @.testSQL