Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

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 ...
0
fmorelle
Asked:
fmorelle
  • 5
  • 3
  • 2
  • +1
1 Solution
 
Philip PinnellCommented:
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
 
Philip PinnellCommented:
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this kind of condition shall do:

AND (ZipCode = @ZipCode OR @ZipCode = 0)
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now