Link to home
Start Free TrialLog in
Avatar of Anthony Cardullo
Anthony CardulloFlag for United States of America

asked on

optional variables

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
ASKER CERTIFIED SOLUTION
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
or more like this
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
@ewangoya: since the direct comparison to '' will also filter NULLs, isn't the original method I showed work as stated http:#a35781745 ?

Correct,

I only intended to show the optional parameter
CREATE PROCEDURE dbo.foo @param1 VARCHAR(5) = NULL,  @param2 VARCHAR(5) = NULL
That makes sense. Without any explanatory text, it just seemed odd. Got you now. Hopefully this helps the Asker. :)