CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Clanger67
Clanger67

Reputation: 37

Return values from stored procedure that contains a cursor

I have the following stored procedure and I can get it to print the values/information that I require, but I would like to be able to return the values and use them to populate a dropdownlist in a C# application.

This is the stored procedure:

ALTER PROCEDURE sp_IHubTypes
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
DECLARE @NewsTypeID AS NVARCHAR(100)
DECLARE @Description AS NVARCHAR(100)
DECLARE @NTCount AS NVARCHAR(100)
DECLARE NewsCURSOR CURSOR FOR

SELECT NewsTypeID, Description
FROM tblNewsType
WHERE PortalID = 3
AND Sector = 'GENERIC'
AND Enabled = 1
ORDER BY RowOrder ASC, Description ASC

OPEN NewsCURSOR
FETCH NEXT FROM NewsCURSOR
INTO @NewsTypeID,@Description
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @NTCount = Count(0)
    FROM    tblAsset
    WHERE NewsTypeS LIKE '%,' + CAST(@NewsTypeID AS nvarchar(100)) + ',%'

IF @NTCount > 0
    BEGIN
        PRINT  '(' + @NTCount + ')' + @Description
    END

    FETCH NEXT FROM NewsCURSOR INTO @NewsTypeID,@Description
END

CLOSE NewsCURSOR
DEALLOCATE NewsCURSOR

END

When I execute the stored procedure in SQL Server Management Studio the print statement returns the following:

 5 - Canada
 3 - China
 2 - Germany
 3 - India
 2 - Netherlands
 1 - Russia
 2 - UK

Any help would be much appreciated.

Upvotes: 0

Views: 1979

Answers (2)

db_brad
db_brad

Reputation: 923

I would avoid using a CURSOR and do something SET based like this:

SELECT  COUNT(*) AS ItemCount, 
        nt.[Description] AS Country
FROM    tblNewsType AS nt, tblAsset AS a
WHERE   a.NewsTypes LIKE '%,' + CAST(nt.NewsTypeID AS nvarchar(100)) + ',%'
GROUP BY nt.[Description]

Upvotes: 2

huMpty duMpty
huMpty duMpty

Reputation: 14460

You can add data to temporary table and select from there

CREATE TABLE #TEMPDATA(ID int,COUNTRY varchar(100))
BEGIN

    IF @NTCount > 0
    BEGIN
       INSERT INTO #TEMPDATA(ID,OUNTRY)
       VALUES(@NTCount ,@Description)
    END
    FETCH NEXT FROM NewsCURSOR INTO @NewsTypeID,@Description

END

Then after the cursor

SELECT ID,COUNTRY FROM #TEMPDATA

Upvotes: 0

Related Questions