Help with stored proc - 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)

Help with stored proc

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-07-03 : 21:19:57

Well I think this is the most complicated stored proc i've attempted, I'm not the greatest at sql when it comes to procedures that involve manipulating the data like this. Can someone give me a hand? I'll explain what I have to do.

I have to pass a comma delimited string to a stored procedure. The values passed represent keywords for each user. Inside the stored procedure I have to separate all the "keywords" via the comma. Once I have seperated all the keywords I have to do the following.

For example

I am user 500 - I pass the string "books, sqlteam, beer, girls, sports"

I have a table called TBLINTERESTDETAILS
the table has two columns

InterestID and Interest
1 books
2 sqlteam
3 beer

I have another table called TblInterests as follows:
UserID InterestID

500 1
500 2
500 3

After the stored proc has broken down the string into individual keywords it must find whether each individual keyword exists in the TBLINTERESTDETAILS table. If the individual keyword already exists in the TBLINTERESTDETAILS table, I need to insert a row into TBLINTERESTS. The data I need to insert into TBLINTERESTS is the corresponding InterestID along with the UserID (which will be passed to the sproc).

If the keyword does NOT exist, I must insert it into TBLINTERESTDETAILS, and then grab the corresponding InterestID (identity column) and insert that interestID into the TBLINTERESTS (with the userID).

Thats pretty difficult to explain, but if anybody has any suggestions it would be greatly appreciated, or even similar stored procs i can look at and modify / learn from. If i need to explain something better please let me know.

Thanks alot guys! ( again!)
Mike123

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-07-03 : 21:33:16

Mike,

You seem to have all the rules covered, but your ONLY problem is that you have used an Identity Column.

It is very difficult to manage sets of insertion when you have identity columns as SQL only has provisions for returning the last value, so any more than 1 row of data becomes problematic.

Is the Interest attribute unique? Is the (UserID, Interest) combination Unique? If you answered "yes" to these 2 questions, I would recommend altering your design....

DavidM

"SQL-3 is an abomination.."

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-07-03 : 21:49:21

byrmol, thanks again for help on this one ..

I answer yes to your two questions. The interests are unique because I will only have one InterestID for each Interest. The userID, and interestID is unique because the user can only be interested in each 'interest' once.

It sounds like one row at a time inserting is not the way to go? What do you recommmend?

How would you recommend getting around the identity problem also?

Im wide open to suggestions

thanks alot!

mike123

Also: here is a link to a previous post about my design in case it is unclear

[url]http://sqlteam.com/forums/topic.asp?TOPIC_ID=27224[/url]

Edited by - mike123 on 07/03/2003 21:50:44

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-07-03 : 21:51:38

I don't think this is all that difficult, you need a Numbers/Tally/Sequence table:

CREATE PROCEDURE AddInterests @userID int, @interests varchar(8000) AS
SET NOCOUNT ON

SELECT SubString(', '+@interests+', ', ID, CharIndex(', ', ', '+@interests+', ')-ID) AS Interest
INTO #interests
FROM Numbers
WHERE SubString(', '+@interests+', ', ID-2, 2)=', '

INSERT INTO tblInterestDetails(interest)
SELECT I.Interest FROM #interests I
WHERE NOT EXISTS (SELECT * FROM tblInterestDetails WHERE Interest=I.Interest)

--OOOOOOOOOOOPS, I forgot to include this line originally:

DELETE I FROM #interests I INNER JOIN tblInterestDetails D ON I.Interest=D.Interest
INNER JOIN tblInterests T ON T.InterestID=D.InterestID
WHERE T.UserID=@userID

INSERT INTO tblInterests(UserID, InterestID)
SELECT @userID, D.InterestID FROM tblInterestDetails D
INNER JOIN #interests I ON I.Interest=D.Interest

DROP TABLE #interests

FYI - this is not tested, so you may have to tweak it. You can also use any CSV parsing technique to populate the temp table:

http://www.sqlteam.com/SearchResults.asp?SearchTerms=csv

Edited by - robvolk on 07/03/2003 22:33:37

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-07-03 : 22:21:42

Rob's solution will work (you will need to debug it first), but he is relying (correctly in this case) on the fact that you have unique interest's.

If you don't have an Identity Column (You have Interest as the Key) then the final insertion (into TblInterests) is very easy..



--Code that extracts CSV into table (lets call it #Interests as Rob has)

--Exactly the same as Rob's
INSERT INTO tblInterestDetails(interest) 
SELECT I.Interest FROM #interests I 
WHERE NOT EXISTS (SELECT * FROM tblInterestDetails WHERE Interest=I.Interest) 

INSERT INTO tblInterests(UserID, Interest) 
SELECT @userID AS UserID, Interest FROM #interests I

The main point here is that you don't have to "go back" to the tblInterestDetails table to get the "interest" key.

If you get rid of the Identity column then you will never have to join the 2 tables together to find out what a users interest are.

DavidM

"SQL-3 is an abomination.."

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-07-03 : 22:26:17

wow, i wish that wasnt all that difficult for me ! :)

thanks alot rob, im playing with it now trying to get it to work

im getting this error, looks pretty minor .. if you see it right away, i'd love to know

Server: Msg 174, Level 15, State 1, Procedure AddInterests, Line 8
The substring function requires 3 arguments.

thanks again rob!

Mike123

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-07-03 : 22:33:27

byrmol,

thanks for the update

which way do you think is faster? I'm looking but its going to take me awhile to break down and figure everything out...

what are the advantages/ disadvantages of the two ways? One thing that I did not mention was updates. I am planning on just deleting all records for the user then doing an insert to make life easier. I dont see too much of a downside to doing it that way.

Any input greatly appreciated, I want to make sure I do this right, speed is everything! ..

thanks to both of you

mike123

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-07-03 : 22:38:34

Check it now, I fixed the line I think is causing the problem. Make sure to use the version that's there now, I tweaked it twice already.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-07-03 : 22:44:27

the procedure creates properly, however when I run it I get the following error

I'm attempting to look into it

Server: Msg 208, Level 16, State 1, Procedure AddInterests, Line 8
Invalid object name 'Numbers'.

thanks again rob!!

mike123

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-07-03 : 22:56:23

Well, I said originally that you need a numbers/tally/sequence table, you can create one fairly easily:

CREATE TABLE Numbers(ID smallint NOT NULL PRIMARY KEY)
INSERT Numbers VALUES(1)
WHILE (SELECT Max(ID) FROM Numbers)<4097
INSERT INTO Numbers SELECT ID+(SELECT Max(ID) FROM Numbers) FROM Numbers

You also don't need to use this method, check the other CSV articles for other ways to parse a CSV.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-07-03 : 23:11:54

sorry i interpreted that as a temp table for some reason ... well it almost there ..

Im getting this error

Server: Msg 536, Level 16, State 3, Procedure AddInterests, Line 8
Invalid length parameter passed to the substring function.
The statement has been terminated.

this is the line that does it on the length parameter, any ideas ?? I will be eternally grateful! .. well i already am

SELECT SubString(', '+@interests+', ', ID, CharIndex(', ', ', '+@interests+', ')-ID) AS Interest

thanks again

mike123

Edited by - mike123 on 07/03/2003 23:12:13

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-07-03 : 23:14:23

Did David say you'd have to debug this a little?

CREATE PROCEDURE AddInterests @userID int, @interests varchar(8000) AS
SET NOCOUNT ON

SELECT SubString(', '+@interests+', ', ID, CharIndex(', ', ', '+@interests+', ')-ID) AS Interest
INTO #interests
FROM Numbers
WHERE SubString(', '+@interests+', ', ID-2, 2)=', ' AND ID<=Len(', '+@interests+', ')

INSERT INTO tblInterestDetails(interest)
SELECT I.Interest FROM #interests I
WHERE NOT EXISTS (SELECT * FROM tblInterestDetails WHERE Interest=I.Interest)

