vendredi 10 juin 2016

Executing the stored procedure causes error

I have a stored procedure in which I want to get the reportdate while executing.

I pass one parameter to the SP for executing it,. I pass it like this

exec UserReportData '10-06-2016'

but I get an error:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

This is my stored procedure:

ALTER PROCEDURE [dbo].[UserReportData] 
    @As_ONDATE Datetime 
AS 
BEGIN 
    DECLARE @REPORTDATE datetime        
    --DECLARE @OPENING INT      

    SELECT * 
    INTO #temptable
    FROM
        (SELECT 
             a.CUser_id, b.User_Id, a.U_datetime AS REPORTDATE
         FROM 
             inward_doc_tracking_trl a, user_mst b
         WHERE
             a.CUser_id = b.mkey
             AND CONVERT(varchar(50), a.U_datetime, 103) = @As_ONDATE) AS x

    DECLARE Cur_1 CURSOR FOR 
        SELECT CUser_id, User_Id 
        FROM #temptable

    OPEN Cur_1

    DECLARE @CUser_id INT
    DECLARE @User_Id INT
                        FETCH NEXT FROM Cur_1 
                        INTO @CUser_id, @User_Id

                        WHILE (@@FETCH_STATUS = 0)
            BEGIN

                            SELECT convert(varchar(50), U_datetime, 103) FROM inward_doc_tracking_trl                       
                            where convert(varchar(50), U_datetime, 103) = @As_ONDATE                     

                            UPDATE #temptable
                            SET REPORTDATE = @REPORTDATE
                                WHERE CUser_id = @CUser_id
                                AND User_Id = @User_Id

                FETCH NEXT FROM Cur_1 INTO @CUser_id, @User_Id
                    END
            CLOSE Cur_1
            DEALLOCATE Cur_1


            SELECT * FROM #temptable
                        DROP TABLE #temptable                           
    END

Kindly help me know what is the cause of the error.

Aucun commentaire:

Enregistrer un commentaire