SQL Stored procedure

Hello,

We currenlty use the following stored procedure:

PROCEDURE [dbo].[Proc_Customer] (@PosID int, @ZipCode int) AS
BEGIN
SELECT Name
FROM TableCustomer
WHERE Running = 1
AND PosID = @PosID
AND ZipCode = @ZipCode
END

but we look for a solution for:

when the last AND value (ZipCode) is equal a certain value (example if = 0) then this AND request is not taken in account.

I hope the question is clear ...
fmorelleAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this kind of condition shall do:

AND (ZipCode = @ZipCode OR @ZipCode = 0)
0
 
Atdhe NuhiuCommented:
Do you mena something like this?

PROCEDURE [dbo].[Proc_Customer] (@PosID int, @ZipCode int) AS
BEGIN
SELECT Name
FROM TableCustomer
WHERE Running = 1
AND PosID = @PosID
AND (ZipCode = @ZipCode OR ZipCode = 0)
END

0
 
Lee SavidgeCommented:

PROCEDURE [dbo].[Proc_Customer] (@PosID int, @ZipCode int) AS
BEGIN
if @ZipCode = 0
begin
    SELECT Name
    FROM TableCustomer
    WHERE Running = 1
    AND PosID = @PosID
end
else
begin
    SELECT Name
    FROM TableCustomer
    WHERE Running = 1
    AND PosID = @PosID
    AND ZipCode = @ZipCode
end
END

Open in new window

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Atdhe NuhiuCommented:
or perhaps


PROCEDURE [dbo].[Proc_Customer] (@PosID int, @ZipCode int) AS
BEGIN
SELECT Name
FROM TableCustomer
WHERE Running = 1
AND PosID = @PosID
AND (ZipCode = @ZipCode AND @ZipCode <> 0)
END

0
 
fmorelleAuthor Commented:
Thanks for the quick answer.
The solution of Isavidge is good for my problem, but I'm searching for an other solution with "If... " inside SELECT, and note repeat all the  procedure.
0
 
fmorelleAuthor Commented:
Thanks for the quick answer.
The solution of Isavidge is good for my problem, but I'm searching for an other solution with "If... " inside WHERE, and note repeat all the  procedure.
0
 
Lee SavidgeCommented:
Dynamics SQL then is your only option.
0
 
Lee SavidgeCommented:

PROCEDURE [dbo].[Proc_Customer] (@PosID int, @ZipCode int) AS
BEGIN
declare @sSQL nvarchar(500)

select @sSQL = 'SELECT Name FROM TableCustomer WHERE Running = 1 AND PosID = @PosID'

if @ZipCode <> 0 select @sSQL = @sSQL + ' AND ZipCode = @ZipCode'

exec(@sSQL)
END

Open in new window

0
 
fmorelleAuthor Commented:
Thanks for all the answers.
I need to make a pause in my job for 3 or 4 hours.
I'll come back after.
0
 
fmorelleAuthor Commented:
The answer of angelIII is exactly what I'm looking for !
Thank you very much.
0
 
fmorelleAuthor Commented:
Than you very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.