SQL Server Stored Procedure creation and modification implementation code, SQL Server Stored Procedure

SQL Server Stored Procedure creation and modification implementation code, SQL Server Stored Procedure

Open the management tool of SQL Server 2005, select the database for which you want to create the stored procedure, find "programmable", and then you can see the "stored procedure ". Right-click it and select "Create a stored procedure". The editing window on the right opens, containing the statements automatically generated by Microsoft for SQL Server to create a stored procedure.

After writing the name, parameters, and operation statement of the stored procedure, click syntax analysis. If there is no error, Run "F5" directly. After the stored procedure is created, the following is the code for 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

Since the stored procedure has already been created, will it be difficult to modify it? Obviously not. It is also quite easy to modify the stored procedure. First, refresh the stored procedure list of the current database. Then you can see the name of the stored procedure you just created, right-click it, select modify, and open an edit window on the right, the Stored Procedure Code is modified (as follows)

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

Modify the Code 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 successfully executed and the stored procedure is modified.

I started to write a stored procedure to insert data. To put it bluntly, I add data. The code for 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 -- Example: http://hovertree.com/hovertreescj/ SQL /p_hovertreescj_urls_add.htm

F5: It is created successfully. Call it to insert data. OK: No problem. The data is inserted successfully, which achieves the expected purpose.

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 = '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

The above is the SQL Server Stored Procedure creation and modification Implementation Code introduced by xiaobian. I hope it will be helpful to you. If you have any questions, please leave a message and I will reply to you in time!