Solved

SQL Stored procedure

Posted on 2011-03-21
11
243 Views
Last Modified: 2012-05-11
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
Comment
Question by:fmorelle
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 13

Expert Comment

by:Philip Pinnell
ID: 35181647
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
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 35181654

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
 
LVL 13

Expert Comment

by:Philip Pinnell
ID: 35181662
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:fmorelle
ID: 35181773
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
 

Author Comment

by:fmorelle
ID: 35181792
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
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 35181830
Dynamics SQL then is your only option.
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 35181866

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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
ID: 35181968
this kind of condition shall do:

AND (ZipCode = @ZipCode OR @ZipCode = 0)
0
 

Author Comment

by:fmorelle
ID: 35182216
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
 

Author Comment

by:fmorelle
ID: 35184717
The answer of angelIII is exactly what I'm looking for !
Thank you very much.
0
 

Author Closing Comment

by:fmorelle
ID: 35184725
Than you very much
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question