SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Server Stored Procedure with cursor Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dim
Yak Posting Veteran

USA
57 Posts

Posted - 11/23/2010 :  16:59:57  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 11/23/2010 :  17:10:44  Show Profile  Visit chadmat's Homepage  Reply with Quote
Why are you using a cursor? (Or attempting to use a cursor)

-Chad
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 11/23/2010 :  17:12:35  Show Profile  Visit chadmat's Homepage  Reply with Quote
Just execute the select in each section of the IF statement, there is no need for a cursor.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000