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
