| Author |
Topic |
|
whr2
Starting Member
9 Posts |
 Posted - 2006-03-21 : 16:01:53
|
| I've been given a report to modify as the old developer is no longer here. The report calls a stored procedure and it was deleting data that was built in a temp table based on a paramter passed to the stored procedure (delete from #temp where id = @id)I need to change this to handle somethign like this:delete from #temp where id in (@id)and @id will look like this: 1,2Problem is it deletes everythig in the temp table. I fgure it has to do with how I'm trying to do this task, so looking for input on solving this issue.Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
whr2
Starting Member
9 Posts |
Posted - 2006-03-22 : 07:47:56
|
| Thanks tkizer, one question though, will that work on a SQL 7 database? |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-03-22 : 07:54:56
|
| Test itMadhivananFailing to plan is Planning to fail |
|
|
|
whr2
Starting Member
9 Posts |
Posted - 2006-03-22 : 08:08:40
|
| I did some searching on ask.com and as I thought user defined functions are not supported in SQL 7. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-03-22 : 08:12:26
|
| Yes it is. Read the link I specified and try the specified methodsMadhivananFailing to plan is Planning to fail |
|
|
|
whr2
Starting Member
9 Posts |
Posted - 2006-03-22 : 08:35:46
|
| Which specified methods? I tried the ones using an UDF (as tkizer post suggested I use, and what I inquired about), and the UDF fails to create (I have admin rights on this database). |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-03-22 : 08:39:25
|
| Try delete from #temp where ','+@id+',' like '%,'+cast(id as varchar(10))+',%'MadhivananFailing to plan is Planning to fail |
|
|
|
whr2
Starting Member
9 Posts |
Posted - 2006-03-22 : 09:23:34
|
| Tried that, didn't work. What I ended up doing was parsing the CSV values into another temp table. Then changing my query to delete values to delete from #temp where reject_id not in (select reject_id from #reject). Not as nice as doing a UDF (especially where I could use this in some other routines), but it works.Thanks |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-03-22 : 09:29:04
|
| >>Tried that, didn't work.Did you get error?MadhivananFailing to plan is Planning to fail |
|
|
|
whr2
Starting Member
9 Posts |
Posted - 2006-03-22 : 16:08:00
|
| No error message at all, just deleted everything instead of the things in my not in clause. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-03-23 : 01:33:36
|
| If it not in list then use not like in my queryMadhivananFailing to plan is Planning to fail |
|
|
|
pootle_flump
1064 Posts |
Posted - 2006-03-23 : 06:20:49
|
| HiOut of curiosity - and not directly related to your question - how come the sproc is populating the temp table and then deleting all records except those of the supplied ID? It implies that the entire contents of a table are being dumped without a where clause. If so - this is inefficient. Are you able to use the where clause from your delete statement in your insert statement so you only get the required records in your temp table right from the start? One process instead of two and lots less IO. HTH |
|
|
|
whr2
Starting Member
9 Posts |
Posted - 2006-03-23 : 07:39:14
|
| I agree it is inefficient, and as I said it is something that got handed to me after the person that originally set it up left the company. I looked at changing it so it wouldn't behave like this and pretty much concluded it would require an entire re-write. Looking at the time required to re-write I figured it would be much quicker to do this fix and address things later.The person that developed this SP did many others too. Too often he had 4 or 5 SP's that were returning similair data. Maybe this one had a different field, but from the same database, things like that. I would like to reduce all of these to a single SP, but then I also need to figure out which reports are using these SP, something else he never documented. Fun, fun, fun.I could probably do it in the where clause that is inserting into the database, but even that would have required some work, and actually modifying a second SP that this one was calling, with no idea what else is using that second SP. |
|
|
|
|
Deleting data in a temp table