How to count record sets?

Hi,
I have the following stored proc. I was hoping it would would a single record set with my data in but instead it may bring back more than one record set where each record set may have more than one record.

Anyone know how to either:
a) rewrite the sp so the results are amalgamated into 1 rs at the end of the sp
b) rewrite the sp so only one rs is created in the first place
c) rewrite the sp in a different way altogether!

CREATE PROC dbo.proc_ShortCodes_ATK_Get
AS

DECLARE @Account AS VARCHAR(5)

DECLARE Account CURSOR
LOCAL FORWARD_ONLY FOR

SELECT
vchrATKUserId1
FROM
tblShortCode JOIN tblSCAccount ON
intAccount = idAccount JOIN tblSCStatus ON
tintStatus = idStatus
WHERE
bitATKRequest = 1

OPEN Account

FETCH NEXT FROM Account
INTO @Account

WHILE @@FETCH_STATUS = 0
BEGIN
IF @Account = '0'
BEGIN
SELECT DISTINCT vchrATKUserId1,vchrThirdParty, vchrShortCode, vchrDescription,dtmDate, vchrUser
FROM tblshortcode JOIN
(
SELECT vchrATKUserId1,vchrThirdParty, vchrShortcode AS historysc
FROM tblschistory
JOIN tblSCAccount ON intAccount = idAccount
WHERE Convert(char(19),dtmDate,120)+vchrShortCode IN
(
SELECT Convert(char(19),MAX(dtmDate),120) + vchrShortCode
FROM tblschistory
WHERE intAccount <> 0
AND tintType = 2
GROUP BY vchrShortCode
)
AND intAccount <> 0
)
AS history ON tblshortcode.vchrShortcode = history.historysc
JOIN tblSCStatus ON idStatus = tintStatus
WHERE bitATKRequest = 1
END
ELSE
BEGIN
SELECT
vchrATKUserId1, vchrThirdParty, vchrShortCode, vchrDescription, dtmDate, vchrUser
FROM
tblShortCode
JOIN tblSCAccount ON intAccount = idAccount
JOIN tblSCStatus ON tintStatus = idStatus
WHERE
bitATKRequest = 1
AND vchrATKUserId1 = @Account
AND tintType = 2

END

FETCH NEXT FROM Account
INTO @Account

END

CLOSE Account
DEALLOCATE Account


RETURN @@ERROR
GO

Try inserting your resluts into a temp table:

Code:

---

CREATE PROC dbo.proc_ShortCodes_ATK_Get
AS
Set NoCount On
DECLARE @Account AS VARCHAR(5)

Create #ShortCodes(vchrATKUserId1 varchar(50),
                                vchrThirdParty varchar(50),
                                vchrShortCode varchar(50),
                                vchrDescription varchar(50),
                                dtmDate datetime,
                                vchrUser varchar(50))

DECLARE Account CURSOR
LOCAL FORWARD_ONLY FOR

SELECT
vchrATKUserId1
FROM
tblShortCode JOIN tblSCAccount ON
intAccount = idAccount JOIN tblSCStatus ON
tintStatus = idStatus
WHERE
bitATKRequest = 1

OPEN Account

FETCH NEXT FROM Account
INTO @Account

WHILE @@FETCH_STATUS = 0
BEGIN
IF @Account = '0'
BEGIN
    Insert Into #ShortCodes (vchrATKUSerId1
                                            , vchrThirdParty
                                            , vchrShortCode
                                            , vchrDescription
                                            , dtmDate
                                            , vchrUser)
    SELECT DISTINCT vchrATKUserId1,vchrThirdParty, vchrShortCode, vchrDescription,dtmDate, vchrUser
    FROM tblshortcode JOIN
    (
    SELECT vchrATKUserId1,vchrThirdParty, vchrShortcode AS historysc
    FROM tblschistory
    JOIN tblSCAccount ON intAccount = idAccount
    WHERE Convert(char(19),dtmDate,120)+vchrShortCode IN
    (
    SELECT Convert(char(19),MAX(dtmDate),120) + vchrShortCode
    FROM tblschistory
    WHERE intAccount <> 0
    AND tintType = 2
    GROUP BY vchrShortCode
    )
    AND intAccount <> 0
    )
    AS history ON tblshortcode.vchrShortcode = history.historysc
    JOIN tblSCStatus ON idStatus = tintStatus
    WHERE bitATKRequest = 1
END
ELSE
BEGIN
    Insert Into #ShortCodes (vchrATKUSerId1
                                            , vchrThirdParty
                                            , vchrShortCode
                                            , vchrDescription
                                            , dtmDate
                                            , vchrUser)
    SELECT vchrATKUserId1, vchrThirdParty, vchrShortCode, vchrDescription, dtmDate, vchrUser
    FROM tblShortCode
    JOIN tblSCAccount ON intAccount = idAccount
    JOIN tblSCStatus ON tintStatus = idStatus
    WHERE bitATKRequest = 1
    AND vchrATKUserId1 = @Account
    AND tintType = 2
END

FETCH NEXT FROM Account
INTO @Account

END

CLOSE Account
DEALLOCATE Account

Select *
From #ShortCodes

RETURN @@ERROR
GO

---

You will need to adjust the varchar values in the table definition to the size of the fields in your tables.

It looks like a simple Union of the two sql statements is all you really need.

Code:

---

CREATE PROC dbo.proc_ShortCodes_ATK_Get
AS

SELECT vchrATKUserId1, vchrThirdParty, vchrShortCode, vchrDescription, dtmDate, vchrUser
FROM tblShortCode
JOIN tblSCAccount ON intAccount = idAccount
JOIN tblSCStatus ON tintStatus = idStatus
WHERE bitATKRequest = 1 AND vchrATKUserId1 <> '0' AND tintType = 2

Union All

SELECT DISTINCT vchrATKUserId1,vchrThirdParty, vchrShortCode, vchrDescription,dtmDate, vchrUser
FROM tblshortcode JOIN
(
SELECT vchrATKUserId1,vchrThirdParty, vchrShortcode AS historysc
FROM tblschistory
JOIN tblSCAccount ON intAccount = idAccount
WHERE Convert(char(19),dtmDate,120)+vchrShortCode IN
(
SELECT Convert(char(19),MAX(dtmDate),120) + vchrShortCode
FROM tblschistory
WHERE intAccount <> 0
AND tintType = 2
GROUP BY vchrShortCode
)
AND intAccount <> 0
)
AS history ON tblshortcode.vchrShortcode = history.historysc
JOIN tblSCStatus ON idStatus = tintStatus
WHERE bitATKRequest = 1 And vchrATKUserId1 = '0'

---