problem to create SP - 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

problem to create SP

Author

Topic

eugz
Posting Yak Master

210 Posts

Posted - 2010-03-02 : 10:24:11

Hi All.
I have Address and Phone tables that have Current fields. That field I used to indicate current address or phone if they equal 1. If Current field in according tables equal 0 it means old address or phone. Now I would like to create stored procedure to return ONLY old addresses and phone and stored procedure to return ONLY new addresses and phones. How it to do?
Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 10:26:58

just use a filter condition like
WHERE yourfield=@status

and pass @status as 1 for new and 0 for old

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-02 : 10:28:00

Make one SP.
Give parameter 0 or 1 for Current.
Use parameter in your WHERE clause.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-02 : 10:28:39

oh no

No, you're never too old to Yak'n'Roll if you're too young to die.

eugz
Posting Yak Master

210 Posts

Posted - 2010-03-02 : 10:52:09

Thanks for replay.
I used WHERE statement in SP for old address and phone like:
where (address.[Current] = 0 OR address.[Current] IS NULL)
and (phone.[Current] = 0 OR phone.[Current] IS NULL)

in SP for new address and phone like:
address.[Current] = 1 and phone.[Current] = 1

but when, for example, address changed and phone not result nothing. That is my problem.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 10:55:22

quote:
Originally posted by eugz

Thanks for replay.
I used WHERE statement in SP for old address and phone like:
where (address.[Current] = 0 OR address.[Current] IS NULL)
and (phone.[Current] = 0 OR phone.[Current] IS NULL)

in SP for new address and phone like:
address.[Current] = 1 and phone.[Current] = 1

but when, for example, address changed and phone not result nothing. That is my problem.

Thanks.

show your full query please

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-02 : 10:56:24

Then you should define the possible cases and the wanted reaction/output.

No, you're never too old to Yak'n'Roll if you're too young to die.

eugz
Posting Yak Master

210 Posts

Posted - 2010-03-02 : 11:14:47

That is my query:
select
a.Address
,p.Phone
,a.current
,p.current
from Employee e
JOIN Phone p
ON e.Emp_Id = p.id
join Address a
on e.Emp_Id = a.Emp_Id
where (a.[Current] = 0 OR a.[Current] IS NULL)
and (p.[Current] = 0 OR p.[Current] IS NULL)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 11:19:15

may be this

select
a.Address
,p.Phone
,a.current
,p.current
from Employee e
left JOIN Phone p
ON e.Emp_Id = p.id
left join Address a
on e.Emp_Id = a.Emp_Id
where (a.[Current] = 0 OR a.[Current] IS NULL)
and (p.[Current] = 0 OR p.[Current] IS NULL)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-02 : 11:21:36

The easy way is to give 2 parameters

Something like this:
select
a.Address,
p.Phone,
...
from Employee e
join Phone p
on e.Emp_Id = p.id
join Address a
on e.Emp_Id = a.Emp_Id
where (a.[Current] = @ACurrent OR @ACurrent IS NULL)
and (p.[Current] = @PCurrent OR @PCurrent IS NULL)

So you can give 2 values to fit your needs.

No, you're never too old to Yak'n'Roll if you're too young to die.

eugz
Posting Yak Master

210 Posts

Posted - 2010-03-02 : 12:46:34

Thanks for help.
Can you explaine why your WHERE statement better? I just would like to return records that has old address, phone and new address, phone. I tried LEFT statement, it doesn't work.
Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 12:51:51

if you want both why you need to filter?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

eugz
Posting Yak Master

210 Posts

Posted - 2010-03-03 : 00:32:39

I need SP that return only current address and phone. And SP that return only old address and phone.
Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 09:24:48

quote:
Originally posted by eugz

I need SP that return only current address and phone. And SP that return only old address and phone.
Thanks.

no need of two sps
just use a parameter and then filter bit value based on it to get 0 (old) or 1 (new)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-03 : 09:31:33

quote:
but when, for example, address changed and phone not result nothing. That is my problem.

quote:
Then you should define the possible cases and the wanted reaction/output.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources