Solved

SQL Stored procedure

Posted on 2011-03-21
11
246 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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 143

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

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 …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

691 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