Updating a stored procedure

For this reason its range is limited to database operations and necessary functions; PSQL is in itself however a full and powerful language, and offers more functionalities than you can use if you were just sat on the client.The full range of keywords and functions available for use in procedures and triggers can be found in the for the definition, description and variables of a stored procedure along with comprehensive instructions of how to use IBExpert's Stored Procedure Editor.I think using the parameter names makes code easier to read.Also remember that character string are delimited by single quotes.If you want table names or column names or an entire WHERE clause in your variable it gets more complicated. The second step is to update the existing record with the new status value.That statement looks like this: CREATE PROCEDURE sp New Value @p PKValue int, @p Status char(8) AS SET NOCOUNT ON INSERT My Table SELECT Field1, Field2, 'Active' as Status From My Table Where PKValue = @p PKValue UPDATE My Table SET Status = @p Status WHERE PKValue = @p PKValue go I include a SET NOCOUNT ON so that now extra rows are returned to my client application.It allows an enormous flexibility and a high level of user customization.

You can find a good (though advanced) discussion of that here.Now we have the primary key of the record we wish to update and the new status value. We'll use an INSERT/SELECT statement that looks like this:: Notice that we didn't need to insert a value for PKValue since it's an identity column.I used my parameter in the WHERE clause to indicate which record I wanted.Let's assume your table looks like this: I'll need to pass two parameters to our stored procedure.The first is the old primary key value and the second is the new status.

Leave a Reply