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 2008 Forums

Transact-SQL (2008)

stored procedure help

Author

Topic

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-04-02 : 15:21:39

Here are the two tables, CUSTOMER and PRODUCTS

CUSTOMER

cus_id email_adr email_flag
1 x y
2 y n
3 z n

PRODUCTS

cus_id prod_cd prod_state
1 p Y
1 q N
1 r Y
2 p Y
2 q Y
3 p N
3 q N
3 r Y
3 s N

What is the stored procedure to set email_flag to Y when for each cus_id the prod_state is Y for atleast one prod_cd and to set email_flag to N when for each cus_id the prod_state is N for all prod_cd?

list of pro_cds is (p,q,r,s,t,u) and also I am passing the parameter @email for email_adr from my c# application

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-04-02 : 16:14:49

I'm not clear what purpose the email address has as a parameter since you seem to be updating the entire table. See if this leads you in the right direction:[CODE]create table #CUSTOMER ( --Stuff you should be providing
cus_id int,
email_adr varchar(256),
email_flag char(1)
)

insert into #CUSTOMER
values
(1, 'x', 'y'),
(2, 'y', 'n'),
(3, 'z', 'n')

create table #PRODUCTS (
cus_id int,
prod_cd varchar(50),
prod_state char(1)
)

insert into #PRODUCTS
values
(1, 'p', 'Y'),
(1, 'q', 'N'),
(1, 'r', 'Y'),
(2, 'p', 'Y'),
(2, 'q', 'Y'),
(3, 'p', 'N'),
(3, 'q', 'N'),
(3, 'r', 'Y'),
(3, 's', 'N')
go
create proc dbo.MyProc(@email varchar(256)) -- Start of solution
as
update c
set email_flag = a.prod_state
--select *
from #CUSTOMER c
inner join (
select c1.cus_id, max(p.prod_state) prod_state
from #CUSTOMER c1
inner join #PRODUCTS p
on c1.cus_id = p.cus_id
group by
c1.cus_id
) a
on a.cus_id = c.cus_id
go
exec dbo.MyProc 'What is the email address for?'

select *
from #CUSTOMER[/CODE]

=================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-04-02 : 18:08:33

[code]
UPDATE c
SET
email_adr = @email, --??
email_flag = CASE WHEN EXISTS(
SELECT 1
FROM dbo.PRODUCTS p
WHERE
p.cus_id = c.cus_id AND
p.prod_cd IN ('p','q','r','s','t','u') AND
p.prod_state = 'Y'
)
THEN 'Y' ELSE 'N' END
FROM dbo.CUSTOMER c
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-03 : 01:49:20

[code]
UPDATE c
SET c.email_flag = CASE WHEN Ycnt > 0 THEN 'Y' ELSE 'N' END
FROM CUSTOMER c
INNER JOIN (
SELECT cus_id,
SUM(CASE WHEN prod_state='Y' THEN 1 ELSE 0 END) AS Ycnt
FROM PRODUCTS
GROUP BY cus_id
)p
ON p.cus_id = c.cus_id
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-04-03 : 12:05:29

Thanks man, that worked.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-03 : 13:50:36

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources