Implementation code for creating and modifying SQL Server stored procedures

This article mainly introduces the implementation code for creating and modifying SQLServer stored procedures. Friends in need can refer to the following

Open the management tool of SQL Server 2005, select the database where you need to create a stored procedure, and find the "Can "Programmability", expand it and you can see "Stored Procedures". Right-click it and select "New Stored Procedure". The editing window on the right opens, which contains the SQL Server statements automatically generated by Microsoft to create stored procedures.

After writing the name, parameters, and operation statements of the stored procedure, click Syntax Analysis. If there are no errors, just run "F5" directly. The stored procedure is created. The following is the code of a basic stored procedure. ：

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

Click to view the actual example

Now that you know how to create a stored procedure, is it still difficult to modify it? Obviously not. Modifying stored procedures is also quite easy. First, refresh the stored procedure list of the current database. Now you can see the name of the stored procedure you just created. Right-click it and select Modify. An editing window will open on the right, which contains the code for modifying the stored procedure. (Below)

 ALTER PROCEDURE [dbo].[Get_Data]  
  (  
    @Dealer_ID VARCHAR(<strong>50</strong>)  
   )  
   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(<strong>50</strong>),
    @Period VARCHAR(<strong>20</strong>)
   )  
   AS  
   SELECT * FROM myData WHERE Dealer_ID = @Dealer_ID AND Period = @Period

F5 is executed successfully, modify the stored procedure Finish.

I started writing a stored procedure to implement the data insertion operation. To put it bluntly, it means adding data. The code of this stored procedure is as follows:

 CREATE PROCEDURE PROC_INSERT_DATA_ID
  @DealerID varchar(<strong>50</strong>)
 AS
 BEGIN
 5     DECLARE @COUNT INT  
     SET @COUNT = (SELECT COUNT(*) FROM myDATA_Details WHERE DealerID = @DealerID)
     IF (@COUNT><strong>0</strong>)  
       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
 --实际例子：http://hovertree.com/hovertreescj/sql/p_hovertreescj_urls_add.htm

F5, the creation is successful, call it to insert data, OK, no problem, the insertion is successful, and the expected purpose is achieved

is used to update data, the code is as follows:

CREATE PROCEDURE PROC_INSERT_DATA_DETAIL
     @DealerID varchar(<strong>50</strong>), 
     @FieldName varchar(<strong>2000</strong>),
     @FieldValue varchar(<strong>2000</strong>)
   AS
   BEGIN
     DECLARE @Count INT
     SET @Count = (SELECT COUNT(*) FROM myDATA_Details WHERE DealerID = @DealerID)
     IF (@COUNT><strong>0</strong>)
       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(<strong>50</strong>), 
     @FieldName varchar(<strong>2000</strong>),
     @FieldValue varchar(<strong>2000</strong>)
   AS
   BEGIN
     DECLARE @Count INT
     DECLARE @StrSQL VARCHAR(<strong>2000</strong>)
     SET @Count = (SELECT COUNT(*) FROM myDATA_Details WHERE DealerID = @DealerID)
     IF (@COUNT><strong>0</strong>)
       BEGIN
         SET @StrSQL = &#39;UPDATE myDATA_Details SET &#39;+ @FieldName + &#39; = &#39;&#39;&#39; +@FieldValue + &#39;&#39;&#39; WHERE DealerID = &#39;+ @DealerID
         EXEC(@StrSQL)
       END
     ELSE
       BEGIN
         INSERT INTO myDATA_Details (DealerID) VALUES (@DealerID)
         SET @StrSQL = &#39;UPDATE myDATA_Details SET &#39;+ @FieldName + &#39; = &#39;&#39;&#39; +@FieldValue + &#39;&#39;&#39; WHERE DealerID = &#39;+ @DealerID
         EXEC(@StrSQL)
       END
   END

The above is the detailed content of Implementation code for creating and modifying SQL Server stored procedures. For more information, please follow other related articles on the PHP Chinese website!