February 26, 2002 at 2:51 pm
I may not be looking at this the right way. I am using ADO to execute an SQL stored procedure. I am trying to update records based on how a user links them together.
For instance, if a user has task 14 and originally linked them to clients 12,13,14 then I 3 entries into the clienttask table. Now the user edits the task and now associates the task with clients 0,1,12. I now have to update the clienttask table. Looking at it, I need to delete the entries for clients 13,14 and add the link record for clients 0,1 .
Here is where I am having some problems. My ADO command is:
oConn.Execute "csp_UpdateClientTask 14,'0,1,2'"
In my stored procedure I have:
/* The parameters are */
/* @t_Task_pk char(10), @ClientList varchar(100)*/
/* In the table, t_Client_pk is type INT */
DELETE t_ClientTask
WHERE t_Task_pk = @t_Task_pk
AND t_Client_pk NOT IN (@ClientList)
Obviously, I get a conversion error since t_Client_pk is an INT. Is there a better way of doing this? I had thought that I would have to parse the @ClientList string, create a temporary table and then so a select. That seems like too much trouble. Any thoughts?
David Petersen
dipetersen.com
February 26, 2002 at 4:39 pm
The problem with in passing a variable in is that when it translates it will be this
IN ('0,1,2') and not the IN (0,1,2) that you need.
In this case you may just want to go with a dynamic version.
Ex.
DECLARE @SQLState VARCHAR(1000)
SET @SQLState = 'DELETE t_ClientTask
WHERE t_Task_pk = ' + @t_Task_pk + '
AND t_Client_pk NOT IN (' + @ClientList + ')'
EXEC (@SQLState)
This will handle the issue.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
February 26, 2002 at 10:38 pm
Thank you. That is what I was missing. I kept fooling with the list I was sending as @ClientList to see how I could get it converted correctly. Since this stored procedure will not be executed very often, I think I will go with your suggestion by making it dynamic.
David Petersen
dipetersen.com
February 27, 2002 at 4:28 am
You can avoid dynamic SQL by using code like this:
DECLARE @ClientList varchar(100), @len int, @CurPos int, @PrevPos int
CREATE TABLE #ClientList (names varchar(35))
SET NOCOUNT ON
SET @ClientList = '0,1,2'
SET @len = LEN(@ClientList) + 1
SET @CurPos = 1
SET @PrevPos = @CurPos
WHILE @CurPos < @len + 1
BEGIN
IF SUBSTRING(@ClientList + ',', @CurPos, 1) = ','
BEGIN
INSERT INTO #ClientList (names)
SELECT SUBSTRING(@ClientList,@PrevPos,@CurPos - @PrevPos)
SET @PrevPos = @CurPos + 1
END
SET @CurPos = @CurPos + 1
END
SET NOCOUNT OFF
DELETE t_ClientTask
WHERE t_Task_pk = @t_Task_pk
AND t_Client_pk NOT IN (SELECT * FROM #ClientList)
DROP TABLE #ClientList
And if you check the speed I think you'll find it about the same as the dynamic SQL solution that was posted.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 27, 2002 at 5:16 am
Using 'Not in' will usually result in a slower query plan, avoid it when you can. I find it to be faster to just tag the records as deleted rather than actually deleting - this is both faster because I can use one set of logic and one update query and because I dont have to update any indexes affected by the pseudo deleted row. From a business perspective this is often useful anyway since not unusual for users to delete things by mistake. Then 'delete where deleted=1' to clean things up in a job that runs daily.
Andy
December 8, 2002 at 8:13 pm
Several functions have been posted that parse out a comma separated list of values and let you select/operate on all the rows identified by the keys. Check the scripts area.
December 8, 2002 at 8:14 pm
Several functions have been posted that parse out a comma separated list of values and let you select/operate on all the rows identified by the keys. Check the scripts area.
December 9, 2002 at 7:33 am
NOT IN in this case I believe won't be bad at all because the main filter is on the task, this will return only a few rows and thus NOT IN only has to scan these rows, if they are inserted at the same time then they will liekly be on the same page.
You may also be better of deleting all records in clienttask for that task and then insert the relevant records, if you don't now what the old records are. This is because you don't have to check the record exists before inserting it.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply