Click here to Skip to main content
12,622,675 members (27,872 online)
Click here to Skip to main content
Articles » Database » Database » SQL Server » Downloads

Stats

87.9K views
449 downloads
20 bookmarked
Posted

Array Parameter Handling in a Stored Procedure

, 12 Jun 2008 CPOL
How to handle an array parameter in a Stored Procedure [SQL Server].
-- =============================================
-- To Split the string and returns a table
-- =============================================
CREATE  FUNCTION [dbo].[Split](@sText varchar(8000), @sDelim varchar(20) = '
')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx int,
@value varchar(8000),
@bcontinue bit,
@iStrike int,
@iDelimlength int

IF @sDelim = 'Space'
BEGIN
SET @sDelim = ' '
END

SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1

if(Len(@sText) = 0)
return

IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
BEGIN
WHILE @bcontinue = 1
BEGIN

--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.

IF CHARINDEX(@sDelim, @sText)>0
BEGIN
SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END

--Trim the element and its delimiter from the front of the string.
--Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
SET @idx = @idx + 1
SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))

END
ELSE
BEGIN
--If you can't find the delimiter in the text, @sText is the last value in
--@retArray.
SET @value = @sText
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
--Exit the WHILE loop.
SET @bcontinue = 0
END
END
END
ELSE
BEGIN
WHILE @bcontinue=1
BEGIN
--If the delimiter is an empty string, check for remaining text
--instead of a delimiter. Insert the first character into the
--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
IF DATALENGTH(@sText)>1
BEGIN
SET @value = SUBSTRING(@sText,1,1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
SET @idx = @idx+1
SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)

END
ELSE
BEGIN
--One character remains.
--Insert the character, and exit the WHILE loop.
INSERT @retArray (idx, value)
VALUES (@idx, @sText)
SET @bcontinue = 0
END
END

END

RETURN
END

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Srinath G Nath
Technical Lead Tata Consultancy Services
India India
Srinath has done B.Tech from AWH Engineering College ,Calicut , Kerala, India. Currently working in Tata Consultancy Services[Software MNC] as a Tech Lead @ Cochin, India. He loves to working with Microsoft Technology.

Language / Technology :C#,VB.NET, ASP.NET, AJAX, XML, ADO.Net , WinForms, Javascript, JQuery, PL/SQL, Web Services , SQL Server 2000 & 2005.


Application Server : IIS 5.1, IIS 6.


Certification : IBM Certified Solution Implementer - WebSphere DataPower and MCP Web Client Development


Srinath's Articles:
View My CodeProject Articles


Srinath's Site:
code4asp.net
www.talks4u.com



You may also be interested in...

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.161128.1 | Last Updated 12 Jun 2008
Article Copyright 2008 by Srinath G Nath
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid