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
by jmaynard
by jmaynard
This discussion is closed to new replies.
Trending Desktop posts
Sort by Trending