Solved

SQL Stored procedure

Posted on 2011-03-21
11
241 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
 

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now