stored procedure problem. - 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

General SQL Server Forums

New to SQL Server Programming

stored procedure problem.

Author

Topic

argon007
Starting Member

38 Posts

Posted - 2008-06-20 : 20:46:35

quote:
sp_del_inactive_cust to list customers that have no orders. The stored procedure should list 1 row.

Alter procedure sp_del_inactive_cust
(
	@order_id id
)
AS 
SELECT c.*
FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE @order_id = o.order_id

/* running the procedure */

execute sp_del_inactive_cust NULL

the problem is when i list the results...

quote:
customer_id name contact_name title_id address city region country_code country phone fax active_flg
----------- -------------------------------------------------- ------------------------------ -------- -------------------------------------------------- -------------------- --------------- ------------ --------------- -------------------- -------------------- ----------

(0 row(s) affected)

but there should be 1 row......

what should i do?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-06-21 : 00:50:18

you get nothing back because you are passing NULL for the @order_id. "where order_id=null" will never match anything, because to test for null you have to use the "is" keyword, not = operator.

pass an actual value there, for a real order that is actually in your order table, and you may see stuff returned.

elsasoft.org

ranganath
Posting Yak Master

209 Posts

Posted - 2008-06-21 : 01:23:54

hi,

try with this

Alter procedure sp_del_inactive_cust
(
@order_id INT = NULL
)
AS
SELECT c.*
FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE (@order_id is Null or @order_id = o.order_id)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-21 : 01:29:21

or use WHERE o.order_id =coalesce(@order_id,o.order_id)

pkokkula
Starting Member

3 Posts

Posted - 2008-06-21 : 01:34:53

Hai

You can also write as below.

Alter procedure sp_del_inactive_cust
(
@order_id INT = NULL
)
AS
SELECT c.*
FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE ISNULL(@order_id,'') = ISNULL(o.order_id,''))

Pavan Kokkula
http://www.ggktech.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-21 : 01:42:04

quote:
Originally posted by pkokkula

Hai

You can also write as below.

Alter procedure sp_del_inactive_cust
(
@order_id INT = NULL
)
AS
SELECT c.*
FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE ISNULL(@order_id,'') = ISNULL(o.order_id,''))

Pavan Kokkula
http://www.ggktech.com/

Dont think that it will give the results OP wants when @Order_id is null. in that case left side becomes '' where right side it takes field values and tries to make it '' if NULL which returns the records with order_id as NULL value only, if any. I think what OP wants is to return all records when @order_id is NULL (default value) and return only matching records when @order_id is not null.

argon007
Starting Member

38 Posts

Posted - 2008-06-21 : 18:11:01

[code]
Alter procedure sp_del_inactive_cust
(
@order_id id = NULL
)
AS
SELECT c.*
FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE (@order_id like o.order_id)
[/code]

I don't know why i got nothing in the results...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-22 : 03:12:46

quote:
Originally posted by argon007

Alter procedure sp_del_inactive_cust
(
@order_id id = NULL
)
AS
SELECT c.*
FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE (@order_id like o.order_id)

I don't know why i got nothing in the results...

why did you use like?
wont this be enough?

(o.order_id=COALESCE(@order_id,o.order_id))

is your order_id field varchar? also would you be passing more than 1 id through @order_id?

argon007
Starting Member

38 Posts

Posted - 2008-06-22 : 03:27:50

no, user-defined data types. char(10)
what do you mean by that:
would you be passing more than 1 id through @order_id?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-22 : 05:09:00

quote:
Originally posted by argon007

no, user-defined data types. char(10)
what do you mean by that:
would you be passing more than 1 id through @order_id?

i was asking if you were planning to pass multiple order_ids as comma seperated values through parameter @order_id. I thought that might be reason why you used like.
b/w if you're using like i think you need to change where like this

WHERE o.order_id like @order_id + '%'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-22 : 05:13:14

If you want to list customers who has no orders, why are you passing an orderid into the procedure?

ALTER PROCEDURE sp_del_inactive_cust
(
)
AS

set nocount on

SELECT		c.*
FROM		customers as c
LEFT JOIN	orders as o ON o.customer_id = c.customer_id
WHERE		o.customer_id is null

E 12°55'05.25"
N 56°04'39.16"

argon007
Starting Member

38 Posts

Posted - 2008-06-22 : 12:43:46

Thank you. problem solved.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-22 : 12:48:42

remove the braces

ALTER PROCEDURE sp_del_inactive_cust
AS

set nocount on

SELECT		c.*
FROM		customers as c
LEFT JOIN	orders as o ON o.customer_id = c.customer_id
WHERE		o.customer_id is null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources