Declaring a variable that takes more than one values

  • Is there of way of declaring variable that take one or more than one values based on the users's need in a store procedure

    I have this small code below which is not working but, just trying to give more clue of what I want.

    Declare @Account_N0 varchar (20)

    SET @Account_N0 in ('031-530-9','031-530-10','031-530-2')

  • What I understood you need conceptually is a dynamic array of varchar(20) as a input parameter of a stored procedure. You can achieve this in SQL Server 2008 using the new Table Type feature.

    Steps

    -- Create a User Defined Table Type

    -- Declare Input parameter of the Procedure as TableType

    Following code sample will give you some idea about the implementation

    -- Creating Table type

    CREATE TYPE [dbo].[TestType] AS TABLE(

    [col1] [varchar](20) NULL

    )

    -- Use the TableType as Input Parameter of Procedure

    Create procedure [dbo].[TestProc]

    (

    @InpTable TestType READONLY

    )

    You can use the input parameter @InpTable similar to a Table Variable for data fetching.. Only limitation is any DML operation on the Input parameter ( @InpTable) within the procedure is restricted. Calling Application / Procedure should populate the table @InpTable

  • Thanks for the respond. This is too much for me to understand. I still new in SQL.

    When I try to create the table it complained but did managed to fix it. Now is the store proc

    is complains about something near READONLY

    see below

    "Msg 102, Level 15, State 1, Procedure TestProc, Line 3

    Incorrect syntax near 'READONLY'."

    /*

    What I understood you need conceptually is a dynamic array of varchar(20) as a input parameter of a stored procedure. You can achieve this in SQL Server 2008 using the new Table Type feature.

    Steps

    -- Create a User Defined Table Type

    -- Declare Input parameter of the Procedure as TableType

    Following code sample will give you some idea about the implementation

    */

    -- Creating Table type

    CREATE table [dbo].#TestType (

    [col1] [varchar](20) NULL

    )

    -- Use the TableType as Input Parameter of Procedure

    Create procedure [dbo].[TestProc]

    (

    @InpTable #TestType READONLY

    )

  • There are several ways to accomplish what you need.

    One is a table variable input.

    Another is to parse a string into a table.

    Another is to use the string to build "dynamic SQL".

    Another would be to use an XML parameter.

    Another would be to convert the date column to a string and use the Like operator to find if it matches part of the string-parameter.

    Of these, I most recommend either learning how to use a table parameter, or to parse the string into a table. In either case, you can join to it.

    What are you most comfortable with? Details can be given for any/all of these.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Send parameter '031-530-9,031-530-10,031-530-2'

    Create a stored procedure to split values by comma

    for example:

    SET @Account_N0 = '031-530-9,031-530-10,031-530-2'

    Select * from account where Account_No in (Select data from dbo.Split(@Account_No,',')

    ----- Split function code is

    CREATE FUNCTION dbo.Split

    (

    @RowData nvarchar(2000),

    @SplitOn nvarchar(5)

    )

    RETURNS @RtnValue table

    (

    Id int identity(1,1),

    Data nvarchar(100)

    )

    AS

    BEGIN

    Declare @Cnt int

    Set @Cnt = 1

    While (Charindex(@SplitOn,@RowData)>0)

    Begin

    Insert Into @RtnValue (data)

    Select

    Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

    Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

    Set @Cnt = @Cnt + 1

    End

    Insert Into @RtnValue (data)

    Select Data = ltrim(rtrim(@RowData))

    Return

    END

  • You dont need to write a function to split string values into rows, there is a row constructor available in SQL Server 2008.

    Please see below link for a sample:

    http://mssqlsolutions.blogspot.com/2010/10/enhanced-values-clause-sql-server-2008.html

    Tariq
    master your setup, master yourself.
    http://mssqlsolutions.blogspot.com

  • How about something like this...

    Its pretty straight forward and wouldn't require any extra functions.

    (However, there are perks to the function spiting apart a string)

    --Holds the Account Numbers

    DECLARE @account AS TABLE

    (

    Number VarChar(20)

    )

    --Insert the Account number

    INSERT INTO

    @account(Number)

    SELECT

    Number

    FROM

    (

    VALUES

    ('031-530-9'),

    ('031-530-10'),

    ('031-530-2')

    ) AS AccountNumbers(Number)

    SELECT * FROM @account

Viewing 7 posts - 1 through 6 (of 6 total)

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