stored procedure returns 0 instead of displaying recordst

[diyaa_08]

sallam 2 all; im getting return value 0 instead of getting rec my stored proc is SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[procCaseCategorySearch] ( -- @CheckStatus BIT, @Case VARCHAR, @CaseNo VARCHAR, @Category INT, @CategoryID INT, @FromDate DATETIME, @ToDate DATETIME ) AS -- if @CheckStatus =1 --begin -- true logic IF(@Case! ='' ) BEGIN SELECT CCSI.CS_RECEIVED_DATETIME,CCSI.CS_CASE_NO,CCSI.CS_CASE_TITLE, CCSI.CS_BILLING_COMPLIANCE_ID, CCSI.CS_CASE_DETAIL,CCSI.CS_MAIL_ID, CCSI.CS_CASE_FROM_REP, CCSI.CS_USER_NAME, CCSI.CS_NUMBER_OF_TIMES_CALLED,CCSI.CS_CASE_TYPE_ID,CCSI.PRIORITY_LEVEL, CCC.CS_CATEGORY_NAME, PM.LAST_NAME + ',' + ISNULL(PM.FIRST_NAME,'') + ' [ '+ ISNULL(PM.EMAIL_ADDRESS,'') + ' ]' AS NAME, ISNULL(PM.EMAIL_ADDRESS,'') AS PATIENT_EMAIL, CCP.CS_PRACTICE_CODE, CCP.CS_PROVIDER_CODE,CCP.CS_PATIENT_ACCOUNT, CCP.CS_CLAIM_NO,CCP.CS_INSURANCE_CODE, CCP.CS_RESPONSE_DATE , CCP.CS_REP_PHONE,CCP.CS_REP_PHONE_TYPE , CCP.CS_REP_EMAIL,CCP.CS_SEND_MAIL_TO, CCP.CS_IS_SEND, CCP.CS_CREATED_DATE, CCP.CS_CREATED_BY, CCP.CS_MODIFIED_BY, CCP.CS_MODIFIED_DATE, CCP.SHOW_ON_WEB,CCP.CS_CASE_STATUS ,CCP.CS_AUTHORIZE,CCP.CS_BILLING_COMPLIANCE , LP.PROVIDER_CODE , ISNULL(LP.PROVID_LNAME,'') + ',' + ISNULL(LP.PROVID_FNAME,'') AS PROVIDERNAME, ISNULL(LP.EMAIL_ADDRESS,'') AS EMAIL_ADDRESS_PROVIDER, LPI.PRACTICE_CODE , ISNULL(LPI.PRAC_NAME,'') AS PRACTICENAME, ISNULL(LPI.EMAIL_CONTACT_PERSON,'') AS PRACTICE_EMAIL, LI.INSNAME_DESCRIPTION, PT.PHONE_TYPE, LCT.CS_CASE_TYPE_DESCRIPTION FROM CS_CUSTOMER_SUPPORT_INFO CCSI LEFT OUTER JOIN CS_CASE_CATEGORIES CCC ON CCSI.CS_CASE_CATEGORY = CONVERT(VARCHAR(10), CCC.CS_CATEGORY_ID) LEFT OUTER JOIN CS_CASE_PROGRESS CCP ON CCSI.CS_CASE_NO = CCP.CS_CASE_NO LEFT OUTER JOIN PATIENT PM ON CONVERT(VARCHAR,PM.PATIENT_ACCOUNT) = CCP.CS_PATIENT_ACCOUNT LEFT OUTER JOIN PROVIDERS LP ON LP.PROVIDER_CODE = CONVERT(BIGINT,CCP.CS_PROVIDER_CODE) LEFT OUTER JOIN PRACTICES LPI ON LPI.PRACTICE_CODE = CONVERT(BIGINT,CCP.CS_PRACTICE_CODE) LEFT OUTER JOIN INSURANCE_NAMES LI ON LI.INSNAME_ID = CONVERT(BIGINT,CCP.CS_INSURANCE_CODE) LEFT OUTER JOIN PHONE_TYPES PT ON PT.PHONE_CODE = CCP.CS_REP_PHONE_TYPE LEFT OUTER JOIN CS_CASE_TYPES LCT ON CCSI.CS_CASE_TYPE_ID = LCT.CS_CASE_TYPE_ID WHERE CCSI.CS_CASE_NO LIKE +@CaseNo+ '%' AND isnull(ccsi.cs_deleted,0)=0 AND ISNULL(CCP.CS_DELETED,0)=0 AND ISNULL(CCC.CS_DELETED,0)=0 AND ISNULL(LCT.CS_DELETED,0)=0 END --end --else --begin -- false logic ELSE IF(@Category!='')

[Blue_Boy]

First of all install SQL Assistant programm and format this query by pressing CTRL+F11 then query will looks properly rendered. Better check joins on query and data on tables if they match with each other.

---

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com

[Mycroft Holmes]

To give you some idea this is what a formatted query looks like in code blocks

SELECT
CCSI.CS_RECEIVED_DATETIME,
CCSI.CS_CASE_NO,
CCSI.CS_CASE_TITLE,
CCSI.CS_BILLING_COMPLIANCE_ID,
CCSI.CS_CASE_DETAIL,
CCSI.CS_MAIL_ID,
CCSI.CS_CASE_FROM_REP,
CCSI.CS_USER_NAME,
CCSI.CS_NUMBER_OF_TIMES_CALLED,
CCSI.CS_CASE_TYPE_ID,
CCSI.PRIORITY_LEVEL,
CCC.CS_CATEGORY_NAME,
PM.LAST_NAME + ',' + ISNULL(PM.FIRST_NAME, '') + ' [ ' + ISNULL(PM.EMAIL_ADDRESS,
'') + ' ]' AS NAME,
ISNULL(PM.EMAIL_ADDRESS, '') AS PATIENT_EMAIL,
CCP.CS_PRACTICE_CODE,
CCP.CS_PROVIDER_CODE,
CCP.CS_PATIENT_ACCOUNT,
CCP.CS_CLAIM_NO,
CCP.CS_INSURANCE_CODE,
CCP.CS_RESPONSE_DATE,
CCP.CS_REP_PHONE,
CCP.CS_REP_PHONE_TYPE,
CCP.CS_REP_EMAIL,
CCP.CS_SEND_MAIL_TO,
CCP.CS_IS_SEND,
CCP.CS_CREATED_DATE,
CCP.CS_CREATED_BY,
CCP.CS_MODIFIED_BY,
CCP.CS_MODIFIED_DATE,
CCP.SHOW_ON_WEB,
CCP.CS_CASE_STATUS,
CCP.CS_AUTHORIZE,
CCP.CS_BILLING_COMPLIANCE,
LP.PROVIDER_CODE,
ISNULL(LP.PROVID_LNAME, '') + ',' + ISNULL(LP.PROVID_FNAME, '') AS PROVIDERNAME,
ISNULL(LP.EMAIL_ADDRESS, '') AS EMAIL_ADDRESS_PROVIDER,
LPI.PRACTICE_CODE,
ISNULL(LPI.PRAC_NAME, '') AS PRACTICENAME,
ISNULL(LPI.EMAIL_CONTACT_PERSON, '') AS PRACTICE_EMAIL,
LI.INSNAME_DESCRIPTION,
PT.PHONE_TYPE,
LCT.CS_CASE_TYPE_DESCRIPTION
FROM
CS_CUSTOMER_SUPPORT_INFO CCSI
LEFT OUTER JOIN CS_CASE_CATEGORIES CCC ON CCSI.CS_CASE_CATEGORY = CONVERT(VARCHAR(10), CCC.CS_CATEGORY_ID)
LEFT OUTER JOIN CS_CASE_PROGRESS CCP ON CCSI.CS_CASE_NO = CCP.CS_CASE_NO
LEFT OUTER JOIN PATIENT PM ON CONVERT(VARCHAR, PM.PATIENT_ACCOUNT) = CCP.CS_PATIENT_ACCOUNT
LEFT OUTER JOIN PROVIDERS LP ON LP.PROVIDER_CODE = CONVERT(BIGINT, CCP.CS_PROVIDER_CODE)
LEFT OUTER JOIN PRACTICES LPI ON LPI.PRACTICE_CODE = CONVERT(BIGINT, CCP.CS_PRACTICE_CODE)
LEFT OUTER JOIN INSURANCE_NAMES LI ON LI.INSNAME_ID = CONVERT(BIGINT, CCP.CS_INSURANCE_CODE)
LEFT OUTER JOIN PHONE_TYPES PT ON PT.PHONE_CODE = CCP.CS_REP_PHONE_TYPE
LEFT OUTER JOIN CS_CASE_TYPES LCT ON CCSI.CS_CASE_TYPE_ID = LCT.CS_CASE_TYPE_ID
WHERE
CCSI.CS_CASE_NO LIKE +@CaseNo + '%' AND ISNULL(ccsi.cs_deleted, 0) = 0 AND ISNULL(CCP.CS_DELETED,
0) = 0 AND ISNULL(CCC.CS_DELETED,
0) = 0 AND ISNULL(LCT.CS_DELETED,

[Blue_Boy]

Very much readable :)

---

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com

[David Skelly]

diyaa_08 wrote:

IF(@Case! ='' ) BEGIN do stuff END ELSE IF(@Category!='') BEGIN do stuff END

I'm not really an expert on stored procedures, but what will this do if @Case is '' and @Category is ''? In that case, neither IF block will get executed. What would the stored proc return in that scenario?

[David Skelly]

Oh yeah, and this:

AND isnull(ccsi.cs_deleted,0)=0

Is there an advantage to doing that rather than a simple IS NULL test? I'm not saying it's wrong, I'm saying I don't understand whether one is better than another.