Procedure with multiple parameters and multiple Select Statements

[rodney fetterolf]

If someone could give me some guidance with the following problem. I have a table; tblItems; which fields fldItem(nvarchar 75), fldNSN(nvarchar 16), fldPartNum, fldVendor, fldPrice... I would like to have a stored procedure that can search and return a dataset based upon which parameter the user enters. I thought if I created a procedure like this, it would work but I keep getting some error in building the procedure Incorrect Syntax Near the Keyword 'Select' or Invalid operator for datatype. Operator equals modulo, type equals varchar; Create Proc usp_SearchItems @pName nvarchar(75), @pNSN nvarchar(16), @pTPart nvarchar(20), @pVPart nvarchar(50) AS Declare @pQuery nvarchar(1024) if @pName<>'' set @pQuery=Select * From tblItems Where (fldInactive=0) and (fldItem Like '%' + @pName + '%'; Order By fldItem if @pNSN<>'' Set @pQuery=Select * From tblItems Where (fldInactive=0) and (fldNSN Like '%' + @pNSN + '%'; exec @pQuery I don't have a problem if I use Create Proc usp_SearchItem @pName nvarchar(75) As Select * From tblItems Where (fldInactive=0) and fldItem Like '%' + @pName + '%' I'm trying to use one procedure instead of four. Any assistance would be greatly appreciated.

[andyharman]

Try the following:

CREATE PROCEDURE usp_SearchItems
    @pName NVARCHAR(75),
    @pNSN NVARCHAR(16),
    @pTPart NVARCHAR(20),
    @pVPart NVARCHAR(50)
AS BEGIN
    DECLARE @pQuery NVARCHAR(1024)

    SET @pQuery = 'SELECT * FROM tblItems WHERE (fldInactive = 0)'
    IF (@pName<>'') BEGIN
        SET @pQuery = @pQuery + ' AND (fldItem LIKE ''%' + REPLACE(@pName, '''', '''''') + '%'')'
    END
    IF (@pNSN<>'') BEGIN
        SET @pQuery = @pQuery + ' AND (fldNSN LIKE ''%' + REPLACE(@pNSN, '''', '''''') + '%'')'
    END
    SET @pQuery = @pQuery + ' ORDER BY fldItem'

    --PRINT @pQuery
    EXEC(@pQuery)
END

If you have any problems then uncomment the "PRINT" statement to find out what the final contructed query was. The "REPLACE" function is being used to protect the stored procedure against quote characters. Do a search for "SQL Injection Attacks" to find out more about this problem. Regards Andy

[M H 1 2 3]

You could use something like this. Default the parameters to null then coalesce or inull the parameter. The example proc below returns one result set but if you wanted to you could just as easily return one result set per parameter passed in. Create Proc usp_SearchItems @pName nvarchar(75) = null, @pNSN nvarchar(16)= null, @pTPart nvarchar(20)=null, @pVPart nvarchar(50) =null AS Select * From tblItems Where (fldInactive=0) and fldItem Like '%' + coalesce( @pName , fldItem) + '%' and fldNSN Like '%' + coalesce( @pNSN , fldNSN)+ '%' ....