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)
--SELECT ImageID,IndustryID FROM tblAFDImageIndustryAssoc
Commit Tran return 0END

Friday, August 05, 2005


Want to know how Gmail works - and whether it will work?

Google's storage strategy