Updating sql proceedure

Rated 3.81/5 based on 826 customer reviews

The Database Task Activity holds a cache of metadata for stored procedures for performance reasons.It checks for any changes in the database activity that uses the stored procedure (for example, changes in the number of input and output parameters, type changes) before using the one from cache.An SQL procedure has a procedure body, which contains the source statements for the stored procedure.The following example shows a CREATE PROCEDURE statement for a simple stored procedure.To test this stored procedure, first create the Employee table with the required attributes.

Syntax: CREATE PROCEDURE UPDATE_SALARY_1 (1) (IN EMPLOYEE_NUMBER integer, (2) IN RATE INTEGER) (2) LANGUAGE SQL (3) BEGIN UPDATE EMPLOYEE (4) SET SALARY = SALARY * (1.0 * RATE / 100.0 ) WHERE EMPNO = EMPLOYEE_NUMBER; END @ ('@') is used as the terminating character for stored procedures in Net DB2.Summary of the article must be memorized by any developer. I open the procedure in Visual studio, and see the old name after the “Alter procedure” as you said.Regards, Pinal Hi Pinal, I read the article and tried to follow the same. It’s not unexpected that saving this procedure fails.Though after executing sp_helptext, I saw that the name is not changed, but when i see in the sys.procedures, i found that the name of the procedure is changed i.e the sys.procedures system table do get updated. My solution was to change the name that comes after the “Alter procedure” to the new one and everything is fine ever since.thanks, Alirezahelo sir, i was reading ur suggestion and try to solve my problems…after creating the stored procedure ,if i want to delete that procedure so what can i do for that? Pinal Dave is a SQL Server Performance Tuning Expert and an independent consultant.

Leave a Reply