Passing in an array as Parameter to a Stored Procedure

Question

Post reply

Passing in an array as Parameter to a Stored Procedure

swathi-324373

SSC Enthusiast

Points: 107
More actions
June 1, 2006 at 3:12 pm

#168666

Can any one help me with a sample code, which can take an array of
elements as one of it's parameters and get the value inserted into a table in a
stored procedure.

Thanks in advance
vnswathi.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

brij_rauthan SSCommitted Points: 1521 More actions · Answer 1

Swathi, unfortunately you cannot pass array in SQL, however you can pass comma separated lists and then parse it.

There are many posts where you can search for those codes, to cut the time... here is one example...

CREATE Procedure dbo.SAMPLEPROC

(

@RN varchar(15),

@an Varchar(25),

@LIST Varchar(4000),

)

AS

BEGIN

Declare @ID int, @Pos int, @phrase Varchar(4000)

-- Insert the maker details

Insert into TABLE1 Values (@RN, @an)

-- Get the Id from TABLE1

Select @ID = SCOPE_IDENTITY( )

-- Insert the retailers list into MAkerBlockRetailer

Set @phrase = Replace(@LIST,' ' ,'')

While Len(@phrase) >0

Begin

Set @pos = CHARINDEX (',',@Phrase)

if @pos > 0

Begin

Insert into TABLE2 Select @ID, Left(@Phrase,@pos-1)

Set @Phrase = Right(@Phrase, Len(@phrase)-@pos)

end

else

Begin

Insert into TABLE2 Select @ID, @Phrase

Set @phrase=''

end

End

END

Brij

swathi-324373 SSC Enthusiast Points: 107 More actions · Answer 2

Thank you i appreciate your reply.

i will try it out and let you know if i found the solution.

Thanks,

Swathi.

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 3

This usually works... I'm sure this will be followed by warnings of injection attacks...

CREATE PROCEDURE dbo.SampleProc

@CSVParams VARCHAR(8000) -- List of params with comma delimiters

AS

--===== Declare local variables

DECLARE @sql VARCHAR(8000) --Holds dynamic SQL

--===== Create the dynamic SQL

SET @sql = '

SELECT somecolumns

FROM sometable

WHERE comecolumn IN (' + @params + ')'

--===== Execute the dynamic SQL

EXEC @sql

GO

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

swathi-324373 SSC Enthusiast Points: 107 More actions · Answer 4

Thank you all, i really appreciate your help.

Thanks,

Swapna