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 the results from exec command

Hi,

I'm writing a small query where I have a dynamic table name and dynamic condition for the criteria. In order to execute this, I need Exec command.

exec(select count(*) from @.dynamictable where condition = @.dynamiccond)

But here I want to capture the count from the select statement. Could any of you help me capture the results from exec command?

Thanks

2 ways

USE pubs
GO

--sp_executesql
DECLARE @.chvTableName VARCHAR(100),
@.intTableCount INT,
@.chvSQL NVARCHAR(100)

SELECT @.chvTableName = 'Authors'
SELECT @.chvSQL = N'SELECT @.intTableCount = COUNT(*) FROM ' + @.chvTableName

EXEC sp_executesql @.chvSQL, N'@.intTableCount INT OUTPUT', @.intTableCount OUTPUT

SELECT @.intTableCount
GO

--EXEC (SQL)
DECLARE @.chvTableName VARCHAR(100),
@.intTableCount INT,
@.chvSQL NVARCHAR(100)

CREATE TABLE #temp (Totalcount INT)
SELECT @.chvTableName = 'Authors'
SELECT @.chvSQL = 'Insert into #temp Select Count(*) from ' + @.chvTableName

EXEC( @.chvSQL)

SELECT @.intTableCount = Totalcount from #temp

SELECT @.intTableCount

DROP TABLE #temp

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Or:

DECLARE @.chvTableName VARCHAR(100)

CREATE TABLE #temp (Totalcount INT)
SELECT @.chvTableName = 'sysobjects'

insert into #temp(totalCount)
EXEC( 'Select Count(*) from ' + @.chvTableName)

SELECT * from #temp

DROP TABLE #temp

Note: it is generally considered a bad practice to do this sort of thing unless you are building some sort of tool. If this is a production application, it would be better to build a procedure per table:

create procedures count_accounts
as
select count(*) from account
go

Yes, it sounds like a lot of maintenance, but unless you build a large quantity of tables, it shouldn't be a big deal.

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

capture error messages from dynamic tsql

How does one go about capturing error messages for a procedure that executes
dynamic sql...
for example ten lines out of 1000 produce an error... can the error be
captured and returned to the user?
--
Regards,
JamieI'm not sure what you are asking. Exceptions *are* returned to the user by default. See below:
USE tempdb
CREATE TABLE t(c1 int CHECK (c1 < 10))
GO
CREATE PROC p AS
EXEC('INSERT INTO t (c1) VALUES (20)')
GO
--Verify error
EXEC p
GO
--Capture using TRY and CATCH
BEGIN TRY
EXEC p
END TRY
BEGIN CATCH
DECLARE @.errStr nvarchar(4000)
SET @.errStr = ERROR_MESSAGE()
PRINT @.errStr
END CATCH
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:A075E032-9E58-4F19-A8E6-D66D5ED66357@.microsoft.com...
> How does one go about capturing error messages for a procedure that executes
> dynamic sql...
> for example ten lines out of 1000 produce an error... can the error be
> captured and returned to the user?
> --
> Regards,
> Jamie|||I should have added we are still using SQL 2000. Tested this in 2005 and it
works great. Thanks.
--
Regards,
Jamie
"Tibor Karaszi" wrote:
> I'm not sure what you are asking. Exceptions *are* returned to the user by default. See below:
> USE tempdb
> CREATE TABLE t(c1 int CHECK (c1 < 10))
> GO
> CREATE PROC p AS
> EXEC('INSERT INTO t (c1) VALUES (20)')
> GO
> --Verify error
> EXEC p
> GO
> --Capture using TRY and CATCH
> BEGIN TRY
> EXEC p
> END TRY
> BEGIN CATCH
> DECLARE @.errStr nvarchar(4000)
> SET @.errStr = ERROR_MESSAGE()
> PRINT @.errStr
> END CATCH
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:A075E032-9E58-4F19-A8E6-D66D5ED66357@.microsoft.com...
> > How does one go about capturing error messages for a procedure that executes
> > dynamic sql...
> > for example ten lines out of 1000 produce an error... can the error be
> > captured and returned to the user?
> > --
> > Regards,
> > Jamie
>
>