While Loop replacing cursor - 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)

While Loop replacing cursor

Author

Topic

kellog1
Starting Member

35 Posts

Posted - 2010-06-04 : 15:06:57

Gurus,
I am trynig replace an cursor with temporay table but I am not getting any data.

Here is code for Cursor that I am using..

declare @pubId int
declare @sequence binary(8)

DECLARE pubCur CURSOR FOR
SELECT PublicationId, Sequence
FROM dbo.DimPublication

OPEN pubCur;

FETCH NEXT FROM pubCur INTO @pubId, @sequence ;

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @pubId, Id ActionTypeAppId, IsActive, Code, Name, Sort, Description
FROM staging.PIT_ActionType(@sequence)

FETCH NEXT FROM pubCur INTO @pubId, @sequence;
END

CLOSE pubCur;
DEALLOCATE pubCur;

Here is code to replace Cursor with Temp table...

-- Create a temporary table, note the IDENTITY
-- column that will be used to loop through
-- the rows of this table
CREATE TABLE #Pub (
RowID int IDENTITY(1, 1),
PublicationId int,
Sequence binary(8)
)

-- Insert the resultset we want to loop through
-- into the temporary table
INSERT INTO #Pub(PublicationId, Sequence)
SELECT PublicationId,Sequence
FROM DimPublication

DECLARE @NumberRecords int, @RowCount int
DECLARE @pubId int, @seq binary(8)
-- Get the number of records in the temporary table
SET @NumberRecords = @@ROWCOUNT
SET @RowCount = 1

-- loop through all records in the temporary table
-- using the WHILE loop construct
WHILE @RowCount <> @NumberRecords
BEGIN
SELECT @pubId PublicationId, Id ActionTypeAppId, IsActive, Code, Name, Sort, Description
FROM staging.PIT_ActionType(@seq)

SET @RowCount = @RowCount + 1
END

-- drop the temporary table
DROP TABLE #pub

What am I doing wrong...I am not getting any data back!!!

Sachin.Nand

2937 Posts

Posted - 2010-06-04 : 15:28:46

What is this in your loop?Is it some kind of a table valued function?
Also I cannot see it anywhere value of @seq been set

FROM staging.PIT_ActionType(@seq)

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

kellog1
Starting Member

35 Posts

Posted - 2010-06-04 : 15:48:58

Yes I am using table value function and I am trynig to pass values stored in #Pub temp table as parameter inside while loop.

quote:
Originally posted by Idera

What is this in your loop?Is it some kind of a table valued function?
Also I cannot see it anywhere value of @seq been set

FROM staging.PIT_ActionType(@seq)

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-06-04 : 15:52:30

But where are you setting the value of @seq in the loop?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

kellog1
Starting Member

35 Posts

Posted - 2010-06-04 : 15:59:20

@Seq is stored in #pub table in Column Sequence...but how do read the #pub table inside while loop to get the values for @seq = Sequence?

quote:
Originally posted by Idera

But where are you setting the value of @seq in the loop?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-06-04 : 16:09:21

Just put an identity column in the table #pub like this


INSERT INTO #Pub(id int identity(1,1),PublicationId, Sequence)
SELECT PublicationId,Sequence
FROM DimPublication

and then in this part

WHILE @RowCount <> @NumberRecords
BEGIN
SELECT @seq=Sequence from #pub where id=@RowCount
SELECT @pubId PublicationId, Id ActionTypeAppId, IsActive, Code, Name, Sort, Description
FROM staging.PIT_ActionType(@seq)

SET @RowCount = @RowCount + 1
END

Please see the changes in green.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

kellog1
Starting Member

35 Posts

Posted - 2010-06-04 : 16:19:45

Here is error I am getting when I make changes. And please note that in #pub table there are 43 rows in sequence column.

Msg 512, Level 16, State 1, Line 23
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Msg 512, Level 16, State 1, Line 24
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

quote:
Originally posted by Idera

Just put an identity column in the table #pub like this

INSERT INTO #Pub(id int identity(1,1),PublicationId, Sequence)
SELECT PublicationId,Sequence
FROM DimPublication

and then in this part

WHILE @RowCount <> @NumberRecords
BEGIN
SELECT @seq=Sequence from #pub where id=@RowCount
SELECT @pubId PublicationId, Id ActionTypeAppId, IsActive, Code, Name, Sort, Description
FROM staging.PIT_ActionType(@seq)

SET @RowCount = @RowCount + 1
END

Please see the changes in green.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-06-05 : 01:51:00

This is a much simpler way to do what you need:


SELECT
	a.PublicationId,
	b.Id ActionTypeAppId,
	b.IsActive,
	b.Code,
	b.Name,
	b.Sort,
	b.Description
FROM
	dbo.DimPublication a
	cross apply
	staging.PIT_ActionType(a.sequence) b

CODO ERGO SUM

kellog1
Starting Member

35 Posts

Posted - 2010-06-05 : 15:57:59

Thanks a lot. This will work perfectly for me.

quote:
Originally posted by Michael Valentine Jones

This is a much simpler way to do what you need:

SELECT
a.PublicationId,
b.Id ActionTypeAppId,
b.IsActive,
b.Code,
b.Name,
b.Sort,
b.Description
FROM
dbo.DimPublication a
cross apply
staging.PIT_ActionType(a.sequence) b

CODO ERGO SUM

Panafro
Starting Member

2 Posts

Posted - 2011-12-23 : 21:57:52

I have a slightly different situation in which i want to pass a list on non-contiguous integers as one of the parameters in a stored procedure. It is an insert SP and i will like to return as many rows for the given EmployeeID as there are items in the list (@addIDList).
As the list is not contiguous, i cannot increment a WHILE loop by 1. Assuming @addIDList = '2,6, 9' , how can I (without using cursors) create one row for this employee for each of the 3 items in my list? Part of my SP is shown below. Cheers!

declare @empID int
Declare @addIDList [nvarchar](MAX) = null
Declare @DoneBy [nvarchar](50)

set @empID=1
set @addIDList = '1,2,5'
set @DoneBy = 'me'

--INSERT INTO
-- ......................................................................

SELECT
x.EmployeeID
,@addIDList
,@DoneBy

FROM

(
SELECT e.EmployeeID
FROM [Employee] AS e
WHERE e.EmployeeID =@empID
)
AS x

Panafro
Starting Member

2 Posts

Posted - 2011-12-24 : 09:45:01

Ok. Well, I finally figured it using the following:

declare @empID int
Declare @addIDList [nvarchar](MAX) = null
Declare @DoneBy [nvarchar](50)

set @empID=1
set @addIDList = '1,2,5'
set @DoneBy = 'me'

SELECT
b.EmployeeID,
a.item
,@DoneBy

FROM

[dbo].[ufn_fnSplit](@addIDList, ',') a
cross apply
fn_getsubtree (a.item) b

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-24 : 10:11:54

quote:
Originally posted by Panafro

Ok. Well, I finally figured it using the following:

declare @empID int
Declare @addIDList [nvarchar](MAX) = null
Declare @DoneBy [nvarchar](50)

set @empID=1
set @addIDList = '1,2,5'
set @DoneBy = 'me'

SELECT
b.EmployeeID,
a.item
,@DoneBy

FROM

[dbo].[ufn_fnSplit](@addIDList, ',') a
cross apply
fn_getsubtree (a.item) b

Glad that you sorted it out!
But in future please post your question as a new thread
this will increase the chances of people seeing your post easily and giving quick solution.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources