news:1149707271.546045.40900@j55g2000cwa.googlegroups.com...
> Say my parameter, @whereClause, has the following value passed to it:
>
> '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'?
>
> It is odd because in Query Analyzer i pass over the following to the
> stored procedure:
>
> exec InsertAvpArticles3 @premiumUserId=100001, @whereClause='articleId
> accomodationTypes IN (''Villas'',''Suites''))'
>
> This executes fine in Analyzer.  Then I try to run it from my ASP page,
> and it throws an incorrect syntax error (near ')').
>
> Any ideas?
>

news:1149709735.571449.313350@h76g2000cwa.googlegroups.com...
>I made a mistake in typing that text here.  It really looks like this:
> ================================================
> exec InsertAvpArticles3 @premiumUserId=100001, @whereClause='articleId
> IN (SELECT articleId FROM tblArticlesAccomodationTypeRelationship WHERE
> accomodationTypes IN (''Villas'',''Suites''))'
> ================================================
> Note there are no line breaks, unless this group editor is
> intentionally putting one in.  The only thing that changes is the WHERE
> clause and the 'premiumUserId' (WHERE clause must be dynamic, there are
> at least 150 combinations, and I'm trying not to make 150 stored
> procedures).
>
> Below is the SP.  It works just fine with numeric values, but not with
> text, as I noted in my first post.
> ===================================================
> CREATE PROCEDURE dbo.InsertAvpArticles3
>        @premiumUserId int,
>        @whereClause varchar(1000)
> 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
>

> You need to escape your single quotes again, I think, because the parser
> will need to pass over this parameter twice -- once when accepting it on SP
> call, and once when running it through the subsequent EXEC call.  Try
>
> exec InsertAvpArticles3 @premiumUserId=100001, @whereClause='articleId
>  IN (SELECT articleId FROM tblArticlesAccomodationTypeRelationship WHERE
>  accomodationTypes IN (''''Villas'''',''''Suites''''))'