Problem select query for number 2?

Question

I just creates this stored procedure in selecting palletnumber=datatype is int. SerialNumber is varchar. Then pallet numbers are 1 - 200..But if i enter 2 it shows 2 with other palletnumbers. I change @search to nchar and its ok but serialnumber is has error. Im new in stored procedure.

ALTER PROCEDURE [dbo].[sp_SearchFresh]
    -- Add the parameters for the stored procedure here
    @Search varchar(50)--so that i only have one search engine/input box
AS
SELECt  dbo.Monitor.SerialNumber, 
      WIP.dbo.WIPTEST.PartNumber,
      dbo.Monitor.PalletNumber
FROM  WIP.dbo.WIPTEST 
      INNER JOIN dbo.Monitor 
         ON WIP.dbo.WIPTEST.SerialNumber = dbo.Monitor.SerialNumber
WHERE WIP.dbo.WIPTEST.StatusDescription = 'FG-FRESH' 
    AND (dbo.Monitor.PalletNumber = @Search 
        OR dbo.Monitor.SerialNumber LIKE @Search + '%' )
END

it works..i will practice dynamic SQL. But how can i supply null values on my ui? this is my DAL code

public DataSet FGSearch_Grid(Ientity user) {

        SqlConnection conn = new SqlConnection(connStr);
        SqlCommand cmd = new SqlCommand("sp_SearchFresh", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        DataSet dSet = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        cmd.Parameters.Add("@Search", SqlDbType.VarChar, 50).Value = user.Search;

        try
        {
            conn.Open();

            da.Fill(dSet, "WIPDATA");

            conn.Close();
        }
        catch (SqlException)
        {
            throw;
        }
        return dSet;
    }

Answer 1

Make sure that the pallet numbers that you are getting doesn't have a serial number starting with 2?

Answer 2

To have the @Search and palletnumber evaluate as int try

AND (dbo.Monitor.PalletNumber = cast(@Search as int)

Answer 3

You're quickly finding that using a single parameter doesn't work well to serve multiple filter criteria that has different data types. Make things easier on yourself, and accept that for every separate filter criteria you want to provide, must have a separate stored procedure arguement parameter.

ALTER PROCEDURE [dbo].[sp_SearchFresh]    
  @Searchvarchar(50),
  @pallet_num INT

AS

  SELECT dbo.Monitor.SerialNumber,      
              WIP.dbo.WIPTEST.PartNumber,      
              dbo.Monitor.PalletNumber
    FROM  WIP.dbo.WIPTEST       
      JOIN dbo.Monitor ON WIP.dbo.WIPTEST.SerialNumber = dbo.Monitor.SerialNumber
  WHERE WIP.dbo.WIPTEST.StatusDescription = 'FG-FRESH'   
     AND (@pallet_num IS NULL OR dbo.Monitor.PalletNumber = @pallet_num)
     AND (@Search IS NULL OR dbo.Monitor.SerialNumber LIKE @Search+'%')

END

Answer 4

Be careful with "Catch All Queries" since they always cause a full table scan. If you don't know them, have a look at Gail Shawn's blog Catch All Queries.

The only way to ensure index utilization with optional arguments is dynamic SQL (ensure using sp_executesql to avoid SQL injection):

ALTER PROCEDURE [dbo].[sp_SearchFresh]    
  @search varchar(50),
  @pallet_num INT
AS

DECLARE @sql NVARCHAR(4000);

SET @sql = N'
  SELECT dbo.Monitor.SerialNumber,      
              WIP.dbo.WIPTEST.PartNumber,      
              dbo.Monitor.PalletNumber
    FROM  WIP.dbo.WIPTEST       
      JOIN dbo.Monitor ON WIP.dbo.WIPTEST.SerialNumber = dbo.Monitor.SerialNumber
  WHERE WIP.dbo.WIPTEST.StatusDescription = ''FG-FRESH'''

IF (@search IS NOT NULL)
BEGIN
   SET @sql = @sql + ' AND dbo.Monitor.SerialNumber LIKE @searchIN';
   SET @search = REPLACE(@search, '%', '%%') + '%';
END

IF (@pallet_num IS NOT NULL)
   SET @sql = @sql + ' AND dbo.Monitor.PalletNumber = @pallet_numIN';

EXECUTE sp_executesql 
            @sql
            ,N'@searchIN VARCHAR(50), @pallet_numIN INT'
            ,@searchIN = @search
            ,@pallet_numIN = @pallet_num;

Greets Flo