Error in stored procedure

  • Guys,

    I working on stored procedure which handles conditional where clause based in null or not null values

    paased. My stored procedure is based of the example - PROCEDURE [dbo].[Emp_Svc_Cost] from

    http://www.yukonxml.com/chapters/apress/reportingservices/bestpractices/

    but when I try to create the stored procedure I get an error

    "Msg 102, Level 15, State 1, Procedure SP_RPT, Line 28

    Incorrect syntax near 'END'."

    CREATE PROCEDURE SP_RPT

    @CASE_NUMBER VARCHAR(20)=NULL,

    @PARTY_ID BIGINT=NULL,

    @LAST_NAME VARCHAR(250)=NULL

    AS

    SELECT [CASE_ID],[CASE_NUMBER],[CASE_TYP_CD],[CASE_TYP_DESC],[CASE_STATUS_CD]

    ,[CASE_STS_DESC],[COURT_CD],[COURT_NAME],[PARTY_ID],[LAST_NAME],[MIDDLE_NAME]

    ,[FIRST_NAME],[DISPLAYNAME],[CI_ANOTD_OFNS_REF_NUM],[CI_ANOTD_OFNS_EFF_DT]

    ,[ANOTD_OFNS_EFF_DT],[ANOTD_OFNS_REF_NUM],[CNT_DISPOSITION_DT]

    ,[DI_ANOTD_OFNS_REF_NUM],[DI_ANOTD_OFNS_EFF_DT],[CI_OFFENSE_CLASS_CD]

    ,[CI_OFFENSE_CLASS_DESC],[CI_OFFENSE_TYP_CD],[CI_OFFENSE_TYP_DESC],[OFFENSE_CLASS_CD]

    ,[OFFENSE_CLASS_DESC],[OFFENSE_TYP_CD],[OFFENSE_TYP_DESC],[DI_OFFENSE_CLASS_CD]

    ,[DI_OFFENSE_CLASS_DESC],[DI_OFFENSE_TYP_CD],[DI_OFFENSE_TYP_DESC],[VCC_CD]

    ,[VCC_CHARGEDESCRIPTION],[DISPOSITION_DT],[DISPOSITION_CD],[DISPOSITION_DESC]

    ,[DISPOSITION_BY_CD],[DISPOSITION_BY_DESC],[ID]

    FROM [RPT_V_DISPJUDGEMENT]

    WHERE

    (ISNULL(CASE_NUMBER, 0) = ISNULL(@CASE_NUMBER, ISNULL(CASE_NUMBER, 0)))

    AND

    (ISNULL(PARTY_ID, 0) = ISNULL(@PARTY_ID, ISNULL(PARTY_ID, 0)))

    AND

    (ISNULL(LAST_NAME, 0) = ISNULL(@LAST_NAME, ISNULL(LAST_NAME, 0))

    -- CASE TO CHECK IF NON REQUIRED FIELDS WHERE PASSED

    AND

    1=CASE

    WHEN (@PARTY_ID IS NULL) THEN 1

    ELSE 0

    END

    AND

    1=CASE

    WHEN (@LAST_NAME IS NULL) THEN 1

    ELSE 0

    END

    GO

    Any suggestions and inputs would help

  • Number of opened and closed brackets in one of your lined don't match.

    _____________
    Code for TallyGenerator

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply