Convert variable into dynamic select

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

"Rodusa" <rclwebdesign@yahoo.com> wrote in message
news:1113860462.474946.94080@f14g2000cwb.googlegroups.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

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
--

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

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

"Rodusa" <rclwebdesign@yahoo.com> wrote in message
news:1113924689.072483.110750@g14g2000cwa.googlegroups.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

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

"Rodusa" <rclwebdesign@yahoo.com> wrote in message
news:1113940976.094846.55700@z14g2000cwz.googlegroups.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

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=@reportna
me

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,reportn
ame 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=@reportna
me
FETCH NEXT FROM my_cursor INTO @sql,@reportname
END
CLOSE my_cursor
DEALLOCATE my_cursor

Rodusa (rclwebdesign@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=@reportna
me
>
> 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,reportn
ame 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, esquel@sommarskog.se

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