out variable in cursors - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

out variable in cursors

Author

Topic

lazydev
Starting Member

16 Posts

Posted - 2008-04-17 : 18:30:55

i want to store the output in out variable .Which gives multiple values.

create procedure usp_test (@AccountID INT)
as
begin
DECLARE @getAccountID CURSOR
SET @getAccountID = CURSOR FOR
SELECT Account_ID
FROM Accounts
OPEN @getAccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @AccountID FETCH NEXT
FROM @getAccountID INTO @AccountID
END
CLOSE @getAccountID
DEALLOCATE @getAccountID
end

i get nearly ten rows in the print statement
how can i assign the output to a out variable where i get all ten rows.

Any ideas or suggestions.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-17 : 18:43:35

not sure what you mean. You want the resultset?
Then use a temp table and don't use a cursor.

create proc usp_test
as
insert #Accounts
select Account_ID
FROM Accounts
go
create table #Accounts (Account_ID int)
exec usp_test
select * from #Accounts

or do the insert in the calling code
create proc usp_test
as
select Account_ID
FROM Accounts
go
create table #Accounts (Account_ID int)
insert #Accounts
exec usp_test
select * from #Accounts

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources