How to create stored procedures in SQLServer

This article introduces how to create stored procedures in SQLServer, the content is very detailed, interested friends can refer to, I hope it can be helpful to you.

Open the management tools of SQL Server 2005, select the database where you need to create stored procedures, find programmability, and expand to see stored procedures. Right-click it, select "New stored procedure", and the editing window on the right opens with Microsoft's automatically generated SQL Server statement to create the stored procedure.

After writing the name, parameters and operation statements of the stored procedure, click on the syntax analysis, and run "F5" directly without errors. After the stored procedure has been created, the following is the code of a basic stored procedure:

CREATE PROCEDURE Get_Data (@ Dealer_ID VARCHAR (50)) AS SELECT * FROM myData WHERE Dealer_ID = @ Dealer_ID

Click to view actual examples

Now that you know how to create a stored procedure, will it be difficult to modify it? Apparently not. It is also quite easy to modify stored procedures. First of all, refresh the list of stored procedures in the current database, and then you can see the name of the stored procedure you just created. Right-click it, select modify, and open an editing window on the right. It contains the code to modify the stored procedure (see below).

ALTER PROCEDURE [dbo]. [Get_Data] (@ Dealer_ID VARCHAR (50)) AS SELECT * FROM myData WHERE Dealer_ID = @ Dealer_ID

Simply modify it, the code is as follows

ALTER PROCEDURE [dbo]. [Get_Data] (@ Dealer_ID VARCHAR (50), @ Period VARCHAR (20)) AS SELECT * FROM myData WHERE Dealer_ID = @ Dealer_ID AND Period = @ Period

F5 was executed successfully, and the modified stored procedure was completed.

Began to write a stored procedure to implement the data insertion operation, to put it bluntly, to add data. The code for this stored procedure is as follows:

CREATE PROCEDURE PROC_INSERT_DATA_ID @ DealerID varchar (50) AS BEGIN 5 DECLARE @ COUNT INT SET @ COUNT = (SELECT COUNT (*) FROM myDATA_Details WHERE DealerID = @ DealerID) IF (@ COUNT > 0) BEGIN DELETE FROM myDATA_Details WHERE DealerID = @ DealerID INSERT INTO myDATA_Details (DealerID) VALUES (@ DealerID) END ELSE BEGIN INSERT INTO myDATA_Details (DealerID) VALUES (@ DealerID) END END-practical example: http://hovertree .com / hovertreescj/sql/p_hovertreescj_urls_add.htm

F5, create successfully, call it to insert data, OK, no problem insert successfully, achieve the desired goal

To update the data, the code is as follows:

CREATE PROCEDURE PROC_INSERT_DATA_DETAIL @ DealerID varchar (50), @ FieldName varchar (2000), @ FieldValue varchar (2000) AS BEGIN DECLARE @ Count INT SET @ Count = (SELECT COUNT (*) FROM myDATA_Details WHERE DealerID = @ DealerID) IF (@ COUNT > 0) BEGIN UPDATE myDATA_Details SET DealValue = @ FieldValue WHERE DealerID = @ DealerID END ELSE BEGIN INSERT INTO myDATA_Details (DealerID) VALUES (@ DealerID) END END

Or:

Modified code

ALTER PROCEDURE PROC_INSERT_DATA_DETAIL @ DealerID varchar (50), @ FieldName varchar (2000) @ FieldValue varchar (2000) AS BEGIN DECLARE @ Count INT DECLARE @ StrSQL VARCHAR (2000) SET @ Count = (SELECT COUNT (*) FROM myDATA_Details WHERE DealerID = @ DealerID) IF (@ COUNT > 0) BEGIN SET @ StrSQL = 'UPDATE myDATA_Details SET' + @ FieldName +'=''+ @ FieldValue +''WHERE DealerID =' + @ DealerID EXEC (@ StrSQL) END ELSE BEGIN INSERT INTO myDATA_Details (DealerID) VALUES (@ DealerID) SET @ StrSQL = 'UPDATE myDATA_Details SET' + @ FieldName +'=''+ @ FieldValue +''WHERE DealerID =' + @ DealerID EXEC (@ StrSQL) END END about how to create stored procedures in SQLServer I hope the above content can be of some help to you and learn more knowledge. If you think the article is good, you can share it for more people to see.