optional variables

Anthony Cardullo
Anthony Cardullo used Ask the Experts™
on
I am trying to build a procedsure that can take either one of two variables and run two different sql statesments. roughly liek this

declare var1,var2

if var1 > '' then

select * from table1

or if var2 is > '' then
select * from table2

i also want to do nothing if both var1 and var 2 are empty

thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chief Technology Officer
Most Valuable Expert 2011
Commented:
CREATE PROCEDURE sp_YourProcedureName 
   -- Add the parameters for the stored procedure here
   @var1 VARCHAR(20), -- change to appropriate data type
   @var2 VARCHAR(20)
AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;
   
   IF (@var1 <> '') BEGIN
      -- do something
      SELECT * FROM Table1;
   END

   IF (@var2 <> '') BEGIN
      -- do something else
      SELECT * FROM Table2;
   END
END
GO

Open in new window

http://msdn.microsoft.com/en-us/library/ms187926.aspx

You will need to consider what happens if both have a valid value.  If you only want one to execute, then you may want to setup as IF/THEN/ELSE and have the other variable checked in the ELSE of the one you want to take precedence if both are available.
Ephraim WangoyaSoftware Engineer
Commented:


CREATE PROCEDURE dbo.foo
    @param1 VARCHAR(5) = NULL,
    @param2 VARCHAR(5) = NULL
AS
BEGIN
    SET NOCOUNT ON
 
    IF (@param1 <> '') BEGIN
      SELECT * FROM Table1;
   END

   IF (@param2 <> '') BEGIN
       SELECT * FROM Table2;
   END
END
Ephraim WangoyaSoftware Engineer

Commented:
or more like this
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Ephraim WangoyaSoftware Engineer

Commented:
CREATE PROCEDURE dbo.foo
    @param1 VARCHAR(5) = NULL,
    @param2 VARCHAR(5) = NULL
AS
BEGIN
    SET NOCOUNT ON
 
    IF (@param1 is not null) BEGIN
      SELECT * FROM Table1;
   END

   IF (@param2 is not null) BEGIN
       SELECT * FROM Table2;
   END
END
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
@ewangoya: since the direct comparison to '' will also filter NULLs, isn't the original method I showed work as stated http:#a35781745 ?
Ephraim WangoyaSoftware Engineer

Commented:

Correct,

I only intended to show the optional parameter
CREATE PROCEDURE dbo.foo @param1 VARCHAR(5) = NULL,  @param2 VARCHAR(5) = NULL
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
That makes sense. Without any explanatory text, it just seemed odd. Got you now. Hopefully this helps the Asker. :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial