Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Server Stored Procedure with cursor Help

Author  Topic 

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 OUTPUT
BEGIN
IF
brn_cd ='ALL'
THEN
SET @rs = CURSOR FOR
SELECT
branchs.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
Factsummary
INNER JOIN branchs ON Fact_MzP_branch_daily_counts_summary.BRANCH_key = branchs.branch_key
INNER JOIN commission_code ON Fact_MzP_branch_daily_counts_summary.commission_key = commission_code.commission_key
INNER JOIN coverage_type ON Factsummary.membership_coverage_type_key = coverage_type.membership_coverage_type_key
WHERE
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_code
ORDER BY
branchs.branch_code,
branchs.branch_name,
commission_code.COMMISSION_Code,
membership_coverage_type.membership_coverage_type,
Fact_summary.billing_sub_code

OPEN @rs
FETCH NEXT
FROM @rs
WHILE @@FETCH_STATUS = 0

ELSE
SET @rs = CURSOR FOR
SELECT
branchs.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
Factsummary
INNER JOIN branchs ON Fact_MzP_branch_daily_counts_summary.BRANCH_key = branchs.branch_key
INNER JOIN commission_code ON Fact_MzP_branch_daily_counts_summary.commission_key = commission_code.commission_key
INNER JOIN coverage_type ON Factsummary.membership_coverage_type_key = coverage_type.membership_coverage_type_key
WHERE
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_code
ORDER BY
branchs.branch_code,
branchs.branch_name,
commission_code.COMMISSION_Code,
membership_coverage_type.membership_coverage_type,
Fact_summary.billing_sub_code

OPEN @rs
FETCH NEXT
FROM @rs
WHILE @@FETCH_STATUS = 0
END
CLOSE @rs
DEALLOCATE @rs

Thank You,
dm



Dp

chadmat
The Chadinator

1974 Posts

Posted - 2010-11-23 : 17:10:44
Why are you using a cursor? (Or attempting to use a cursor)

-Chad
Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2010-11-23 : 17:12:35
Just execute the select in each section of the IF statement, there is no need for a cursor.
Go to Top of Page
   

- Advertisement -