如何在SQL Server中创建存储过程 [英] how to create store procedure in sql server

问题描述

如何在sql server的一个存储过程中创建ADD，UPDATE，DELETE存储过程?

how to create ADD,UPDATE,DELETE Store Procedure in One Store Procedure in sql server?

推荐答案

看看.我创建了一个样本.根据您的需要进行更改

Take a look at this. I have created a sample one. Change it based on your need

CREATE PROCEDURE Usp_SingleProcedureForAllOperations
(
	@EmpID INT,
	@EmpName VARCHAR(10),
	@Status CHAR(1),
	@Mode VARCHAR(10),
	@Result INT OUT
)
AS
	IF @Mode='INSERT'
		BEGIN

			INSERT INTO Emp(EmpID,EmpName,Status) VALUES(@EmpID,@EmpName,@Status)

			SET @Result=1
		END
	ELSE IF @Mode='UPDATE'
		BEGIN

			UPDATE Emp SET
			EmpName=@EmpName,
			Status=@Status
			WHERE
			EmpID=@EmpID

			SET @Result=1

		END
	ELSE IF @Mode='DELETE'
		BEGIN

			DELETE Emp WHERE EmpID=@EmpID
			--UPDATE Emp SET Status='N' WHERE EmpID=@EmpID --If you don't want to delete the record use this 

			SET @Result=1

		END
	RETURN @Result
	IF @@ERROR<>0
		SET @Result=@@ERROR
		GO

如果您拥有Visual Studio，则可以自动执行:

http://weblogs.asp. net/stevewellens/archive/2009/12/11/automatically-generate-stored-procedures-with-visual-studio.aspx [

If you have Visual Studio, you can have it do it automatically:

http://weblogs.asp.net/stevewellens/archive/2009/12/11/automatically-generate-stored-procedures-with-visual-studio.aspx[^]

请参见此处 [

这篇关于如何在SQL Server中创建存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！