SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Simple question about EXEC
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

deadfish
Starting Member

38 Posts

Posted - 11/28/2002 :  04:53:50  Show Profile  Reply with Quote
I have to create a sql for retrieving sum of quantity of the order no. from user selected:
But the following query does not work....

SET @QTY=0
SET @TABLE = 'ABC'
SET @ORDER_NO = '001'
SET @QUERY = 'SET @QTY= (SELECT SUM(QTY) FROM '+@TABLE+' WHERE ORDER_NO = '''+@ORDER_NO+
'''
EXEC(@QUERY)

I need to get the result quantity into the variable @qty, any idea??

Thanks!




harshal_in
Aged Yak Warrior

India
633 Posts

Posted - 11/28/2002 :  05:18:07  Show Profile  Visit harshal_in's Homepage  Send harshal_in a Yahoo! Message  Reply with Quote
declare @QTY int,@table varchar(30),@order_no varchar (50),@query varchar(300)

SET @TABLE = 'ABC'
SET @ORDER_NO = 4
set @query=0
SET @QUERY = ' (SELECT SUM(TY) as sum FROM '+@TABLE+' WHERE ORDERNO = '+@ORDER_NO+ ')'

EXEC(@QUERY)







Go to Top of Page

deadfish
Starting Member

38 Posts

Posted - 11/28/2002 :  09:23:19  Show Profile  Reply with Quote
quote:

declare @QTY int,@table varchar(30),@order_no varchar (50),@query varchar(300)

SET @TABLE = 'ABC'
SET @ORDER_NO = 4
set @query=0
SET @QUERY = ' (SELECT SUM(TY) as sum FROM '+@TABLE+' WHERE ORDERNO = '+@ORDER_NO+ ')'

EXEC(@QUERY)











But what to write if I want to assign the sum(qty) to the variable @qty??

Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 11/28/2002 :  09:49:27  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Why are you using EXEC? Is this a simplified example of what you need to do?

If not,

SET @ORDER_NO='001'
SET @QTY=(SELECT SUM(QTY) FROM ABC WHERE ORDER_NO = @ORDER_NO)

If there are multiple tables you must consider querying, I assume the list of possible tables is finite so use IF's to pick the table you need. (But look carefully at your database design if this is the case)

I would only use dynamic SQL as a last resort, and it looks like you may not need to use it.


- Jeff

Edited by - jsmith8858 on 11/28/2002 09:50:33
Go to Top of Page

deadfish
Starting Member

38 Posts

Posted - 11/28/2002 :  10:24:47  Show Profile  Reply with Quote
Because this script is to be called by different programs, the table name cannot be fixed. i.e. it may not be 'ABC' when it is called by another program....
but if I write the following,

SET @ORDER_NO='001'
set @table = 'DEF'
SET @QTY=(SELECT SUM(QTY) FROM @table WHERE ORDER_NO = @ORDER_NO)

I got the error message: "Must declare the variable '@table'."

Any idea?

Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 12/01/2002 :  12:20:10  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
You can't use a variable like that, in a SELECT * FROM @Var statement, as far as I know.

If there are, say, 3 tables to choose from, you could always do it like this:

IF @Table = 'DEF'
SELECT * FROM DEF;
IF @Table = 'ABC'
SELECT * FROM ABC;
IF @Table = 'GHI'
SELECT * FROM GHI;

If there are tons of tables to choose from, and they are all using the same stored proc, why are they in seperate tables?

That is, if you have 10 tables, all with the same structure and same kind of data, put them all in 1 table and add a key field to distinguish the data from each table.

See

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=21961

for something similar.


- Jeff


Edited by - jsmith8858 on 12/02/2002 11:22:19
Go to Top of Page

damcalcan
Starting Member

Canada
11 Posts

Posted - 12/02/2002 :  10:36:20  Show Profile  Reply with Quote
you can do it using sp_executesql.

DECLARE @sql NVARCHAR(500) , @qty int

SET @sql = (SELECT @qty = SUM(QTY) FROM '+@TABLE+' WHERE ORDER_NO = '''+@ORDER_NO+
'''

EXEC sp_executesql @sql , N'@qty int OUTPUT' , @qty OUTPUT

--just for checking
PRINT @qty

The question is ...what would you need to do that

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000