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.
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=0SET @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 = 4set @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 = 4set @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.- JeffEdited 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.Seehttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=21961for something similar.- JeffEdited 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 intSET @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 @qtyThe question is ...what would you need to do that |
|
|
|
|
|
|
|