Running a batch update - 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 2005 Forums

Transact-SQL (2005)

Running a batch update

Author

Topic

jodders
Starting Member

41 Posts

Posted - 2013-08-02 : 09:38:30

Hi all,

I basically would like to remove a set of unique clients numbers in one rather than having to highlight each one. I have tried combinations but SQL doesn't like it.

They are stored procedures so I think this is slightly different then doing an insert or delete.


SP_CMS_CLIENTGRP_DEL   3769,'PR'
SP_CMS_CLIENTGRP_DEL   3828,'PR'

Do you have any advice?

Thanks
Jods

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-02 : 10:45:51

quote:
Originally posted by jodders

Hi all,

I basically would like to remove a set of unique clients numbers in one rather than having to highlight each one. I have tried combinations but SQL doesn't like it.

They are stored procedures so I think this is slightly different then doing an insert or delete.

SP_CMS_CLIENTGRP_DEL 3769,'PR'
SP_CMS_CLIENTGRP_DEL 3828,'PR'

Do you have any advice?

Thanks
Jods

Didn't quite get what you want to do. Did you mean that you want to run the stored procedure just once to handle both the client numbers? If you do, you will have to look into the stored procedure code to see if it can handle that at all. My suspicion is that the way it is written now, it cannot. It expects a single integer as the first parameter. So in order to make it handle more than one integer, you will need to open up the code and make appropriate modifications.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-03 : 03:32:21

Your stored procedure needs to accept multiple values for that and then logic should be modified to do the deletion of all the records in a batch
there are various ways of doing this.
see

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

As an example


CREATE PROC SP_CMS_CLIENTGRP_DEL   
@ValueList varchar(1000),
...
AS
DELETE 
FROM table
WHERE ',' + @ValueList + ',' LIKE '%,' + CAST(ClientGroupColumn AS varchar(10)) + ',%'
AND ... other conditions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jodders
Starting Member

41 Posts

Posted - 2013-08-05 : 06:46:23

Thanks for your help, I will have a look at passing the information in.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-05 : 07:36:08

cool
let us know if you need anything else

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources