Using table-valued parameters in SQL Server

private static void BuildCollection(IEnumerable inputCollection, IList outputCollection, SqlMetaData[] metaData)
        {
            foreach (var i in inputCollection)
            {
                var rec = new SqlDataRecord(metaData);
                rec.SetValue(0, i);
                outputCollection.Add(rec);
            }
        }

private static void PrepareDataRecordParameter(SqlCommand command, string paramName, IEnumerable records, string typeName)
        {
            if (records.Any())
            {
                var listParam = new SqlParameter(paramName, SqlDbType.Structured);
                listParam.Direction = ParameterDirection.Input;
                listParam.TypeName = typeName;
                listParam.Value = records;

                command.Parameters.Add(listParam);
            }
        }

Use these methods as follow

IEnumerable orders = new List {1, 200, 234, 23}

IList drOrders = new List();

SqlMetaData[] IntegerTable =
{
    new SqlMetaData("Id", SqlDbType.Int)
};

BuildCollection(orders, drOrders , intIdTable);

var command = new SqlCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "dbo.CheckOrders";
 
PrepareDataRecordParameter(command, "@orders", drOrders, "dbo.IntIdTable");


Stored procedure looks like this:

CREATE PROCEDURE dbo.CheckOrders(
@orders [dbo].[IntegerTable] readonly
)
AS
...

Advertisements

Paging in stored procedures

Pass two additional parameters to you stored procedure and perform where condition like this

WHERE rowNumber BETWEEN @skip + 1 AND @skip + @take

Example

@take int = 20
@skip int = 0

;WITH orders AS (
SELECT 
   OrderId,
   InvoiceNumber
    ROW_NUMBER() OVER (ORDER BY OrderId) AS rowNumber
FROM dbo.Orders
)

SELECT 
OrderId,
InvoiceNumber,
(SELECT COUNT(*) FROM orders) as TotalRows
FROM orders
WHERE rowNumber BETWEEN @skip + 1 AND @skip + @take