SQL Server Performance Forum – Threads Archive

Great Problem in Stored procedure

Hi , I have a problem in all my stored procedures. I want to compare local variable with input parameter in sp. but it is not comparing properly..i mentioned it in BOLD letter. Kindly clear my doubt immediatly Thanks and Rgds., Bala SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ALTER PROC RM1111 ( @TODATE VARCHAR(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, @SCHEME VARCHAR(5), @ZO VARCHAR(20), @RO VARCHAR(20), @BO VARCHAR(20) ) AS DECLARE @PREV_MONTH_LAST_DAY DATETIME DECLARE @FILTER VARCHAR(3000) DECLARE @PMTR CHAR(20) DECLARE @COLUMN CHAR(20) SET NOCOUNT ON SET @PREV_MONTH_LAST_DAY=dbo.GetFirstDayOfMonth(CAST(@TODATE AS DATETIME))-1 –SET @FILTER = DBO.INPUTPARAM(@ZO,@RO,@BO) IF @BO <> ‘All’ AND @ZO<>’All’ AND @RO <> ‘All' BEGIN SET @PMTR = @BO SET @COLUMN = ‘A.BRANCH_ID' END IF @ZO <> ‘All’ AND @BO = ‘All’ AND @RO<>’All’ BEGIN SET @PMTR = @RO SET @COLUMN = ‘D.REGION_ID' END IF @ZO <> ‘All’ AND @BO = ‘All’ AND @RO=’All’ BEGIN SET @PMTR = @ZO SET @COLUMN = ‘E.ZONE_ID' END BEGIN SELECT CAST(@TODATE as DATETIME) ASONDATE, BB.SCHEME_NAME, PREV_ACCS=SUM(CASE WHEN A.LAST_MOD_DATE<[email protected]_MONTH_LAST_DAY AND A.CLOSE_DATE IS NULL THEN 1 ELSE 0 END), PREV_OS=SUM(CASE WHEN A.LAST_MOD_DATE<[email protected]_MONTH_LAST_DAY AND A.CLOSE_DATE IS NULL THEN A.BAL_OS ELSE 0 END), CURR_DIS_ACCS=SUM(CASE WHEN A.DISBURSE_DT>@PREV_MONTH_LAST_DAY AND A.DISBURSE_DT<[email protected] AND A.CLOSE_DATE IS NULL AND A.DISBURSE_DT IS NOT NULL THEN 1 ELSE 0 END), CURR_DIS_OS=SUM(CASE WHEN A.DISBURSE_DT>@PREV_MONTH_LAST_DAY AND A.DISBURSE_DT<[email protected] AND A.CLOSE_DATE IS NULL AND A.DISBURSE_DT IS NOT NULL THEN A.BAL_OS ELSE 0 END), CURR_RECOV_ACCS=SUM(CASE WHEN A.SANCT_DT<[email protected] AND A.CLOSE_DATE>[email protected]_MONTH_LAST_DAY THEN 1 ELSE 0 END), CURR_RECOV_OS=SUM(CASE WHEN A.SANCT_DT<[email protected] AND A.CLOSE_DATE>[email protected]_MONTH_LAST_DAY THEN A.BAL_OS ELSE 0 END), CURR_ACCS=SUM(CASE WHEN A.CLOSE_DATE IS NULL THEN 1 ELSE 0 END), CURR_OS=SUM(CASE WHEN A.CLOSE_DATE IS NULL THEN A.BAL_OS ELSE 0 END), STA_LOSS_ACCS=SUM(CASE WHEN A.CLOSE_DATE IS NULL AND B.ASSETCLASSI_CD=’1′ AND A.LAST_MOD_DATE<[email protected] THEN 1 ELSE 0 END), STA_OS=SUM(CASE WHEN A.CLOSE_DATE IS NULL AND B.ASSETCLASSI_CD=’1′ AND A.LAST_MOD_DATE<[email protected] THEN A.BAL_OS ELSE 0 END), OV_UPTO30=SUM(CASE WHEN A.CLOSE_DATE IS NULL AND B.ASSETCLASSI_CD=’1′ AND A.BAL_OS>A.DRAWING_POWER AND A.LAST_MOD_DATE<[email protected] THEN 1 ELSE 0 END), OV_UPTO30_OS=SUM(CASE WHEN A.CLOSE_DATE IS NULL AND B.ASSETCLASSI_CD=’1′ AND A.BAL_OS>A.DRAWING_POWER AND A.LAST_MOD_DATE<[email protected] THEN A.BAL_OS ELSE 0 END), /* OV_UPTO30=SUM(CASE WHEN A.CLOSE_DATE IS NULL AND B.ASSETCLASSI_CD=’1′ AND A.BAL_OS>A.DRAWING_POWER AND A.LAST_MOD_DATE>[email protected]_MONTH_LAST_DAY THEN 1 ELSE 0 END), OV_UPTO30_OS=SUM(CASE WHEN A.CLOSE_DATE IS NULL AND B.ASSETCLASSI_CD=’1′ AND A.BAL_OS>A.DRAWING_POWER AND A.LAST_MOD_DATE>[email protected]_MONTH_LAST_DAY THEN A.BAL_OS ELSE 0 END), OV_UPTO60=SUM(CASE WHEN A.CLOSE_DATE IS NULL AND B.ASSETCLASSI_CD=’1′ AND A.BAL_OS>A.DRAWING_POWER AND A.LAST_MOD_DATE>=(@TODATE-61) THEN 1 ELSE 0 END), OV_UPTO60_OS=SUM(CASE WHEN A.CLOSE_DATE IS NULL AND B.ASSETCLASSI_CD=’1′ AND A.BAL_OS>A.DRAWING_POWER AND A.LAST_MOD_DATE>=(@TODATE-61) THEN A.BAL_OS ELSE 0 END), OV_UPTO90=SUM(CASE WHEN A.CLOSE_DATE IS NULL AND B.ASSETCLASSI_CD=’1′ AND A.BAL_OS>A.DRAWING_POWER AND A.LAST_MOD_DATE>=(@TODATE-91) THEN 1 ELSE 0 END), OV_UPTO90_OS=SUM(CASE WHEN A.CLOSE_DATE IS NULL AND B.ASSETCLASSI_CD=’1′ AND A.BAL_OS>A.DRAWING_POWER AND A.LAST_MOD_DATE>=(@TODATE-91) THEN A.BAL_OS ELSE 0 END), */ SUB_ACCS=SUM(CASE WHEN A.CLOSE_DATE IS NULL AND B.ASSETCLASSI_CD=’2′ AND A.LAST_MOD_DATE<[email protected] THEN 1 ELSE 0 END), SUB_OS=SUM(CASE WHEN A.CLOSE_DATE IS NULL AND B.ASSETCLASSI_CD=’2′ AND A.LAST_MOD_DATE<[email protected] THEN A.BAL_OS ELSE 0 END), DOUB_ACCS=SUM(CASE WHEN A.CLOSE_DATE IS NULL AND B.ASSETCLASSI_CD=’3′ AND A.LAST_MOD_DATE<[email protected] THEN 1 ELSE 0 END), DOUB_OS=SUM(CASE WHEN A.CLOSE_DATE IS NULL AND B.ASSETCLASSI_CD=’3′ AND A.LAST_MOD_DATE<[email protected] THEN A.BAL_OS ELSE 0 END), LOSS_ACCS=SUM(CASE WHEN A.CLOSE_DATE IS NULL AND B.ASSETCLASSI_CD=’4′ AND A.LAST_MOD_DATE<[email protected] THEN 1 ELSE 0 END), LOSS_OS=SUM(CASE WHEN A.CLOSE_DATE IS NULL AND B.ASSETCLASSI_CD=’4′ AND A.LAST_MOD_DATE<[email protected] THEN A.BAL_OS ELSE 0 END) FROM dbo.BR_ADVANCES A LEFT OUTER J OIN dbo.BR_ADVANCE_CODES B ON A.ADVANCE_ID=B.ADVANCE_ID AND A.BRANCH_ID=B.BRANCH_ID AND A.GL_HEAD=B.GL_HEAD LEFT OUTER JOIN dbo.M_SCHEME BB ON A.SCHEME_CD=BB.SCHEME_CD LEFT OUTER JOIN dbo.BK_BRANCHES C ON A.BRANCH_ID=C.BRANCH_ID LEFT OUTER JOIN dbo.BK_REG_OFFICES D ON C.REGION_ID=D.REGION_ID LEFT OUTER JOIN dbo.BK_FGMO_ZONES E ON D.ZONE_ID=E.ZONE_ID WHERE @[email protected] AND A.ACTIVE=’1′ AND [email protected] GROUP BY BB.SCHEME_NAME SET NOCOUNT OFF END –EXEC RM1111 ‘20061130’,’399′,’552178′,’531791′,’531791′ –EXEC RM1111 ‘20061130’,’399′,’552178′,’531791′,’All' –EXEC RM1111 ‘20061130’,’399′,’552178′,’ALL’,’ALL' –EXEC RM1111 ‘20061130’,’399′,’All’,’All’,’All' /* SELECT TITLE=CASE WHEN @ZO = ‘All’ AND @BO = ‘All’ AND @RO=’All’ THEN ‘CENTRAL OFFICE’ WHEN @ZO <> ‘All’ AND @RO = ‘All’ AND @BO = ‘All’ THEN E.ZONE_NAME WHEN @ZO <> ‘All’ AND @RO <> ‘All’ AND @BO = ‘All’ THEN D.REGION_NAME WHEN @ZO <> ‘All’ AND @RO <> ‘All’ AND @BO <> ‘All’ THEN C.BRANCH_NAME END ,*/ GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO

Are you sure you are getting correct values for @BO @ZO and @RO. I can’t see any issues there. Also for @[email protected] you have to use execute command as you are creating dynamic sql —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

Dineshasanka, both @COLUMN,@PMTR are varchar . even i didn’t use any subquery in it. so i don’t think of using execute command. Should i need to use Execute command for variable execution.
if so , should i need to use two execute statements for both @column and @pmtr. I have one more doubt? I want to append string in a select query ,like after where clause i want to append dynamic condition . but when i used making the query as a string by appending into a variable and executing the string in the stored procedure . then it is working fine.
but i want to attend straight away with the query with out using string concatenation. If it works fine, First problem obviously gets solved. Hope u understand my problem. Bala

Yes, you have to use execute command. Read about dynamic sql in Books-on-Line.

Hi , where/how can i use execute command in the where clause ? will "where" clause supports execute command ? For ex:
shall i use the below syntax
"WHERE @[email protected] AND A.ACTIVE=’1′ AND [email protected]
GROUP BY BB.SCHEME_NAME" AS
"WHERE exec(@COLUMN)=exec(@PMTR) AND A.ACTIVE=’1′ AND [email protected]
GROUP BY BB.SCHEME_NAME" It’s not working . I am not sure of using execute command at where clause in SP. is it possible?
The problem is , i want to compare to variables dynamically which i mentioned in the forum. Thx in advance,
bala.

FYI
http://www.sommarskog.se/dynamic_sql.html

I have seen the given website . "http://www.sommarskog.se/dynamic_sql.html".Thanks buddy.
by the by I was already using the same logic by appending into a string and executing it in sp.
But appending just 2 or 3 dynamic words (eg: @[email protected] ) should i need to append entire query into string variable and execute it. will it affect the performance of the sp? that is why i want to find it in some other way. b’z i am going to handle bank data. it will be very huge data. so , executing string in a sp will be time taking process as i think. so , which one is better solution for my problem.. Thx in advance.

Without Dynamic SQL, you can try
Select columns from table
where (@TODATE is null or [email protected]) and
(@SCHEME is null or [email protected]) and
(@ZO is null or [email protected]) and
(@RO is null or [email protected]) and
(@BO is null or [email protected])
By col I meant respective Columns Madhivanan Failing to plan is Planning to fail

I think you can replace is null by =’All’ Madhivanan Failing to plan is Planning to fail

wow…thank you buddy ..now it’s working fine..i need to do some changes..anyhow , it works fine.
Thanks for your help madhivanan.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by balavijay wow…thank you buddy ..now it’s working fine..i need to do some changes..anyhow , it works fine. Thanks for your help madhivanan. <hr height="1" noshade id="quote"></blockquote id="quote">You are welcome [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

]]>