如何使用参数调用SQL Server存储过程?

编程入门行业动态更新时间:2024-10-10 13:23:15

本文介绍了如何使用参数调用SQL Server存储过程?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述

您将如何在asp C#应用程序中将应用程序变量集作为SQL Server存储过程参数发送? 谢谢！

How would you send an application variable set in an asp C# application as a SQL Server stored procedure parameter? Thank you!

推荐答案

是的，您应该并且应该通过SqlParameter传递参数.您可以在此处查看C#教程/示例: www.java2s/Tutorial /CSharp/0560__ADO.Net/CallstoredprocedurewithparametersusingSqlCommand.htm [ ^ ]. 希望对您有所帮助！最好的问候， Manfred Yes you can and you should pass parameters via SqlParameter. You can look at a C# tutorial/example here: www.java2s/Tutorial/CSharp/0560__ADO.Net/CallstoredprocedurewithparametersusingSqlCommand.htm[^]. Hope that helps! Best Regards, Manfred

Manfred也给了您一个很好的链接:) 这是一个用c#调用下面定义的存储过程的示例. 该代码说明了存储过程中的输入和输出变量: Manfred gave you a good link too :) Here is an example in c# calling the stored procedure defined below. The code illustrates both input and output variables from the stored procedure: public static bool PostalAreaInsert( SqlConnection sqlConnection, ref Guid oid, ref DateTime? registeredTime, ref Guid? registeredBy, Guid? region, string name, DateTime? fromTime, DateTime? throughTime) { bool result = false; SqlCommand sqlCommand = sqlConnection.CreateCommand( ); using( sqlCommand ) { sqlCommand.CommandText = "PostalAreaInsert"; sqlCommand.CommandType = CommandType.StoredProcedure; SqlParameter oidParameter = sqlCommand.Parameters.Add(new SqlParameter("@Oid", SqlDbType.UniqueIdentifier)); oidParameter.Direction = ParameterDirection.InputOutput; oidParameter.Value = oid; SqlParameter registeredTimeParameter = sqlCommand.Parameters.Add(new SqlParameter ("@RegisteredTime", SqlDbType.DateTime)); registeredTimeParameter.Direction = ParameterDirection.InputOutput; if( registeredTime.HasValue ) { registeredTimeParameter.Value = registeredTime.Value; } else { registeredTimeParameter.Value = DBNull.Value; } SqlParameter registeredByParameter = sqlCommand.Parameters.Add( new SqlParameter("@RegisteredBy", SqlDbType.UniqueIdentifier)); registeredByParameter.Direction = ParameterDirection.InputOutput; if( ( registeredBy.HasValue ) && ( registeredBy.Value != Guid.Empty ) ) { registeredByParameter.Value = registeredBy.Value; } else { registeredByParameter.Value = DBNull.Value; } SqlParameter objectTypeParameter = sqlCommand.Parameters.Add( new SqlParameter("@ObjectType", SqlDbType.Int)); objectTypeParameter.Direction = ParameterDirection.InputOutput; objectTypeParameter.Value = 2; SqlParameter regionParameter = sqlCommand.Parameters.Add( new SqlParameter("@Region", SqlDbType.UniqueIdentifier)); if( ( region.HasValue ) && ( region.Value != Guid.Empty ) ) { regionParameter.Value = region.Value; } else { regionParameter.Value = DBNull.Value; } SqlParameter nameParameter = sqlCommand.Parameters.Add( new SqlParameter("@Name", SqlDbType.NVarChar, 255)); if(( name != null ) &&( name.Length > 0 )) { nameParameter.Value = name; } else { nameParameter.Value = DBNull.Value; } SqlParameter fromTimeParameter = sqlCommand.Parameters.Add( new SqlParameter("@FromTime", SqlDbType.DateTime)); if( fromTime.HasValue ) { fromTimeParameter.Value = fromTime.Value; } else { fromTimeParameter.Value = DBNull.Value; } SqlParameter throughTimeParameter = sqlCommand.Parameters.Add( new SqlParameter("@ThroughTime", SqlDbType.DateTime)); if( throughTime.HasValue ) { throughTimeParameter.Value = throughTime.Value; } else { throughTimeParameter.Value = DBNull.Value; } int rowsAffected = sqlCommand.ExecuteNonQuery(); if(rowsAffected == 1) { Guid newOid = (Guid)oidParameter.Value; DateTime regTime = Convert.ToDateTime(registeredTimeParameter.Value); Guid regByOid = (Guid)registeredByParameter.Value; oid = newOid; registeredTime = regTime; registeredBy = regByOid; result = true; } } return result; }

存储过程-请注意事务与保存点的处理；它确保该过程仅回滚其自身的修改-如果该过程也可以从另一个存储过程中调用，则很有用:

The stored procedure - please note the handling of transactions vs savepoints; it ensures that the procedure will only rollback its own modifications - something that''s useful if the procedure can be called from another stored procedure too:

CREATE PROCEDURE [PostalAreaInsert] @Oid uniqueidentifier OUTPUT, @RegisteredTime DateTime OUTPUT, @RegisteredBy uniqueidentifier OUTPUT, @ObjectType int OUTPUT, @Region uniqueidentifier, @Name nvarchar(255), @FromTime DateTime, @ThroughTime DateTime AS BEGIN IF @Oid IS NULL BEGIN SET @Oid = NEWID() END IF @ObjectType IS NULL BEGIN SET @ObjectType = 2 END IF @RegisteredBy IS NULL BEGIN SET @RegisteredBy = (SELECT Oid From SecurityLogin WHERE [Identity] = SYSTEM_USER) END IF @RegisteredTime IS NULL BEGIN SET @RegisteredTime = SYSDATETIME() END DECLARE @TranCounter INT; SET @TranCounter = @@TRANCOUNT; IF @TranCounter > 0 SAVE TRANSACTION SavePoint2; ELSE BEGIN TRANSACTION; BEGIN TRY INSERT INTO Element(Oid,OptimisticLockField,RegisteredBy,RegisteredTime,ObjectType) VALUES(@Oid,0,@RegisteredBy,@RegisteredTime,@ObjectType); INSERT INTO [PostalArea](Oid, [Region], [Name], [FromTime], [ThroughTime]) VALUES(@Oid, @Region, @Name, @FromTime, @ThroughTime); IF @TranCounter = 0 COMMIT TRANSACTION; END TRY BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); IF @TranCounter = 0 ROLLBACK TRANSACTION; ELSE IF XACT_STATE() <> -1 ROLLBACK TRANSACTION SavePoint2; RAISERROR( @ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH END GO