Discussion Locked

Stored Procedures in SQL 7.0

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I need to know if there is a way to assign a variable array in a stored procedure. I know how to declare variables but need to set up an array. Any idea?

3 replies

by chrandrob

T-SQL does not support arrays. You can, however, fake it. The following code accepts an input parameter of comma delimited values (the pretend array) and parses them into a temp table with an identity field that gives each one a key value to go by:
CREATE PROCEDURE [dbo].[sp_FakeArray]
@IDSubF int,
@listingIDs varchar(500)
AS

--Parse the ListingIDs
Declare @bLoop bit,
@iStart int,
@iLength int,
@iID varchar(255),
@count int
--Init vars
SET @bLoop = 1
SET @iStart = 1
SET @iLength = 0
Set @count = 1
--Store the ListingIDs into a temp table
Create Table #tmpListingID
(
ID int identity (1,1),
ListingID int
)
While @bLoop = 1
Begin
SET @count = @count + 1
--Get the lengthof characters to get back for the substring function
SET @iLength = CHARINDEX(',',@ListingIDs, @iStart) - @iStart
--If the length is 0 or less then fix it.
IF @iLength <= 0
SET @iLength = (LEN(@ListingIDs) - (@iStart-1))
--Convert the string to an int
-- Start , Length
SET @iID = rtrim(ltrim(substring(@ListingIDs,@iStart,@iLength )))
--Insert the newly parsed id into the temp table
Insert INTO #tmpListingID (ListingID)
Values(Cast(@iID As int))
SET @iStart = CHARINDEX(',',@ListingIDs , @iStart +1 ) +1
IF @iStart = 1
BEGIN
SET @bLoop = 0
END
End

This discussion is closed to new replies.