Convert variable into dynamic select

[Rodusa]

I am trying to assign @sql variable to @total, where @sql is a sql
statement stored on the database, however what I am getting is its
string value and not its calcuation. Could anybody help?
DECLARE my_cursor CURSOR FOR
SELECT sqlstatement from Sn_SalesReport
declare @sql varchar(255), @total varchar(20)
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @sql
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
SET @total = (@sql)
print @total
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM my_cursor
INTO @sql
END
CLOSE my_cursor
DEALLOCATE my_cursor

I tried this SET @total = EXEC (@sql), but no success

Rod

[Simon Hayes]

"Rodusa" <rc**********@yahoo.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

I am trying to assign @sql variable to @total, where @sql is a sql
statement stored on the database, however what I am getting is its
string value and not its calcuation. Could anybody help?
DECLARE my_cursor CURSOR FOR
SELECT sqlstatement from Sn_SalesReport
declare @sql varchar(255), @total varchar(20)
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @sql
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
SET @total = (@sql)
print @total
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM my_cursor
INTO @sql
END
CLOSE my_cursor
DEALLOCATE my_cursor

I tried this SET @total = EXEC (@sql), but no success

Rod

Your syntax isn't correct - the best option is probably to use sp_executesql
with an output parameter. See this article for more details on dynamic SQL
generally:

http://www.sommarskog.se/dynamic_sql.html

And especially this section:

http://www.sommarskog.se/dynamic_sql.html#sp_executesql

Simon

[David Portas]

Why do you want to put the SQL statements into a table? You'll have to
use sp_executesql to do this and put up with the performance, security
and maintenance issues inherent in dynamic SQL. I'm sure there ought to
be a better way to build a Sales Report!

--
David Portas
SQL Server MVP
--

[Rodusa]

David, The Sales Report is composed of several SQL statements. The
Stored procedure processes each row which contains a sql and places the
result in a temp table, in this case, SalesReport. I then use a asp.net
datagrid to view the results and export them to excel. The reason for
why I use the sql statetements separated is because the marketing dept.
should be able to update and add additional sql statements.

Rod

[Rodusa]

I tried your suggestion on the article. The problem is that I need to
open two databases at the same time. With the script below @total is
always outputing null and I can't get it to output the real values.
use sales
DECLARE my_cursor CURSOR FOR
SELECT sqlstatement from Sn_SalesReport
declare @sql nvarchar(512), @total decimal(19,4)
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
use testdb
EXEC sp_executesql @sql, N'@total decimal(19,4) OUTPUT', @total
OUTPUT
PRINT @total
use sales
update Sn_SalesReport set dollar_amount= @total
FETCH NEXT FROM my_cursor
INTO @sql
END
CLOSE my_cursor
DEALLOCATE my_cursor

[Simon Hayes]

"Rodusa" <rc**********@yahoo.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

I tried your suggestion on the article. The problem is that I need to
open two databases at the same time. With the script below @total is
always outputing null and I can't get it to output the real values.
use sales
DECLARE my_cursor CURSOR FOR
SELECT sqlstatement from Sn_SalesReport
declare @sql nvarchar(512), @total decimal(19,4)
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
use testdb
EXEC sp_executesql @sql, N'@total decimal(19,4) OUTPUT', @total
OUTPUT
PRINT @total
use sales
update Sn_SalesReport set dollar_amount= @total
FETCH NEXT FROM my_cursor
INTO @sql
END
CLOSE my_cursor
DEALLOCATE my_cursor

Try this:

exec testdb..sp_executesql @sql, N'@total decimal(19,4) OUTPUT', @total
OUTPUT

Simon

[Rodusa]

Thanks, Simon, I just changed it a little bit. I am almost there.
There is just one piece which is not working.

On the piece below I am trying to substitute "SELECT sum(total_amount)
AS total FROM testdb.dbo.invoice_hdr" with @sql but I am getting an
error:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@sql'.
Server: Msg 16950, Level 16, State 2, Line 17
The variable '@my_cur' does not currently have a cursor allocated to
it.

use sales
DECLARE my_cursor CURSOR FOR
SELECT sqlstatement from Sn_SalesReport
declare @sql nvarchar(512), @total decimal(19,4)
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @sql
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @my_cur CURSOR
EXEC sp_executesql
N'SET @my_cur = CURSOR FOR SELECT sum(total_amount) AS total FROM
testdb.dbo.invoice_hdr; OPEN @my_cur',
N'@my_cur cursor OUTPUT', @my_cur OUTPUT
FETCH NEXT FROM @my_cur into @total
CLOSE @my_cur
DEALLOCATE @my_cur

update Sn_SalesReport set dollar_amount = @total
-- This is executed as long as the previous fetch succeeds.

FETCH NEXT FROM my_cursor
INTO @sql
END
CLOSE my_cursor
DEALLOCATE my_cursor

[Simon Hayes]

"Rodusa" <rc**********@yahoo.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

Thanks, Simon, I just changed it a little bit. I am almost there.
There is just one piece which is not working.

On the piece below I am trying to substitute "SELECT sum(total_amount)
AS total FROM testdb.dbo.invoice_hdr" with @sql but I am getting an
error:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@sql'.
Server: Msg 16950, Level 16, State 2, Line 17
The variable '@my_cur' does not currently have a cursor allocated to
it.

<snip>

I don't really understand your code - why is this line inside the cursor
loop?

update Sn_SalesReport set dollar_amount = @total

The value of dollar_amount will change every time you go through the loop,
and without a WHERE clause, every row will change (unless it's a one-row
table, of course). As a complete guess, you want something like this:

declare @sql nvarchar(512),
@total decimal(19,4),
@grand_total decimal(19,4)

set @grand_total = 0.0

DECLARE my_cursor CURSOR FOR
SELECT sqlstatement from Sn_SalesReport

OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_executesql @sql, N'@total decimal(19,4) OUTPUT', @total OUTPUT
set @grand_total = @grand_total + isnull(@total, 0.0)
FETCH NEXT FROM my_cursor INTO @sql
END
CLOSE my_cursor
DEALLOCATE my_cursor

update dbo.Sn_SalesReport
set dollar_amount = @grand_total
-- where ... ??

Here, you need @sql to look like this, to match the @total output parameter:

'select @total = sum(total_amount) from testdb.dbo.invoice_hdr'

I would also suggest - as David did - that there may be easier ways to
produce reports than writing your own reporting tool (which seems to be more
or less what you're doing). Nested cursors with dynamic SQL and user-defined
SQL statements create a number of fairly significant maintenance and
security issues. Going for a proper reporting tool will save you a lot of
time of effort in the longer run, although I appreciate that in the short
term any new solution will have a learning curve. SQL Server Reporting
Services is free if you have an MSSQL licence, for example:

http://www.microsoft.com/sql/reporting/default.asp

Simon

[Rodusa]

Simon, I am sorry, the update was just for testing. You are right, it
should show the update with the where clause like this: update
Sn_SalesReport set dollar_amount = @total where reportname=@reportname

I tried to match your suggestion, but @grand_total is being updated
with null value
I also will look look at SQL server reporting services as you
recommended.

use sales
DECLARE my_cursor CURSOR FOR
SELECT sqlstatement,reportname from Sn_SalesReport
declare @sql nvarchar(512), @total decimal(19,4),@grand_total
decimal(19,4), @reportname nvarchar(255)
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @sql,@reportname
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_executesql @sql, N'@total decimal(19,4) OUTPUT', @total
OUTPUT
set @grand_total = @grand_total + isnull(@total, 0.0)

update Sn_SalesReport set dollar_amount = @grand_total where
reportname=@reportname
FETCH NEXT FROM my_cursor INTO @sql,@reportname
END
CLOSE my_cursor
DEALLOCATE my_cursor

[Erland Sommarskog]

Rodusa (rc**********@yahoo.com) writes:

Simon, I am sorry, the update was just for testing. You are right, it
should show the update with the where clause like this: update
Sn_SalesReport set dollar_amount = @total where reportname=@reportname

I tried to match your suggestion, but @grand_total is being updated
with null value
I also will look look at SQL server reporting services as you
recommended.

use sales
DECLARE my_cursor CURSOR FOR
SELECT sqlstatement,reportname from Sn_SalesReport
declare @sql nvarchar(512), @total decimal(19,4),@grand_total
decimal(19,4), @reportname nvarchar(255)
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @sql,@reportname
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_executesql @sql, N'@total decimal(19,4) OUTPUT', @total
OUTPUT
set @grand_total = @grand_total + isnull(@total, 0.0)

This will indeed leave you with NULL in @grand_total. You need to
set @grand_total to 0 before you move into the cursor.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp