Code -- ================================ -- Create User-defined Table Type -- ================================ USE Test GO -- Create the data type CREATE TYPE dbo.MyType AS TABLE ( col1 int NOT NULL, col2 varchar(20) NULL, col3 datetime NULL, PRIMARY KEY (col1) ) GO
這個是操作 表類型的腳本:
Code DECLARE @MyTable MyType INSERT INTO @MyTable(col1,col2,col3) VALUES (1,'abc','1/1/2000'), (2,'def','1/1/2001'), (3,'ghi','1/1/2002'), (4,'jkl','1/1/2003'), (5,'mno','1/1/2004') SELECT * FROM @MyTable |
下面演示如何將表參數作為一個存儲過程參數傳遞,以及ADO.NET的代碼
sql部分:
Code USE [Test] GO CREATE TABLE [dbo].[MyTable] ( [col1] [int] NOT NULL PRIMARY KEY, [col2] [varchar](20) NULL, [col3] [datetime] NULL, [UserID] [varchar] (20) NOT NULL ) GO CREATE PROC usp_AddRowsToMyTable @MyTableParam MyType READONLY, @UserID varchar(20) AS INSERT INTO MyTable([col1],[col2],[col3],[UserID]) SELECT [col1],[col2],[col3],@UserID FROM @MyTableParam GO |
如何在sql中調用此存儲過程:
Code DECLARE @MyTable MyType INSERT INTO @MyTable(col1,col2,col3) VALUES (1,'abc','1/1/2000'), (2,'def','1/1/2001'), (3,'ghi','1/1/2002'), (4,'jkl','1/1/2003'), (5,'mno','1/1/2004') EXEC usp_AddRowsToMyTable @MyTableParam = @MyTable, @UserID = 'Kathi' SELECT * FROM MyTable |
其中還涉及到一個權限問題,需要執行以下代碼:
Code GRANT EXECUTE ON TYPE::dbo.MyType TO TestUser; |
從.net app那調用此存儲過程:
Code 'Create a local table Dim table As New DataTable("temp") Dim col1 As New DataColumn("col1", System.Type.GetType("System.Int32")) Dim col2 As New DataColumn("col2", System.Type.GetType("System.String")) Dim col3 As New DataColumn("col3", System.Type.GetType("System.DateTime")) table.Columns.Add(col1) table.Columns.Add(col2) table.Columns.Add(col3) 'Populate the table For i As Integer = 20 To 30 Dim vals(2) As Object vals(0) = i vals(1) = Chr(i + 90) vals(2) = System.DateTime.Now table.Rows.Add(vals) Next |
Code 'Create a command object that calls the stored proc Dim command As New SqlCommand("usp_AddRowsToMyTable", conn) command.CommandType = CommandType.StoredProcedure 'Create a parameter using the new type Dim param As SqlParameter = command.Parameters.Add("@MyTableParam", SqlDbType.Structured) command.Parameters.AddWithValue("@UserID", "Kathi") |
Code 'Set the value of the parameter param.Value = table 'Execute the query command.ExecuteNonQuery() |
|