SQL Server Information and Resources |
Custom Search
|
The following example is for creating a simple update stored procedure. You can run it through an explicit call from a host language program or directly from a DBMS query execution shell like SQL Server Management Studio or dbOrchestra.
CREATE PROCEDURE dbo.sp_Students_UPD_byPK
@active_flg TINYINT = NULL ,
@student_id INT
AS
BEGIN
SET NOCOUNT ON
UPDATE dbo.Students
SET
active_flg = ISNULL(@active_flg , active_flg )
FROM dbo.Students
WHERE
student_id = @student_id
END
GO
It is important to note that for every column you include in the update code you will need to enter data for. Typically, an sql update stored procedure would be created for all the columns. There would be a mapping layer that moves existing data to the exposed variables. In the example provided I have created an update stored procedure that could be tied directly to a function that would activate or inactivate a student. Having an sql update stored procedure for this type of activity is wholly rational and appropriate.
To run the stored procedure you need to supply a values to the applicable variables.
-- @active_flg IN TINYINT
-- @student_id IN INT
EXEC dbo.sp_Students_UPD_byPK
@active_flg = 0 ,
@student_id = 23
Link to schema for Students table