Simple question about EXEC - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Simple question about EXEC

Author

Topic

deadfish
Starting Member

38 Posts

Posted - 2002-11-28 : 04:53:50

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

633 Posts

Posted - 2002-11-28 : 05:18:07

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)

deadfish
Starting Member

38 Posts

Posted - 2002-11-28 : 09:23:19

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-28 : 09:49:27

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

deadfish
Starting Member

38 Posts

Posted - 2002-11-28 : 10:24:47

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?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-01 : 12:20:10

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

damcalcan
Starting Member

11 Posts

Posted - 2002-12-02 : 10:36:20

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources