September 23, 2007 at 1:00 pm
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
September 23, 2007 at 2:15 pm
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