Thursday, August 25, 2005

parsing a comma delimited string by stored procedure

This is a Code by which u can send a string delimited by comma(or any delimiter u may choose)containing multiple values to a stored proc to be inserted into the DB. It saves considerable DB roundtrips.N.B. There is a caveat : Use it only if u are sure that the string would not exceed 8000 chars (The max length for a varchar).=====================================================================
ALTER PROC sprocCreateIndustryImageAssoc( @ImageList varchar(5000), @IndustryID int)ASBEGIN
Begin Tran
SET NOCOUNT ON IF @ImageList = '' BEGIN Delete From tblAFDImageIndustryAssoc where IndustryID=@IndustryID commit tran RETURN END
DECLARE @ImageID int, @Pos int
SET @ImageList = LTRIM(RTRIM(@ImageList))+ ',' SET @Pos = CHARINDEX(',', @ImageList, 1)
IF REPLACE(@ImageList, ',', '') <> '' BEGIN Delete From tblAFDImageIndustryAssoc where IndustryID=@IndustryID WHILE @Pos > 0 BEGIN SET @ImageID = LTRIM(RTRIM(LEFT(@ImageList, @Pos - 1))) IF @ImageID <> '' BEGIN INSERT INTO tblAFDImageIndustryAssoc (ImageID,IndustryID) VALUES (CAST(@ImageID AS int),@IndustryID) --conversion of ImageID to Int if @@Error > 0 begin rollback tran return -1 end END SET @ImageList = RIGHT(@ImageList, LEN(@ImageList) - @Pos) SET @Pos = CHARINDEX(',', @ImageList, 1)
END END
--SELECT ImageID,IndustryID FROM tblAFDImageIndustryAssoc
Commit Tran return 0END

6 Comments:

Blogger Lakshmi said...

Thanks a lot for all your valuable article! We are really happy about the your...
hadoop Training in Chennai

May 18, 2016 at 4:52 AM

 
Blogger jayashree said...

This idea is mind blowing. I think everyone should know such information like you have described on this post. Thank you for sharing this explanation.

Best Implant Clinic In Chennai | Best Laser Clinic In Chennai | Best Dental Clinic In Vellore

January 31, 2017 at 1:34 AM

 
Blogger Swethapriya said...



These information really worth saying, i think you are master of the content and thank you so much sharing that valuable information and get new skills after refer that post.



Best Dental Clinic in Velachery
| Best Dental Clinic in Anna Nagar |
Painless Dental Treatment In Chennai

February 2, 2017 at 5:28 AM

 
Blogger Sowmiya said...

I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I’ll be subscribing to your feed and I hope you post again soon.

Digital Marketing Company in Chennai

February 7, 2017 at 10:22 PM

 
Blogger Shalini said...

Superb. I really enjoyed very much with this article here. Really it is an amazing article I had ever read. I hope it will help a lot for all. Thank you so much for this amazing posts and please keep update like this excellent article.thank you for sharing such a great blog with us. expecting for your.
Digital Marketing Company in India
Seo Company in India

February 24, 2017 at 11:08 PM

 
Blogger Mahalya sree said...

I have definitely picked up anything new from right here. I did however expertise a few technical points using this site, since I experienced to reload the web site a lot of times previous to I could get it to load correctly.
Office Interiors in Chennai
Home Interior Decorators in Chennai

April 19, 2017 at 2:44 AM

 

Post a Comment

<< Home