473,320 Members | 1,839 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

Jul 23 '05 #1
9 6589

"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
Jul 23 '05 #2
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
--

Jul 23 '05 #3
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

Jul 23 '05 #4
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

Jul 23 '05 #5

"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
Jul 23 '05 #6
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

Jul 23 '05 #7

"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
Jul 23 '05 #8
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

Jul 23 '05 #9
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
Jul 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Ken Halley | last post by:
How does one refer to a variable name using a variable within a variable name??? For example, I want to perform a SQL query and use the results of the query to determine which variable to assign a...
2
by: Martin Feuersteiner | last post by:
Hi I'm grateful for any light you can shed on this!! I've to admit, it's an unusual design but I've multiple contact tables named e.g. i2b_ash_contact or i2b_ted_contact. 'i2b_' and...
7
by: Robert Brown | last post by:
Is there a way to use PreparedStatements (or bind variables) with SQL statements that have a variable number of arguments. For example, I have an array of IDs for employees of a certain type and I...
4
by: Son KwonNam | last post by:
In XSLT, is this possible to get value from xml using XPath which is in XSLT variable? I mean XPath strings can be dynamic while XSL Transforming. If possible, How?? Because I'm not a...
5
by: Allerdyce.John | last post by:
Do I need to convert string to integer in python? or it will do it for me (since dynamic type)? In my python script, I have this line: x /= 10; when i run it, I get this error: TypeError:...
14
by: Matt | last post by:
I need to add the following variable into an SQL statement and not sure how to do it. strGCID needs to be inserted into the following statement: SQL = "SELECT tblContacts.* FROM tblContacts...
6
by: Muthu08 | last post by:
Hello Experts, I'm trying to convert an dynamic XML doc into HTML using XSLT. In the example I have shown one form(form1)...But there could be multiple forms....names are unknown and each one...
0
by: becksinthecity | last post by:
I'm trying to convert the below SQL into Oracle but am having some issues with the variable declarations. SQL commands declare @sqlstring varchar(500) select @sqlstring= (select ...
0
by: tickle | last post by:
Need to convert this PL/SQL script to Dynamic SQL Method 2 * copybook - celg02u3.sql SIR 24265 * * updates dt_deny for all rows in * * ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.