Using varchar parameter in exec()

Question

I am trying to execute my whole stored procedure as a string since I have to make the condition dynamic. Here is my code:

CREATE PROCEDURE SP1
  (@VoucherType varchar(10),
  @ProductID bigint,
  @BrandID bigint)
AS
BEGIN

DECLARE @Condition as varchar(300)
SET @Condition=' WHERE VoucherType=@VoucherType '
IF (@ProductID<>-1)
  BEGIN
  SET @Condition=@Condition+' AND ProductID='+cast(@ProductID as varchar)
  END
IF (BrandID<>-1)
  BEGIN
  SET @Condition=@Condition+' AND BrandID='+cast(@BrandID as varchar)
  END
EXEC('SELECT * FROM Products '+@Condition)

END

Filtering by ProductID and BrandID are optional (if their values are not -1 then those conditions are added to where clause). And filtering by VoucherType is compulsory. The problem is that I can't get the value of the parameter @VoucherType in line:

SET @Condition=' WHERE VoucherType=@VoucherType '

The error says there is no column Named SI (Which was my input for @VoucherType). How can I get the value of that parameter.

Answer 1

Just do it without a dynamic SQL:

SELECT * FROM Products WHERE VoucherType=@VoucherType AND 
  (@ProductID=-1 OR  ProductID=@ProductID)
  AND 
  (@BrandID=-1 OR  BrandID=@BrandID)