procedure

Question

procedure

dastagirid

SSCommitted

Points: 1810
More actions
January 30, 2009 at 8:56 pm

#128290

hi,
i used .net c# .here i created one array. that array have two values ..
how can i create array parameter in sql procedure
regards:
Dastagiri.D
Thanks
Dastagiri.D

Viewing 12 posts - 1 through 11 (of 11 total)

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

alim.shaikh Old Hand Points: 330 More actions · Answer 1

unfortunately, sql server doesn't provide array since it is not a full fledge programming language. although the problem can be solved using concatenated string with some specific delimiter. for e.g. if you want to pass two values like ID1 and ID2 then you can use comma(,) to separate these two IDs. that means your string wud be like ID1,ID2 then in stored procedure you can parse these string by finding comma(,).

if you need sample then please let me know.

dastagirid SSCommitted Points: 1810 More actions · Answer 2

thanks..

give me one souce example

regards:

Dastagiri.D

Thanks
Dastagiri.D

alim.shaikh Old Hand Points: 330 More actions · Answer 3

Thanks for your interest,

Following is the example. hope it will help you for your problem.

Create Function [dbo].[fn_ParseDelimitedStrings]

(@String nvarchar(3500), @Delimiter char(1))

Returns @Values Table

(

RowId int Not Null Identity(1,1) Primary Key

,Value nvarchar(255) Not Null

)

As

Begin

Declare @startPos smallint

,@endPos smallint

If (Right(@String, 1) != @Delimiter)

Set @String = @String + @Delimiter

Set @startPos = 1

Set @endPos = CharIndex(@Delimiter, @String)

While @endPos > 0

Begin

Insert @Values(Value)

Select LTrim(RTrim(SubString(@String, @startPos, @endPos - @startPos)))

-- remove the delimiter just used

Set @String = Stuff(@String, @endPos, 1, '')

-- move string pointer to next delimiter

Set @startPos = @endPos

Set @endPos = CharIndex(@Delimiter, @String)

End

Return

End

Thanks

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

You can use a While loop if you want... but I wouldn't... 😉 See the following...

http://www.sqlservercentral.com/articles/T-SQL/63003/

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

dastagirid (1/30/2009)
i used .net c# .here i created one array. that array have two values ..

There are lots of ways to do it, but for two values, they aren't worth it. Just pass two parameters.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

RBarryYoung (1/31/2009)
dastagirid (1/30/2009)
i used .net c# .here i created one array. that array have two values ..
There are lots of ways to do it, but for two values, they aren't worth it. Just pass two parameters.

Heh... in the land of computers, I forgot to use the pencil. 😛

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

alim.shaikh (1/31/2009)
unfortunately, sql server doesn't provide array since it is not a full fledge programming language...

This is incorrect on two counts. first, SQL is a "full-fledged" programming language. Really. Perhaps you meant to say that SQL is not a "general Purpose" programming language, which is true. SQL is a full-fledged special-purpose programming language.

Secondly, SQL does have a data structure that is more than the functional equivalent of arrays. They are called "Tables" and they are the preferred way of storing and manipulating array-like data in SQL. You should only use other means to emulate arrays when a table will not work (usually for interfacing reasons).

And actually, now there is a second data structure that is the functional equivalent of arrays and then some: XML. And there are at least six (6) other data types that can emulate them with varying degrees of difficulty. But I would say that tables are still preferred.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

RBarryYoung (1/31/2009)
alim.shaikh (1/31/2009)
unfortunately, sql server doesn't provide array since it is not a full fledge programming language...
This is incorrect on two counts. first, SQL is a "full-fledged" programming language. Really. Perhaps you meant to say that SQL is not a "general Purpose" programming language, which is true. SQL is a full-fledged special-purpose programming language.
Secondly, SQL does have a data structure that is more than the functional equivalent of arrays. They are called "Tables" and they are the preferred way of storing and manipulating array-like data in SQL. You should only use other means to emulate arrays when a table will not work (usually for interfacing reasons).
And actually, now there is a second data structure that is the functional equivalent of arrays and then some: XML. And there are at least six (6) other data types that can emulate them with varying degrees of difficulty. But I would say that tables are still preferred.

"The Force is strong in this one."

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

Heh. "The ability to destroy a planet is insignificant next to the power of the force."

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

Perfect. 🙂 Miller time? :w00t:

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

Jeff Moden (1/31/2009)
Perfect. 🙂 Miller time? :w00t:

Really? I haven't seen Matt here all weekend...

😛

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]