SET @strSQL = 'SELECT @mainID = E.Main_ID, @curTitle = F.Title, @violateRules = Violate_Rules, @Testcell = Testcell, @Series_Name = Series_Name From ' + @dataTable + ' A,'
SET @strSQL = @strSQL + ' ' + @headerTable + ' B,'
SET @strSQL = @strSQL + ' ' + @seriesTable + ' C,'
SET @strSQL = @strSQL + ' tbl_Setting_Series D, tbl_Setting_Main_Series_Mapping E, tbl_Setting_Main F'
SET @strSQL = @strSQL + ' Where A.Line_ID = C.Line_ID And B.Header_ID = C.Header_ID'
SET @strSQL = @strSQL + ' And C.Series_ID = D.Series_ID And D.Series_ID = E.Series_ID And C.Line_ID IN (' + Cast(@allLineID as Varchar(5000)) + ')'
SET @strSQL = @strSQL + ' And E.Main_ID = F.Main_ID' 
SET @strSQL = @strSQL + ' And A.Date = ''' + @Date + ''' And A.Interval = ''' + @Interval + ''''

			
Exec sp_executesql @strSQL, N'@violateRules nvarchar(100) output, @Testcell nvarchar(100) output, @Series_Name nvarchar(100) output, @mainID int output, @curTitle nvarchar(100) output', @ViolateRules output, @Testcell output,@Series_Name output, @mainID output, @curTitle output

quote:

---

Originally posted by ryanlcs

Hi

I got the following statement:

SET @strSQL = 'SELECT @mainID = E.Main_ID, @curTitle = F.Title, @violateRules = Violate_Rules, @Testcell = Testcell, @Series_Name = Series_Name From ' + @dataTable + ' A,'
SET @strSQL = @strSQL + ' ' + @headerTable + ' B,'
SET @strSQL = @strSQL + ' ' + @seriesTable + ' C,'
SET @strSQL = @strSQL + ' tbl_Setting_Series D, tbl_Setting_Main_Series_Mapping E, tbl_Setting_Main F'
SET @strSQL = @strSQL + ' Where A.Line_ID = C.Line_ID And B.Header_ID = C.Header_ID'
SET @strSQL = @strSQL + ' And C.Series_ID = D.Series_ID And D.Series_ID = E.Series_ID And C.Line_ID IN (' + Cast(@allLineID as Varchar(5000)) + ')'
SET @strSQL = @strSQL + ' And E.Main_ID = F.Main_ID' 
SET @strSQL = @strSQL + ' And A.Date = ''' + @Date + ''' And A.Interval = ''' + @Interval + ''''

			
Exec sp_executesql @strSQL, N'@violateRules nvarchar(100) output, @Testcell nvarchar(100) output, @Series_Name nvarchar(100) output, @mainID int output, @curTitle nvarchar(100) output', @ViolateRules output, @Testcell output,@Series_Name output, @mainID output, @curTitle output

The query will return more than 1 record, but from the output command in the Exec sp_executesql, I can only get 1 return value.

Is there a way to retrieve more than 1 value from the query or is the a way I can loop the query ?

Thanks.

---

quote:

---

Originally posted by Kristen

Is it possible to return a Cursor as an Output variable from an Sproc call? (Don't like cursors, try never to use them, so I'm not sure ... )

---

quote:

---

Originally posted by Kristen

Is it possible to return a Cursor as an Output variable from an Sproc call? (Don't like cursors, try never to use them, so I'm not sure ... )

---

-- Define Variables to store the result in
DECLARE @ProductID INT
DECLARE @ProductNumber NVARCHAR(25)
DECLARE @Name NVARCHAR(50)

-- Define the select statement used to populate the cursor
DECLARE cr CURSOR FOR
SELECT ProductID, ProductNumber, [Name]
FROM Production.Product
WHERE [Name] LIKE 'b%'

-- Open the Cursor
OPEN cr

-- Loop through each row in the cursor
-- Fetch the first row from the cursor
FETCH NEXT FROM cr INTO @ProductID, @ProductNumber, @Name
-- Check the fetch status. If a row was fetched, enter the loop
WHILE @@FETCH_STATUS = 0
BEGIN
	-- Do something with the results. 
	PRINT CAST(@ProductID AS VARCHAR) + ' - ' + @ProductNumber + ' - ' + @Name
	
	-- Fetch the nest row from the cursor
	FETCH NEXT FROM cr INTO @ProductID, @ProductNumber, @Name
END

-- VERY IMPORTANT!!! Close and deallocate the cursor
CLOSE cr
DEALLOCATE cr

quote:

---

Originally posted by Kristen
I wasn't implying that you did suggest that, only that an Sproc (i.e. sp_ExecuteSQL in this case) could return a Cursor as an OUTPUT parameter and that could be used to loop through the records

---

USE AdventureWorks
GO 

CREATE PROCEDURE dbo.currency_cursor 
    @currency_cursor CURSOR VARYING OUTPUT
AS
    SET @currency_cursor = CURSOR
    FORWARD_ONLY STATIC FOR
      SELECT CurrencyCode, Name
      FROM Sales.Currency;
    OPEN @currency_cursor;
GO

DECLARE @MyCursor CURSOR;
DECLARE @CurrencyCode NCHAR(3)
DECLARE @Name NVARCHAR(50)

EXEC dbo.currency_cursor @currency_cursor = @MyCursor OUTPUT;
FETCH NEXT FROM @MyCursor INTO @CurrencyCode, @Name;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
	PRINT @CurrencyCode + ' - ' + @Name
	FETCH NEXT FROM @MyCursor INTO @CurrencyCode, @Name;
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
GO

DROP PROCEDURE dbo.currency_cursor
GO

quote:

---

Originally posted by Kristen

That's a good link/example. But I hope I never need it!

---