` Printed Icetips Article

Icetips Article

SQL Related Articles: Passing array to a stored procedure
2002-10-01 -- Andy Stapleton
 
Newsgroups: TopSpeed.Products.Drivers

Send it as a single string...

Arraystring = '1,23,145,33,456'

File{Prop:Sql}='Call DBO.MyProc <39>'&ArraySTring&'<39>'

call the stored procedure, then inside the stored procedure using



Declare @CharCounter integer,
             @CurrCounter integer,
               @holdSTring varchar(25)

Create #ArrayTable(
ArraySTring     VArchar(25),
ArrayValue        integer);

SET @CharCounter = 1
While @CurrCounter < LEN(@ArrayString)
  BEGIN
     Set @CurrCounter = @currCounter + 1
     IF @CurrCounter > Len(@ArrayString)
          BREAK

     IF SUBSTRING(@ArraySTring, @CurrCounter,@CurrCounter) = ','
            BEGIN
                SET @HoldString =
Substring(@arraystring,@charcounter,@currcounter -1)
               Insert into #ArrayTable Values( @holdString,Null)
               SET @charCounter = @CurrCounter + 1
           END
  END

  Update #ArrayTable
   Set ArrayValue = StrToValue(ArraySTring)


/ now you have an table of the elements you sent to the procedure again to
work with....

this is off the top of my head , but should get you close....


--
Andy <> Stapleton
CIO DB|Wired.com / (ccs) Cowboy Computing Solutions
www.dbwired.com      Web and Clarionet hosting
www.ccscowboy.com CCS SQL templates
www.Paywire.com    ACH / Credit Card payment processing

"Cliff Campbell & Associates"  wrote in message
news:3d996ae6@news.softvelocity.com...
> Hi,
>
> I have a problem. I need to be able to pass in a list/array/table or
> anything else of integer items into an MS SQL Stored Proc for processing.
> However, I cannot find a data type in Transact-SQL to help me do this and
I
> do not want to have to put the CALL to the proc. into a loop.
> I fear it is not possible but I hope someone can help me out with an idea.
>
> Cheers,
>
> Iain
>
>

Printed November 21, 2024, 9:48 am
This article has been viewed/printed 35216 times.
Google search has resulted in 265 hits on this article since January 25, 2004.