--OOOOOOOOOOOPS, I forgot to include this line originally:

DELETE I FROM #interests I INNER JOIN tblInterestDetails D ON I.Interest=D.Interest
INNER JOIN tblInterests T ON T.InterestID=D.InterestID
WHERE T.UserID=@userID

INSERT INTO tblInterests(UserID, InterestID)
SELECT @userID, D.InterestID FROM tblInterestDetails D
INNER JOIN #interests I ON I.Interest=D.Interest

DROP TABLE #interests

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-07-03 : 23:33:23

hmmm no luck rob, i tried playing with it to the best my knowledge but still no luck (same error)... i seriously owe you for this one .. i dont know if your strictly sql but if you ever need any vb.net im not bad with it .. i'll offer what i can

any help appreciated

thanks alot

mike123

Edited by - mike123 on 07/03/2003 23:33:44

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-07-03 : 23:36:35

quote:

which way do you think is faster? I'm looking but its going to take me awhile to break down and figure everything out...

what are the advantages/ disadvantages of the two ways? One thing that I did not mention was updates. I am planning on just deleting all records for the user then doing an insert to make life easier. I dont see too much of a downside to doing it that way.

"Faster" is a relative term. But in a very ill-defined nut shell.. The less joins the faster.

As for Deleting first, I do NOT recommend this. For a start you increase IO, think of the log file, the index and statistics maintenance that must happen, then there is locks placed on the affected rows. Update is essentially a delete then a insert and SQL Server will determine if it needs to do just that. But it can do a update in place (no delete or insert required). If you can get away with using the EXISTS expression as in Rob's example, then that is the way to go.

But the only real way, is to TEST..

DavidM

"SQL-3 is an abomination.."

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-07-07 : 18:53:35

I've been trying to figure out this sp but I still can't get it going. I think its a fairly small problem, but I havent' been able to figure it out. Does anyone have any ideas?

Here is the error message I am getting

Server: Msg 536, Level 16, State 3, Procedure AddInterests, Line 4
Invalid length parameter passed to the substring function.
The statement has been terminated.

This is the stored proc I am currently using


alter PROCEDURE AddInterests @userID int, @interests varchar(8000) AS 
SET NOCOUNT ON 

SELECT SubString(', '+@interests+', ', ID, CharIndex(', ', ', '+@interests+', ')-ID) AS Interest 
INTO #interests 
FROM Numbers 
WHERE SubString(', '+@interests+', ', ID-2, 2)=', ' AND ID<=Len(', '+@interests+', ') 

INSERT INTO tblInterestDetails(interest) 
SELECT I.Interest FROM #interests I 
WHERE NOT EXISTS (SELECT * FROM tblInterestDetails WHERE Interest=I.Interest) 


DELETE I FROM #interests I INNER JOIN tblInterestDetails D ON I.Interest=D.Interest 
INNER JOIN tblInterests T ON T.InterestID=D.InterestID 
WHERE T.UserID=@userID 

INSERT INTO tblInterests(UserID, InterestID) 
SELECT @userID, D.InterestID FROM tblInterestDetails D 
INNER JOIN #interests I ON I.Interest=D.Interest 

DROP TABLE #interests

This is some sample data of what goes in the tables. Any help greatly appreciated.

Thanks

Mike123

TBLINTERESTS

userID interestID

500 1
500 2
501 1

TBLINTERESTDETAILS

1 music
2 movies
3 writing
4 reading
5 friends
6 computers

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-08 : 07:52:36

If speed is as important to this as you say, in this case do NOT use a tally or Numbers table. just loop through your variable the old fashioned way and insert the values into the table directly as you parse them.

Set-based methods are usually the way to go, but not in this case. By using the numbers table, you are bringing in all kinds of processing and I/O (bringing a numbers table into memory) that you do not need, when you can simply loop through a few characters in a string variable and put values in to a table one at a time. Or, put them into a temp table first (or table variable) the way you are doing it now.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources