Array data type in SQL server

_{Tuesday, March 25, 2014 10:28 AM}

Hi there,

I have stored procedure which does have array data type. recently we have migrated oracle to SQL server

but this array datatype doesn't support in SQL server.

How can we rewrite this stored procedure to make it work exactly same as Oracle SP.

Can you please help on this.

ALTER PROCEDURE [dbo].[New1] 
    @i_ID dbo.t_IdArray READONLY , 
    @i_ArraySize INT ,
    @SWP_Ret_Value FLOAT = NULL OUTPUT 
AS

Begin
 
      UPDATE    fmm.SITES
        SET     DISPLAY_ORDER = id.t_RowNum
        FROM    @i_ID id
        JOIN    dbo.SITE1 s
                on s.SITE_ID = id.t_ID
      
  end   

Thanks for your help.

_{Tuesday, March 25, 2014 11:01 AM ✅Answered}

TVP is equivalent to Array concept in SQL Server.

Before you go with the below procedure, you need to create type for Array ids as below:

CREATE TYPE t_IdArray  AS TABLE
(
ID INT
);

Now, you need to pass the values as TVP :

// Configure the SqlCommand and table-valued parameter.
 SqlCommand insertCommand = new SqlCommand(
   "New1", connection);
 insertCommand.CommandType = CommandType.StoredProcedure;
 SqlParameter tvpParam = 
    insertCommand.Parameters.AddWithValue(
    "@i_ID", dataReader);
 tvpParam.SqlDbType = SqlDbType.Structured;//you may add other parameters as well.

ALTER PROCEDURE [dbo].[New1] 
    @i_ID dbo.t_IdArray READONLY , 
    @i_ArraySize INT ,
    @SWP_Ret_Value FLOAT = NULL OUTPUT 
AS
Begin
 
      UPDATE    fmm.SITES
        SET     DISPLAY_ORDER = id.t_RowNum
        FROM    @i_ID id
        JOIN    dbo.SITE1 s
                on s.SITE_ID = id.t_ID
      
  end  

_{Tuesday, March 25, 2014 10:46 AM}

Siva,

We have table valued parameters!! That should do the required for yu!!

check:

http://blog.sqlauthority.com/2008/08/31/sql-server-table-valued-parameters-in-sql-server-2008/

http://msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx

http://technet.microsoft.com/en-us/library/bb510489.aspx

Also, check Erland's article on this topic:

http://www.sommarskog.se/arrays-in-sql-2008.html

Thanks,
Jay
<If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

_{Tuesday, March 25, 2014 12:10 PM}

Hi Latheesh,

I have tried your bit except configure the SQLCommand and TVP parameter I don't where I need to do this bit.

Without that bit I have tried your code it was throwing an error like this

Operand type clash: int is incompatible with t_IdArray

Can you please have a look .

Thanks,

RK











_{Tuesday, March 25, 2014 12:18 PM}

How can we rewrite this stored procedure to make it work exactly same as Oracle SP.

I don't think we can help you much without seeing the Oracle code or at least a description of the problem.  One typically uses a table-valued parameter in SQL Server to provide array-like functionality.  But I see you are passing an array size parameter, which doesn't really apply to a TVP, so I think that may be an artifact of the Oracle proc interface.  I don't see anywhere in the proc code where @SWP_Ret_Value is referenced either.

Note that the TVP value is passed from .NET application code as a DataTable, DataReader or IEnumerable<SqlDataRecord>.  The techniques will vary for other programming languages.

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

_{Tuesday, March 25, 2014 12:19 PM}

Hi Siva,

   Where are you calling this procedure from? Did you create the type as given? I assume your id is int datatype.

_{Tuesday, March 25, 2014 12:32 PM}

Hi Latheesh,

I have tried your bit except configure the SQLCommand and TVP parameter I don't where I need to do this bit.

Without that bit I have tried your code it was throwing an error like this

Operand type clash: int is incompatible with t_IdArray

Can you please have a look .

Thanks,

RK











can you show the definition for  dbo.t_IdArray

what does it contain?

Please Mark This As Answer if it helps to solve the issue Visakh http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs