Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use DataTable as a parameter of the stored procedure, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Because there is no built-in function similar to split in SQL Server 2000, it is troublesome to split a column in the foreground dataset into a List with a comma, and then convert it into a string and pass it to the stored procedure. The following editor explains how to use DataTable as a parameter of a stored procedure?

How to use DataTable as a parameter to a stored procedure

I. Test environment

1 、 Windows Server 2008 R2 DataCenter

2 、 Visual Studio 2008 Team System With SP1

3 、 SQL Server 2008 Enterprise Edition With SP1

Because it is a new feature of SQL Server 2008, only 2008 can be used.

II. Overview of testing

The test project is as simple as adding new users.

3. Prepare data

1. Create databases, tables, types, and stored procedures

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID ('Users') AND OBJECTPROPERTY (id) UserID INT IDENTITY IsUserTable') = 1) BEGIN CREATE TABLE dbo.Users (UserID INT IDENTITY (- 1,-1) NOT NULL UserName VARCHAR (20) NOT NULL, UserPass VARCHAR (20) NOT NULL, Sex BIT NULL, Age SMALLINT NULL, CONSTRAINT PK_Users_UserID PRIMARY KEY (UserID) END IF NOT EXISTS (SELECT * FROM sys.table_types WHERE name = 'UserTable' AND is_user_defined = 1) BEGIN CREATE TYPE UserTable AS TABLE (UserName VARCHAR (20) NOT NULL, UserPass VARCHAR (20) NOT NULL, Sex BIT NULL) Age SMALLINT NULL) END GO

How to use DataTable as a parameter to a stored procedure

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID ('sp_InsertSingleUser') AND OBJECTPROPERTY (id) NissIsProcedure') 1) BEGIN DROP PROCEDURE dbo.sp_InsertSingleUser END GO CREATE PROCEDURE dbo.sp_InsertSingleUser (@ User UserTable READONLY) AS SET XACT_ABORT ON BEGIN TRANSACTION INSERT INTO dbo.Users (UserName, UserPass, Sex, Age) SELECT UserName, UserPass, Sex Age FROM @ User COMMIT TRANSACTION SET XACT_ABORT OFF GO

The foreground sets up the form, and the background is mainly a function:

Public void fnInsertSingleUser (DataTable v_dt) {try {SqlConnection cn = new SqlConnection (CONN); SqlCommand cmd = cn.CreateCommand (); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = @ "sp_InsertSingleUser"; SqlParameter p = cmd.Parameters.AddWithValue ("@ User", v_dt); DataSet ds = new DataSet (); SqlDataAdapter da = new SqlDataAdapter (cmd); da.Fill (ds) } catch (Exception ex) {throw ex;}}

The stored procedure is called when the add button is clicked. The test is completed, and it is very simple. It is really convenient to pass a DataTable as a parameter, and you can easily complete a lot of coding work. There are some tricks about table variables, such as statements that determine whether they exist when they are created, stored procedures that reference table variables need to be deleted before deleting table variables, and so on. In general, I will mostly choose to use temporary tables, which is easy to deal with. Table variables can be used as stored procedure parameters is indeed a unique advantage. I hope to continue to enhance the support for table variables and temporary tables in the future version of SQL Server, especially to support temporary table debugging as soon as possible.

The above is all the contents of the article "how to use DataTable as a parameter of a stored procedure". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.