What is ALTER PROCEDURE used for?-CodePudding

I'm new on Databases. Could you explain the lines of this piece to me please? or give me a link to some documentation that will help me understand this code. I only understood that the code is modifying the stored procedure :

ALTER PROCEDURE [dbo].[sp_SetRequestStatus] 
    @requestId int,
    @status int,
    @statusnote nvarchar(max) = '',
    @Result nvarchar(2000) OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    UPDATE t_Request 
        SET StatusId = @status, 
        UpdatedStatusTime = GETDATE(), 
        StatusNote = @statusnote, 
        UpdatedDate = GETDATE() 
        WHERE Id = @requestId
    SET @Result = 'Success'
END

CodePudding user response：

Ok. I'm going to risk being a bit pedantic for this room and go through everything in detail, and this is definitely overkill. Here's the first part:

ALTER PROCEDURE [dbo].[sp_SetRequestStatus] 
    @requestId int,
    @status int,
    @statusnote nvarchar(max) = '',
    @Result nvarchar(2000) OUTPUT 
AS

This is the declaration for the stored procedure. It is using ALTER PROCEDURE which assumes that the procedure already exists in the DB. A stored procedure is roughly equivalent to a C function that returns void. There's more to it, but that's the basic part.

This procedure has four arguments. The first two are of type int (four bytes signed integer). nvarchar(max) ='' is a variable length character string in Unicode which can have a max length of 2Gbytes, and has a default value of empty string, which is not the same thing as NULL. That distinction is very important, and you should look further into that.

The final argument @Result nvarchar(2000) OUTPUT is an output variable with a Unicode character set that has a maximum length of 2000 characters.

Moving on, SET NOCOUNT ON; turns off the row count from the later UPDATE. Normally it isn't a good idea to set this to off, for performance and other reasons. It should be set to ON.

Now on to the UPDATE

    UPDATE t_Request 
        SET StatusId = @status, 
        UpdatedStatusTime = GETDATE(), 
        StatusNote = @statusnote, 
        UpdatedDate = GETDATE() 
        WHERE Id = @requestId

This is updating the table t_Request. It is setting the column StatusID with the passed in argument @status, StatusNote with the argument @statusnote, UpdatedStatusTime with the time right now (GETDATE()) likewise with the date on UpdatedDate. Finally the UPDATE is restricted to those rows where Id = @requestId

The last thing the procedure does is assign 'Success' to @Result. When this procedure is called in a script, any variable you pass in as the fourth argument will now contain 'Success'.

Like I said, this is way overkill, and far too detailed, but there you go.