adding single quotes to group of varchars

Question

Post reply

adding single quotes to group of varchars

Magy

SSCarpal Tunnel

Points: 4217
More actions
November 18, 2008 at 8:23 am

#71681

I am trying to simulate an array within a stored procedure by passing in a large group of values like this:
EXEC sp_EpiData 1,25,'G1000026, G1000086, G1000088, G1000090, G1000092, G1000094, G1000096, G1000098, G1000100, G1000102, G1000104, G1000106, G1000108, G1000110, G1000112, G1000114, G1000116, G1000118, G1000127, G1000160, G1000167, G1000169, G1000171, G1000173, G1000184, G1000198, G1000200, G1000217, G1000231, G1000234, G1000236, G1000245, G1000247, G1000249, G1000251, G1000253, G1000255, G1000257'
The stored procedure looks like this:
CREATE PROCEDURE sp_EpiData
(
@Page int,
@RecsPerPage int,
@GroupList varchar(1000)
)
AS
SET NOCOUNT ON
--Create a temporary table for the Epi data
CREATE TABLE #tblTempEpiData
(
ID int IDENTITY,
moduleId INT,
folderId INT,
pageId INT
)
-- Insert the rows from needed tables into the temp. table
INSERT INTO #tblTempEpiData
(
moduleId, folderId, pageId
)
SELECT pl.moduleId, pl.folderId, pl.pageId
FROM pageList pl
LEFT JOIN moduleList ml on ml.moduleID = pl.moduleId
WHERE 1 = 1
AND iGroup IN (' + @GroupList + ')
-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
-- return the set of paged records
SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM #tblTempEpiData TI
WHERE TI.ID >= @LastRec
)
FROM #tblTempEpiData
WHERE ID > @FirstRec AND ID < @LastRec
SET NOCOUNT OFF
GO
Before I started trying to add the simulated array, the query worked fine.
Right now, when I exec the stored procedure, I get no results back because the values in "GroupList" need to be surrounded in single quotes like this:
'G1000026', 'G1000086', 'G1000088', 'G1000090'
But I can't do that when I use EXEC, so I need to do it within the stored procedure.
So I guess my question is, now that I have all these values in the SP, how can I put single quotes around all of them?
Thanks!
Magy

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

May I sugget reading the EXCELLENT article by Jeff Moden

http://www.sqlservercentral.com/articles/TSQL/62867/

About half way through the article Jeff has an example that is very, very close to what you want (need) to do.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 2

bitbucket (11/18/2008)
May I sugget reading the EXCELLENT article by Jeff Moden
http://www.sqlservercentral.com/articles/TSQL/62867/
About half way through the article Jeff has an example that is very, very close to what you want (need) to do.

Thanks for the awesome compliment, BitBucket. :blush:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 3

Magy (11/18/2008)
I am trying to simulate an array within a stored procedure by passing in a large group of values like this:
EXEC sp_EpiData 1,25,'G1000026, G1000086, G1000088, G1000090, G1000092, G1000094, G1000096, G1000098, G1000100, G1000102, G1000104, G1000106, G1000108, G1000110, G1000112, G1000114, G1000116, G1000118, G1000127, G1000160, G1000167, G1000169, G1000171, G1000173, G1000184, G1000198, G1000200, G1000217, G1000231, G1000234, G1000236, G1000245, G1000247, G1000249, G1000251, G1000253, G1000255, G1000257'

Magy,

The article Bit Bucket pointed you to is well worth the read for many reasons... once you've learned how the Tally table works, then you can do things like what you want above and more. Take a look at the following article to see what I mean... splits on "steroids"....

http://www.sqlservercentral.com/articles/T-SQL/63003/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)