Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2005
    Posts
    74

    Unanswered: Help with CASE and LIKE

    Hello guys! i'm having problem with my stored procedure..can anybody please help me.
    I have a stored procedure below that is successfully executed/saved/"Compiled"(whatever you called it) but when I try to use it by supplying value to its paramaters it throws an error (Please see the error message below). I suspected that the error occurs from line with the Bold Letters becuase "@SeacrhArg" variable is of type varchar while columns "Transac.Item_ID" and "Transac.Item_TransTicketNo" is of type Int. What you think guys?

    ERROR:
    Msg 245, Level 16, State 1, Procedure sp_Transaction_Search, Line 9
    Syntax error converting the varchar value 'Manlagnit' to a column of data type int.

    STORED PROCEDURE:
    Code:
    USE [RuslinCellPawnShoppeDB]
    GO
    /****** Object:  StoredProcedure [dbo].[sp_Transaction_Search]    Script Date: 09/04/2007 08:48:38 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[sp_Transaction_Search]
        @SeacrhArg varchar(20),
        @SearchBy varchar(20),
        @TransType varchar(20),
        @FromDate datetime,
        @Todate datetime
    AS
    BEGIN
        SELECT  Customer.Customer_LastName,Customer.Customer_MiddleInitial, Customer.Customer_FirstName, Customer.Customer_Address, 
                Items.Item_Description,Items.Item_Principal, Transac.ItemTrans_Date_Granted, Transac.ItemTrans_DateCreated,
                Transac.ItemTrans_Status, Transac.Item_ID,Transac.Item_TransID,Transac.Item_TransTicketNo
     
     
        FROM RCPS_TF_ItemTransaction Transac
            INNER JOIN RCPS_Customer Customer
                ON Transac.CustomerID = Customer.CustomerID
            INNER JOIN RCPS_Items Items
                ON Items.ItemID = Transac.Item_ID
     
        WHERE    
                CASE 
                    WHEN @SearchBy = 'FirstName' THEN Customer.Customer_FirstName
                    WHEN @SearchBy = 'LastName' THEN Customer.Customer_LastName
                    WHEN @SearchBy = 'Item ID' THEN Transac.Item_ID
                    WHEN @SearchBy = 'Ticket No' THEN Transac.Item_TransTicketNo
                END 
                LIKE @SeacrhArg AND
     
                Transac.ItemTrans_DateCreated BETWEEN @FromDate AND dateadd(day,1,@Todate) AND
                (
                    (@TransType = 'Pawned' AND Transac.ItemTrans_Status = 1) OR
                    (@TransType = 'Renewed' AND Transac.ItemTrans_Status = 2) OR
                    (@TransType = 'Redeemed' AND Transac.ItemTrans_Status = 3) OR
                    (@TransType = 'Sold' AND Transac.ItemTrans_Status = 5)
                )
    END
    CALL STORED PROCEDURE
    USE [RuslinCellPawnShoppeDB]
    GO

    DECLARE @return_value int

    EXEC @return_value = [dbo].[sp_Transaction_Search]
    @SeacrhArg = '%man%',
    @SearchBy = 'LastName',
    @TransType = 'Pawned',
    @FromDate = N'9/01/2007 12:00:00 AM',
    @Todate = N'9/6/2007 12:00:00 AM'

    SELECT 'Return Value' = @return_value

    GO
    Last edited by daimous; 09-04-07 at 02:06.

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    I suspect Transac.ItemTrans_Status is a varchar and that is where the problem is

  3. #3
    Join Date
    Apr 2007
    Posts
    183
    Code:
    WHERE		CASE    
    			WHEN @SearchBy = 'FirstName' AND Customer.Customer_FirstName LIKE @SeacrhArg THEN 1
    	                WHEN @SearchBy = 'LastName' THEN Customer.Customer_LastName LIKE @SeacrhArg THEN 1
    		        WHEN @SearchBy = 'Item ID' THEN CONVERT(VARCHAR, Transac.Item_ID) LIKE @SeacrhArg THEN 1
    	                WHEN @SearchBy = 'Ticket No' THEN CONVERT(VARCHAR, Transac.Item_TransTicketNo) LIKE @SeacrhArg THEN 1
    			ELSE 0
    		END = 1
    		AND Transac.ItemTrans_DateCreated >= @FromDate
    		AND Transac.ItemTrans_DateCreated < DATEADD(DAY, 1, @Todate)
    		AND 1 =	CASE
    				WHEN @TransType = 'Pawned' AND Transac.ItemTrans_Status = 1 THEN 1
    				WHEN @TransType = 'Renewed' AND Transac.ItemTrans_Status = 2 THEN 1
    				WHEN @TransType = 'Redeemed' AND Transac.ItemTrans_Status = 3 THEN 1
    				WHEN @TransType = 'Sold' AND Transac.ItemTrans_Status = 5 THEN 1
    				ELSE 0
    			END

  4. #4
    Join Date
    Sep 2005
    Posts
    74
    Thanks a loT Peso..But I preferred to use BETWEEN..AND.. for date unless you have there agood reason to use the <>= sign..But anyway thanks alot!
    Last edited by daimous; 09-04-07 at 04:37.

  5. #5
    Join Date
    Apr 2007
    Posts
    183
    if you use BETWEEN, you inadvertently get too may records.

    BETWEEN '20070101' AND '20071231' will NOT get the records dated "20071231 10:14:23"

    BETWEEN '20070101' AND '20080101' will get the records dated "20071231 10:14:23" but ALSO the records dated "20080101 00:00:00".

    >= '20070101' <'20080101' will get the records dated "20071231 10:14:23" but NOT the records dated "20080101 00:00:00".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •