"Tav" <tavis.p***@staticsystems.co.uk> wrote in message
news:1154619308.794188.118190@i42g2000cwa.googlegroups.com...
> Erland Sommarskog wrote:
> > Robert Bravery (m*@u.com) writes:
> > > I need to loop throu a cursor and then insert rows.
> > > But I need the cursor to be populated from data retrieved via a SP
with
> > > Params
> > > how can thin be done
> >
> > The first obvious question is why it has to be a cursor? Why not insert
> > all row at once? I ask because using cursors is an exceellent recipe for
> > poor performance.
> >
> > If you insist, I have an article on my web site that covers the various
> > options. http://www.sommarskog.se/share_data.html.
> >
>
> Hi again Robert,
>
> The only way this is possible is to put the stored procedure results
> into a temporary table and then use the cursor on the temporary table.
> However, I fully agree with Erland, cursors do cause poor performance
> (so do temporary tables to some degree) and they should be used as the
> very very last resort.  You should really be looking at inserting all
> the rows at once, if there are issues in doing this tackle them (ie.
> through this Usenet group) as and when you get to them.
> In any case I have simulated your request, using Scott Adams' Dilbert
> again...
> (run this SQL on a test or development database)
> CREATE TABLE dbo.Employee(
>         EmployeeID              int     IDENTITY(1,1)           NOT
> NULL
>                 CONSTRAINT pk_EmployeeID PRIMARY KEY NONCLUSTERED,
>         BossID                  int                             NULL,
>         DepartmentID            int                             NULL,
>         FullName                varchar(60)                     NULL
> ) ON [PRIMARY]
> GO
>
> INSERT INTO dbo.Employee(BossID, DepartmentID, FullName) VALUES(NULL,
> 1, 'Pointed-haired Boss')
> INSERT INTO dbo.Employee(BossID, DepartmentID, FullName) VALUES(1, 2,
> 'Dilbert')
> INSERT INTO dbo.Employee(BossID, DepartmentID, FullName) VALUES(1, 2,
> 'Wally')
>
> CREATE TABLE dbo.WitchHunt(
> WitchHuntID int     IDENTITY(1,1)           NOT NULL
>                 CONSTRAINT pk_WitchyHuntID PRIMARY KEY NONCLUSTERED,
> EmployeeID              int NOT NULL,
>         BossID                  int                             NULL,
>         DepartmentID            int                             NULL,
>         FullName                varchar(60)                     NULL
> ) ON [PRIMARY]
> GO
>
> CREATE PROCEDURE dbo.spReturnEmployees(
> @Param1 int
> ) AS
>
> SELECT EmployeeID, BossID, DepartmentID, FullName  FROM Employee WHERE
> EmployeeID <> @Param1
>
> RETURN 1
> GO
>
> Someone in the office has sneaked a secret memo to the newspaper, and
> rather than sort out the bad press the pointed-haired boss has started
> a witch hunt (more fun).  He obviously does not want to include
> himself:
>
> DECLARE @EmployeeID int
> DECLARE @BossID int
> DECLARE @DepartmentID int
> DECLARE @FullName varchar(30)
>
> CREATE TABLE #Temp(EmployeeID int, BossID int, DepartmentID int,
> FullName varchar(60))
>
> INSERT INTO #Temp
> EXEC spReturnEmployees 1
>
> DECLARE curEmployeeLoop CURSOR FOR (SELECT * FROM #Temp)
>
> OPEN curEmployeeLoop
>
> FETCH NEXT FROM curEmployeeLoop INTO @EmployeeID, @BossID,
> @DepartmentID, @FullName
>
> WHILE @@FETCH_STATUS = 0
> BEGIN
>
> INSERT INTO WitchHunt(EmployeeID, BossID, DepartmentID, FullName)
> VALUES(@EmployeeID, @BossID, @DepartmentID, @FullName)
> -- Do some complicated SQL stuff here, otherwise there is no point for
> a cursor
>
> FETCH NEXT FROM curEmployeeLoop INTO @EmployeeID, @BossID,
> @DepartmentID, @FullName
>
> END
>
> CLOSE curEmployeeLoop
> DEALLOCATE curEmployeeLoop
>
> DROP TABLE #Temp
>
> SELECT * FROM dbo.WitchHunt
> GO
>
> The pointy-haired boss now goes around the cubicles, interrogating the
> employees on the list (both of them).
>
> Regards,
>
> -Tav.-
>
> Tavis Pitt
>