Stored Procedure help - 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

SQL Server Development (2000)

Stored Procedure help

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-10-28 : 10:00:09

I created this procedure, but it doesnt return any results when I execute it. If i hardcode the cus_no in a query, it works fine.
Also, the value for the cus_no will be the same in both queries.

CREATE PROCEDURE Product_Predictor (
@arg_Cus_No VARCHAR(255))
AS
BEGIN
SELECT Item_No AS Item_Number, request_dt AS Request_Date, SUM(qty_Ordered) AS Quantity_Ordered
FROM OELINHST_SQL
WHERE (Cus_No = '@arg_Cus_No') AND (Ord_Type = 'O') AND (Qty_Bkord = '0') AND item_no IN
(SELECT item_no FROM OELINHST_SQL
WHERE Cus_No = '@arg_Cus_No'
AND Ord_Type = 'o'
AND Qty_Bkord = '0'
GROUP BY item_no HAVING count(item_no) > 2)
GROUP BY item_no, request_dt
ORDER BY item_no DESC
END;

GO

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-28 : 10:06:35

change
WHERE Cus_No = '@arg_Cus_No'
to
WHERE Cus_No = @arg_Cus_No

Go with the flow & have fun! Else fight the flow

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-10-28 : 10:10:38

That worked. Thank you. Why does that work though? cus_no is varchar.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-28 : 10:16:03

because
WHERE Cus_No = '@arg_Cus_No'
will look for cus_no that has the value of '@arg_Cus_No'

WHERE Cus_No = @arg_Cus_No
will look for cus_no that has the value stored in variable @arg_Cus_No

Go with the flow & have fun! Else fight the flow

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-10-28 : 10:28:07

Ok..got it! Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources