procedure

  • 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

  • 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.

  • thanks..

    give me one souce example

    regards:

    Dastagiri.D

    Thanks
    Dastagiri.D

  • 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

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

  • 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]

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

  • 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]

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

  • 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]

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

  • 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]

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

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