Which is the better method? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Which is the better method?

Author

Topic

timgaunt
Posting Yak Master

115 Posts

Posted - 2004-11-15 : 05:49:04

Hi,

I'm writing an admin section which was origionally in ASP with an MS Access dB which processed a lot of the information in the ASP but now because I'm updating to SQL Server I want to move as much of the processing that SQL Server can do out of the code.

I've managed to sort out most of the areas which have been causing me problems but I would like to know how incorrect etc I am being here, I have a table which contains the Users, one which has the Access Levels and one which is a matrix of thier IDs, I'm currently writing an add/edit user area where the user is able to select a variaty of roles which I would then like to insert in the matrix table with the user ID. The input is in the form of a CSV string which needs to be converted to multiple rows. In my learning, I've come across two different solutions and I would like to know which is best (and why) -I beleive both are from SQLTeam.com btw so thanks :):

1. From the article "Parsing CSV Values Into Multiple Rows" (http://www.sqlteam.com/item.asp?ItemID=2652) -I'm not using this atm because of this tally, the input array has all the information needed (1,2,3,4,5 etc)

2. Not sure where I adapted it from but it was something to do with a search facility (apologies for the sloppy code but I just wrote it to test):

DECLARE @Terms as nvarchar(4000), @U_ID as int, @ID as int
declare @separator_position int -- Locates each separator character
declare @array_value varchar(1000) -- Holds each returned array value
DECLARE @array varchar(1000)
DECLARE @separator char(1)

SET @Terms = 'A,rose,by,any,other,name,smells,just,as,sweet'
SET @U_ID = 1
SET @ID = 1
SET @separator = ','

CREATE TABLE #SearchResults (ItemID int, ItemKeyword varchar(1000))-- Build Temp Table to hold results
-- For loop to work you need an extra separator at the end. Always look to the
-- left of the separator character for each array value
set @array = @Terms + @separator
-- Loop through the string searching for separtor characters
while CHARINDEX(@separator, @array) <> 0
begin
-- patindex matches the a pattern against a string
select @separator_position = CHARINDEX(@separator, @array)
select @array_value = left(@array, @separator_position - 1)
INSERT #SearchResults
SELECT @U_ID, @array_value

-- This replaces what we just processed with an empty string
select @array = stuff(@array, 1, @separator_position, '')
end

SELECT * FROM #SearchResults

Thanks for all your opinions, I need to learn so don't hold back :)

Tim

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-15 : 09:15:18

>>The input is in the form of a CSV string which needs to be converted to multiple rows.

The first is the best for this. the second has nothing to do with your situation -- it's for parsing existing text in a database and creating a table of words for searching.

- Jeff

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-15 : 09:29:42

quote:
Not sure where I adapted it from but it was something to do with a search facility

From here:

http://www.sqlteam.com/item.asp?ItemID=5857

timgaunt
Posting Yak Master

115 Posts

Posted - 2004-11-15 : 10:47:01

Hi, thanks for your response, I've actually ended up passing in the actual value rather than the ID because of the way the form was interacting with the server and ended up modifying the second "solution" to insert into my matrix table:

-- Loop through the string searching for separtor characters
WHILE CHARINDEX(@separator, @array) <> 0
BEGIN
-- patindex matches the a pattern against a string
SELECT @separator_position = CHARINDEX(@separator, @array)
SELECT @array_value = left(@array, @separator_position - 1)
INSERT SMS_Access_Matrix
SELECT @CU_ID, [AG_ID]
FROM SMS_Access_Levels
WHERE [AG_Name] = @array_value
-- This replaces what we just processed with an empty string
SELECT @array = stuff(@array, 1, @separator_position, '')
END

It seems to work fine so unless I'm making some major error I'm happy :) As for where I got the search SP from I beleive it was actually http://www.sqlteam.com/item.asp?ItemID=1876 because you are able to pass in the seperator.

Many thanks for your help guys.

Tim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources