Home All Groups Group Topic Archive Search About

Help with looping through records in stored procedure

microsoft.public.sqlserver.programming
Author
7 Jun 2006 12:21 AM
CarlosSanchezJr@gmail.com
I'm not even sure if I'm doing this correctly.  Basically, my goal is
to provide the stored procedure 2 pieces of information that may
change:
- a member ID #
- a "WHERE" clause statement

I want the SP to loop through all the records it finds, and as it is
looping through, insert records into a relational table if necessary.
I am getting syntax errors when trying the below.
===========================================================
CREATE PROCEDURE dbo.InsertAvpArticles2
    @premiumUserId int,
    @whereClause varchar(5000)
AS

DECLARE @articleId int
DECLARE @categoryId int
DECLARE @hotelId int

SET NOCOUNT ON

WHILE EXISTS(EXEC('SELECT @articleId=articleId, @categoryId=categoryId,
@hotelId=hotelId FROM tblArticles WHERE' + @whereClause))
BEGIN

    IF NOT EXISTS (SELECT * FROM tblAVPArticles WHERE
    premiumUserId = @premiumUserId AND articleId = @articleId)

    BEGIN

    INSERT tblAVPArticles
    (premiumUserId, articleId, categoryId, hotelId, createdByAvp)
    VALUES
    (@premiumUserId, @articleId, @categoryId, @hotelId, 1)

    END

END
GO

Author
7 Jun 2006 8:18 AM
Jack Vamvas
Hi

What syntax errors are you getting --which line?
For example, on line 13 , there is no space b/w WHERE and the @whereclause

--
----
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________


<CarlosSanche***@gmail.com> wrote in message
Show quoteHide quote
news:1149639693.901501.151990@g10g2000cwb.googlegroups.com...
> I'm not even sure if I'm doing this correctly.  Basically, my goal is
> to provide the stored procedure 2 pieces of information that may
> change:
> - a member ID #
> - a "WHERE" clause statement
>
> I want the SP to loop through all the records it finds, and as it is
> looping through, insert records into a relational table if necessary.
> I am getting syntax errors when trying the below.
> ===========================================================
> CREATE PROCEDURE dbo.InsertAvpArticles2
> @premiumUserId int,
> @whereClause varchar(5000)
> AS
>
> DECLARE @articleId int
> DECLARE @categoryId int
> DECLARE @hotelId int
>
> SET NOCOUNT ON
>
> WHILE EXISTS(EXEC('SELECT @articleId=articleId, @categoryId=categoryId,
> @hotelId=hotelId FROM tblArticles WHERE' + @whereClause))
> BEGIN
>
> IF NOT EXISTS (SELECT * FROM tblAVPArticles WHERE
> premiumUserId = @premiumUserId AND articleId = @articleId)
>
> BEGIN
>
> INSERT tblAVPArticles
> (premiumUserId, articleId, categoryId, hotelId, createdByAvp)
> VALUES
> (@premiumUserId, @articleId, @categoryId, @hotelId, 1)
>
> END
>
> END
> GO
>
Are all your drivers up to date? click for free checkup

Author
7 Jun 2006 1:40 PM
CarlosSanchezJr@gmail.com
I'll get the following error:

Error 156: Incorrect syntax near the keyword 'EXEC'

Thanks!

Jack Vamvas wrote:
Show quoteHide quote
> Hi
>
> What syntax errors are you getting --which line?
> For example, on line 13 , there is no space b/w WHERE and the @whereclause
>
> --
> ----
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
>
> <CarlosSanche***@gmail.com> wrote in message
> news:1149639693.901501.151990@g10g2000cwb.googlegroups.com...
> > I'm not even sure if I'm doing this correctly.  Basically, my goal is
> > to provide the stored procedure 2 pieces of information that may
> > change:
> > - a member ID #
> > - a "WHERE" clause statement
> >
> > I want the SP to loop through all the records it finds, and as it is
> > looping through, insert records into a relational table if necessary.
> > I am getting syntax errors when trying the below.
> > ===========================================================
> > CREATE PROCEDURE dbo.InsertAvpArticles2
> > @premiumUserId int,
> > @whereClause varchar(5000)
> > AS
> >
> > DECLARE @articleId int
> > DECLARE @categoryId int
> > DECLARE @hotelId int
> >
> > SET NOCOUNT ON
> >
> > WHILE EXISTS(EXEC('SELECT @articleId=articleId, @categoryId=categoryId,
> > @hotelId=hotelId FROM tblArticles WHERE' + @whereClause))
> > BEGIN
> >
> > IF NOT EXISTS (SELECT * FROM tblAVPArticles WHERE
> > premiumUserId = @premiumUserId AND articleId = @articleId)
> >
> > BEGIN
> >
> > INSERT tblAVPArticles
> > (premiumUserId, articleId, categoryId, hotelId, createdByAvp)
> > VALUES
> > (@premiumUserId, @articleId, @categoryId, @hotelId, 1)
> >
> > END
> >
> > END
> > GO
> >
Author
7 Jun 2006 1:56 PM
CarlosSanchezJr@gmail.com
Actually I find a method of how to do this, and I'm trying it below.  I
get an error though (Must declare the variable @li_seq_no) about
declaring a variable, although I am declaring it.  Any thoughts?

CREATE PROCEDURE dbo.InsertAvpArticles2
    @premiumUserId int,
    @whereClause varchar(5000)
AS

DECLARE @articleId int
DECLARE @categoryId int
DECLARE @hotelId int
DECLARE @li_seq_no int
/* hold the current pk we're working on */

/* Get starting row primary key */
EXEC('SELECT @li_seq_no = MIN(articleId) FROM tblArticles WHERE ' +
@whereClause )

/* While loop, processing ALL records, see the end of the loop on how
to
get the "next pk" selection */

WHILE (@li_seq_no != NULL)
BEGIN
    /* Select what you want from the row at the current pk */
    SELECT @articleId=articleId, @categoryId=categoryId, @hotelId=hotelId
FROM tblArticles
    WHERE articleId = @li_seq_no

    IF NOT EXISTS (SELECT * FROM tblAVPArticles WHERE
    premiumUserId = @premiumUserId AND articleId = @articleId)

    BEGIN

    INSERT tblAVPArticles
    (premiumUserId, articleId, categoryId, hotelId, createdByAvp)
    VALUES
    (@premiumUserId, @articleId, @categoryId, @hotelId, 1)

    END

    /* get the next pk like this */
    SELECT @li_seq_no = MIN(articleId) FROM tblArticles WHERE articleId
>@li_seq_no

END   /*this goes back to the beginning of the while loop */
Author
7 Jun 2006 2:09 PM
SQL Menace
Here is one way (untested of course ;-(  )
you can also use an (evil) cursor for this
I am almost positive that you can do this in 1 set statement, but
without data and DLL I won't embark on such a mission
Less talk more code, here it is


CREATE PROCEDURE dbo.InsertAvpArticles2
        @premiumUserId int,
        @whereClause varchar(5000)
AS


DECLARE @articleId int
DECLARE @categoryId int
DECLARE @hotelId int


SET NOCOUNT ON

create table #temp (id int identity,
articleId int,categoryId int,hotelId int)

exec ('insert #temp(articleId ,categoryId ,hotelId)
select articleId ,categoryId ,hotelId
FROM tblArticles WHERE' + @whereClause)

declare @LoopID int, @MaxID int
select @LoopID=1, @MaxID = MAX(ID) from #temp

WHILE @LoopID <= @MaxID
BEGIN

    SELECT @articleId=articleId, @categoryId=categoryId,
    @hotelId=hotelId FROM #temp
    where ID = @LoopID


        IF NOT EXISTS (SELECT * FROM tblAVPArticles WHERE
        premiumUserId = @premiumUserId AND articleId = @articleId)


        BEGIN
            INSERT tblAVPArticles
            (premiumUserId, articleId, categoryId, hotelId, createdByAvp)
            VALUES
            (@premiumUserId, @articleId, @categoryId, @hotelId, 1)
        END

SET @LoopID = @LoopID + 1
END
GO


Denis the SQL Menace
http://sqlservercode.blogspot.com/


CarlosSanche***@gmail.com wrote:
Show quoteHide quote
> Actually I find a method of how to do this, and I'm trying it below.  I
> get an error though (Must declare the variable @li_seq_no) about
> declaring a variable, although I am declaring it.  Any thoughts?
>
> CREATE PROCEDURE dbo.InsertAvpArticles2
>     @premiumUserId int,
>     @whereClause varchar(5000)
> AS
>
> DECLARE @articleId int
> DECLARE @categoryId int
> DECLARE @hotelId int
> DECLARE @li_seq_no int
> /* hold the current pk we're working on */
>
> /* Get starting row primary key */
> EXEC('SELECT @li_seq_no = MIN(articleId) FROM tblArticles WHERE ' +
> @whereClause )
>
> /* While loop, processing ALL records, see the end of the loop on how
> to
> get the "next pk" selection */
>
> WHILE (@li_seq_no != NULL)
> BEGIN
>     /* Select what you want from the row at the current pk */
>     SELECT @articleId=articleId, @categoryId=categoryId, @hotelId=hotelId
> FROM tblArticles
>     WHERE articleId = @li_seq_no
>
>     IF NOT EXISTS (SELECT * FROM tblAVPArticles WHERE
>     premiumUserId = @premiumUserId AND articleId = @articleId)
>
>     BEGIN
>
>     INSERT tblAVPArticles
>     (premiumUserId, articleId, categoryId, hotelId, createdByAvp)
>     VALUES
>     (@premiumUserId, @articleId, @categoryId, @hotelId, 1)
>
>     END
>
>     /* get the next pk like this */
>     SELECT @li_seq_no = MIN(articleId) FROM tblArticles WHERE articleId
> >@li_seq_no
>
> END   /*this goes back to the beginning of the while loop */
Author
7 Jun 2006 2:14 PM
CarlosSanchezJr@gmail.com
That's fairly interesting.  But would that work with thousands of users
logged into the system and possibly hundreds of them hitting that page
(which would in turn I guess create a temp table for each one of them)?

Thanks!

SQL Menace wrote:
Show quoteHide quote
> Here is one way (untested of course ;-(  )
> you can also use an (evil) cursor for this
> I am almost positive that you can do this in 1 set statement, but
> without data and DLL I won't embark on such a mission
> Less talk more code, here it is
>
>
> CREATE PROCEDURE dbo.InsertAvpArticles2
>         @premiumUserId int,
>         @whereClause varchar(5000)
> AS
>
>
> DECLARE @articleId int
> DECLARE @categoryId int
> DECLARE @hotelId int
>
>
> SET NOCOUNT ON
>
> create table #temp (id int identity,
> articleId int,categoryId int,hotelId int)
>
> exec ('insert #temp(articleId ,categoryId ,hotelId)
> select articleId ,categoryId ,hotelId
> FROM tblArticles WHERE' + @whereClause)
>
> declare @LoopID int, @MaxID int
> select @LoopID=1, @MaxID = MAX(ID) from #temp
>
> WHILE @LoopID <= @MaxID
> BEGIN
>
>     SELECT @articleId=articleId, @categoryId=categoryId,
>     @hotelId=hotelId FROM #temp
>     where ID = @LoopID
>
>
>         IF NOT EXISTS (SELECT * FROM tblAVPArticles WHERE
>         premiumUserId = @premiumUserId AND articleId = @articleId)
>
>
>         BEGIN
>             INSERT tblAVPArticles
>             (premiumUserId, articleId, categoryId, hotelId, createdByAvp)
>             VALUES
>             (@premiumUserId, @articleId, @categoryId, @hotelId, 1)
>         END
>
> SET @LoopID = @LoopID + 1
> END
> GO
>
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
>
> CarlosSanche***@gmail.com wrote:
> > Actually I find a method of how to do this, and I'm trying it below.  I
> > get an error though (Must declare the variable @li_seq_no) about
> > declaring a variable, although I am declaring it.  Any thoughts?
> >
> > CREATE PROCEDURE dbo.InsertAvpArticles2
> >     @premiumUserId int,
> >     @whereClause varchar(5000)
> > AS
> >
> > DECLARE @articleId int
> > DECLARE @categoryId int
> > DECLARE @hotelId int
> > DECLARE @li_seq_no int
> > /* hold the current pk we're working on */
> >
> > /* Get starting row primary key */
> > EXEC('SELECT @li_seq_no = MIN(articleId) FROM tblArticles WHERE ' +
> > @whereClause )
> >
> > /* While loop, processing ALL records, see the end of the loop on how
> > to
> > get the "next pk" selection */
> >
> > WHILE (@li_seq_no != NULL)
> > BEGIN
> >     /* Select what you want from the row at the current pk */
> >     SELECT @articleId=articleId, @categoryId=categoryId, @hotelId=hotelId
> > FROM tblArticles
> >     WHERE articleId = @li_seq_no
> >
> >     IF NOT EXISTS (SELECT * FROM tblAVPArticles WHERE
> >     premiumUserId = @premiumUserId AND articleId = @articleId)
> >
> >     BEGIN
> >
> >     INSERT tblAVPArticles
> >     (premiumUserId, articleId, categoryId, hotelId, createdByAvp)
> >     VALUES
> >     (@premiumUserId, @articleId, @categoryId, @hotelId, 1)
> >
> >     END
> >
> >     /* get the next pk like this */
> >     SELECT @li_seq_no = MIN(articleId) FROM tblArticles WHERE articleId
> > >@li_seq_no
> >
> > END   /*this goes back to the beginning of the while loop */
Author
7 Jun 2006 2:16 PM
SQL Menace
Why wouldn' it?
A temp table is unique to the connection
Your alternative is a cursor or rewriting it as a SET statement


Denis the SQL Menace
http://sqlservercode.blogspot.com/

CarlosSanche***@gmail.com wrote:
Show quoteHide quote
> That's fairly interesting.  But would that work with thousands of users
> logged into the system and possibly hundreds of them hitting that page
> (which would in turn I guess create a temp table for each one of them)?
>
> Thanks!
>
> SQL Menace wrote:
> > Here is one way (untested of course ;-(  )
> > you can also use an (evil) cursor for this
> > I am almost positive that you can do this in 1 set statement, but
> > without data and DLL I won't embark on such a mission
> > Less talk more code, here it is
> >
> >
> > CREATE PROCEDURE dbo.InsertAvpArticles2
> >         @premiumUserId int,
> >         @whereClause varchar(5000)
> > AS
> >
> >
> > DECLARE @articleId int
> > DECLARE @categoryId int
> > DECLARE @hotelId int
> >
> >
> > SET NOCOUNT ON
> >
> > create table #temp (id int identity,
> > articleId int,categoryId int,hotelId int)
> >
> > exec ('insert #temp(articleId ,categoryId ,hotelId)
> > select articleId ,categoryId ,hotelId
> > FROM tblArticles WHERE' + @whereClause)
> >
> > declare @LoopID int, @MaxID int
> > select @LoopID=1, @MaxID = MAX(ID) from #temp
> >
> > WHILE @LoopID <= @MaxID
> > BEGIN
> >
> >     SELECT @articleId=articleId, @categoryId=categoryId,
> >     @hotelId=hotelId FROM #temp
> >     where ID = @LoopID
> >
> >
> >         IF NOT EXISTS (SELECT * FROM tblAVPArticles WHERE
> >         premiumUserId = @premiumUserId AND articleId = @articleId)
> >
> >
> >         BEGIN
> >             INSERT tblAVPArticles
> >             (premiumUserId, articleId, categoryId, hotelId, createdByAvp)
> >             VALUES
> >             (@premiumUserId, @articleId, @categoryId, @hotelId, 1)
> >         END
> >
> > SET @LoopID = @LoopID + 1
> > END
> > GO
> >
> >
> > Denis the SQL Menace
> > http://sqlservercode.blogspot.com/
> >
> >
> > CarlosSanche***@gmail.com wrote:
> > > Actually I find a method of how to do this, and I'm trying it below.  I
> > > get an error though (Must declare the variable @li_seq_no) about
> > > declaring a variable, although I am declaring it.  Any thoughts?
> > >
> > > CREATE PROCEDURE dbo.InsertAvpArticles2
> > >     @premiumUserId int,
> > >     @whereClause varchar(5000)
> > > AS
> > >
> > > DECLARE @articleId int
> > > DECLARE @categoryId int
> > > DECLARE @hotelId int
> > > DECLARE @li_seq_no int
> > > /* hold the current pk we're working on */
> > >
> > > /* Get starting row primary key */
> > > EXEC('SELECT @li_seq_no = MIN(articleId) FROM tblArticles WHERE ' +
> > > @whereClause )
> > >
> > > /* While loop, processing ALL records, see the end of the loop on how
> > > to
> > > get the "next pk" selection */
> > >
> > > WHILE (@li_seq_no != NULL)
> > > BEGIN
> > >     /* Select what you want from the row at the current pk */
> > >     SELECT @articleId=articleId, @categoryId=categoryId, @hotelId=hotelId
> > > FROM tblArticles
> > >     WHERE articleId = @li_seq_no
> > >
> > >     IF NOT EXISTS (SELECT * FROM tblAVPArticles WHERE
> > >     premiumUserId = @premiumUserId AND articleId = @articleId)
> > >
> > >     BEGIN
> > >
> > >     INSERT tblAVPArticles
> > >     (premiumUserId, articleId, categoryId, hotelId, createdByAvp)
> > >     VALUES
> > >     (@premiumUserId, @articleId, @categoryId, @hotelId, 1)
> > >
> > >     END
> > >
> > >     /* get the next pk like this */
> > >     SELECT @li_seq_no = MIN(articleId) FROM tblArticles WHERE articleId
> > > >@li_seq_no
> > >
> > > END   /*this goes back to the beginning of the while loop */
Author
7 Jun 2006 2:33 PM
CarlosSanchezJr@gmail.com
That seemed to work!  Though if possible, I would still like to
understand completely why the second SP I wrote failed and how that
could be corrected for knowledge purposes.  :-)
Author
7 Jun 2006 3:40 PM
CarlosSanchezJr@gmail.com
Another issue...

Say my variable, @whereClause, has a value of the following:

accomodationTypes IN ('Villas','Deluxe')

This is throwing an error if I wrap it in single qoutes like I need to.
How do you wrap something like that in single qoutes yet account for
the string values 'Villas' and 'Deluxe'?

Bookmark and Share

Post Other interesting topics
Turn this SP into a view!
Tough SQL problem, need expert advice!!!
Advice Requested : Trying to write portable SQL
Date Parsing using T-SQL
find the first row of ordered records that sum is less than a cert
Comparing dates in one field
Obtain values from different tables
Insert by Parameter
trigger will not execute
Using LIKE operator and spacing to search SPs