dim
Yak Posting Veteran
57 Posts |
Posted - 2010-11-23 : 16:59:57
|
Hi, I am relatively new to the SQL server prog and had a follwing query regarding the stored prodecure syntax. This stored procedure is used to populate data in the report based on the user parameter supplied from an asp page. I am not getting the acurate syntax to declare a cursor and define it based on the user supplied parameters. There are two cases when the user supplies brn_cd="ALL" then one part of the stored proc need to be excuted else the other. Please let me know the possible catch to the cursor with parameter syntax used here. CREATE PROCEDURE CAR_COUNTS @brn_cd VARCHAR(20), @trans_dt_from VARCHAR(20), @trans_dt_to VARCHAR(20), @rs CURSOR VARYING OUTPUTBEGIN IFbrn_cd ='ALL' THEN SET @rs = CURSOR FORSELECTbranchs.branch_name + ' - ' + branchs.branch_code as brn_cd,branchs.branch_name as brn_name,commission_code.COMMISSION_Code,coverage_type.membership_coverage_type,Factsummary.billing_sub_code,sum(Factsummary.MTD_CT)FROM FactsummaryINNER JOIN branchs ON Fact_MzP_branch_daily_counts_summary.BRANCH_key = branchs.branch_keyINNER JOIN commission_code ON Fact_MzP_branch_daily_counts_summary.commission_key = commission_code.commission_keyINNER JOIN coverage_type ON Factsummary.membership_coverage_type_key = coverage_type.membership_coverage_type_keyWHERE commission_code.COMMISSION_Code IN('N','R')AND Factsummary.TRANSaction_DT BETWEEN convert(varchar(20), convert(datetime,trans_dt_from ),101) and convert(varchar(20), convert(datetime,trans_dt_to),101)GROUP BY branchs.branch_name,commission_code.COMMISSION_Code,coverage_type.membership_coverage_type,Factsummary.billing_sub_codeORDER BY branchs.branch_code,branchs.branch_name,commission_code.COMMISSION_Code,membership_coverage_type.membership_coverage_type,Fact_summary.billing_sub_codeOPEN @rsFETCH NEXTFROM @rsWHILE @@FETCH_STATUS = 0ELSE SET @rs = CURSOR FORSELECTbranchs.branch_name + ' - ' + branchs.branch_code as brn_cd,branchs.branch_name as brn_name,commission_code.COMMISSION_Code,coverage_type.membership_coverage_type,Factsummary.billing_sub_code,sum(Factsummary.MTD_CT)FROM FactsummaryINNER JOIN branchs ON Fact_MzP_branch_daily_counts_summary.BRANCH_key = branchs.branch_keyINNER JOIN commission_code ON Fact_MzP_branch_daily_counts_summary.commission_key = commission_code.commission_keyINNER JOIN coverage_type ON Factsummary.membership_coverage_type_key = coverage_type.membership_coverage_type_keyWHERE commission_code.COMMISSION_Code IN('N','R')AND Factsummary.TRANSaction_DT BETWEEN convert(varchar(20), convert(datetime,trans_dt_from ),101) and convert(varchar(20), convert(datetime,trans_dt_to),101)GROUP BY branchs.branch_name,commission_code.COMMISSION_Code,coverage_type.membership_coverage_type,Factsummary.billing_sub_codeORDER BY branchs.branch_code,branchs.branch_name,commission_code.COMMISSION_Code,membership_coverage_type.membership_coverage_type,Fact_summary.billing_sub_codeOPEN @rsFETCH NEXTFROM @rsWHILE @@FETCH_STATUS = 0END CLOSE @rsDEALLOCATE @rsThank You,dmDp |
|