Help with looping through records in stored procedure

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
GOHi
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
___________________________________
<CarlosSanchezJr@.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
>|||I'll get the following error:
Error 156: Incorrect syntax near the keyword 'EXEC'
Thanks!
Jack Vamvas wrote:
> 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
> ___________________________________
>
> <CarlosSanchezJr@.gmail.com> wrote in message
> news:1149639693.901501.151990@.g10g2000cwb.googlegroups.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 */|||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/
CarlosSanchezJr@.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
> END /*this goes back to the beginning of the while loop */|||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/
>
> CarlosSanchezJr@.gmail.com wrote:|||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/
CarlosSanchezJr@.gmail.com wrote:
> 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:|||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. :-)|||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'?