473,837 Members | 1,680 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 6614

"Rodusa" <rc**********@y ahoo.com> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.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**********@y ahoo.com> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.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_exec utesql @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_amoun t)
AS total FROM testdb.dbo.invo ice_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_amoun t) AS total FROM
testdb.dbo.invo ice_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**********@y ahoo.com> wrote in message
news:11******** *************@z 14g2000cwz.goog legroups.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_amoun t)
AS total FROM testdb.dbo.invo ice_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_SalesRep ort
set dollar_amount = @grand_total
-- where ... ??

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

'select @total = sum(total_amoun t) from testdb.dbo.invo ice_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=@rep ortname

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,re portname 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,@reportnam e
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=@rep ortname
FETCH NEXT FROM my_cursor INTO @sql,@reportnam e
END
CLOSE my_cursor
DEALLOCATE my_cursor

Jul 23 '05 #9
Rodusa (rc**********@y ahoo.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=@rep ortname

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,re portname 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,@reportnam e
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****@sommarsk og.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
10024
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 value to. Let's say I've dimmed 12 variables based on the months called "featured_x_product" where x is the month. I'd like to refer to the variable with something like: featured_" & R("month") & "_product where R("month") is the from the...
2
29765
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 '_contact' are static but the middle part is dynamic. Storing all contacts in one table with an identifier of e.g. 'ash' or 'ted' for each record is not possible.
7
2283
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 want to have a PreparedStatement retrieve all of them in a single SQL call. SELECT FROM employees WHERE employee_type = ? employee_id in (?,?,?,...,?) It seems at least in Java that PreparedStatements can only take a fixed number of...
4
21215
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 native English speaker, it's quite hard to make the problem clear. Please see the following example.
5
3444
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: unsupported operand type(s) for /=: 'unicode' and 'int' I want to divide x by 10 and assign that value back to x.
14
3105
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 INNER JOIN tblGC ON tblContacts.GCID = tblGC.gcID WHERE (((tblContacts.GCID)=strGCID))" i am just not sure of the proper syntax.
6
3401
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 could have o or more childforms .I want to display form1 as <th> element followed by attributes,then display Childform1 as header(if there is a child form) .See the XSl below. Is there a way to check if form1 has childforms...so i could render...
0
3282
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 concat('ARC_ALD_',(select DATA_TABLE_ID from arc_active_list aal join arc_resource_ref arr on aal.ID=arr.ID where arr.uri like '%Lists/AccountLockouts' )) a ) exec('select * from'+@sqlstring+'') End SQL
0
2727
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 * * the removal_eligibility_link table for all persons * * in all stages associated with the victim who has * * has had a specific legal status change * EXEC SQL EXECUTE
0
9685
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10886
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10580
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10634
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10277
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9413
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7007
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4479
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4052
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.