serving the solutions day and night

Pages

Saturday, December 17, 2011

Table-valued parameters

Table-valued parameters - allows you to use multiple rows of data in T-SQL statments or send a table as a parameter to functions and stored procedures. It benefits such as flexibility, better performance than other methods of passing list of parameters adn reduce round trips to the server.The user-defined table type used for the table-valued parameters.
(Programmability->Types->User-Defined Table Types->dbo.StocksType)

DROP TABLE Stocks

CREATE TABLE Stocks(StocksName varchar(100), Qty int, Price dec(10,2))

CREATE TYPE StocksType AS TABLE(StocksName varchar(100), Qty int, Price dec(10,2))


CREATE PROCEDURE uspStocks
@tvp StocksType READONLY
AS
BEGIN
SET NOCOUNT ON
INSERT INTO Stocks(StocksName, QTY, Price)
SELECT StocksName, QTY, Price FROM @tvp
END;
GO

DECLARE @v as StocksType
INSERT INTO @v(StocksName, QTY, Price) VALUES('MSFT',100, 36.67), ('SUN',100, 26.67)

EXEC uspStocks @v;
GO

SELECT * FROM Stocks


DataTable dt = new DataTable("Stocks");

dt.Columns.Add("StocksName", typeof(string));
dt.Columns.Add("Qty", typeof(int));
dt.Columns.Add("Price", typeof(decimal));

dt.Rows.Add("GOO", 100, 45.78);
dt.Rows.Add("APP", 50, 35.78);

SqlConnection conn= new SqlConnection("Data Source=local,1433;Initial Catalog=master;Integrated Security=True");
conn.Open();
SqlCommand cmd = new SqlCommand("uspStocks", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("tvp", SqlDbType.Structured);
param.Value = dt;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
conn.Close();

https://msdn.microsoft.com/en-us/library/bb675163%28v=vs.110%29.aspx

No comments: