SQL Server conditional select depending on input parameter

I have a stored procedure in MS SQL, that takes 2 parameteres, for example

CREATE PROCEDURE MyProcedure
    @A      INT,
    @B      INT
AS
BEGIN
    SELECT  ...

    FROM    ...

    WHERE   [A] = @A AND [B] = @B 

END

My question is.开发者_开发知识库 if i have @B = 0 i want in the select row like this where [A]=@A,wthout the [B]=@B.. how can i achieve this with a minimum code? thanks alot

---

Try the following:

SELECT  ...    
FROM    ...    
WHERE   [A] = @A AND 
        (@B = 0 OR [B] = @B)

---

WHERE
   [A] = @A AND [B] = ISNULL(NULLIF(@B, 0), B)

---

You might wanna try something like this:

CREATE PROCEDURE MyProcedure
    @A      INT,
    @B      INT
AS
BEGIN
    DECLARE @sql varchar(max)
    DECLARE @whereStat varchar(max)
    IF @B = 0 
       SET @whereStat = 'WHERE [A] = ' + @A
    ELSE
       SET @whereStat = 'WHERE [A] = ' + @A + 'AND [B] = '+ @B 

    SET @sql = 'SELECT  ...

    FROM    ... '+ @whereStat
    exec sp_execsql @sql

END

Basically you dynamically create a sql string and execute it through system stored procedure. It might not be the best way/quickest way, but it's another alternative. :)

---

It's an ugly solution but simple:

If @B = 0 begin
   ...your sql query without the B = @B
end
else if @B <> 0 begin
   ... your sql query with the B = @B
end