MSSQL Server – Stored Procedure Basic

This article will show you how to create stored procedure with MSSQL syntax. And also in this article you will understand how to use the input parameter and output parameter.

Note: By default, If you create stored procedure and you don’t specified the database to store your stored procedure that you’ve created, it will store in “master” database. If you want to store your Stored Procedure in another database. You just use this syntax:

use { database name }
go

1. Syntax Stored Procedure

+ To create stored procedure:

create proc procedure_name
          @param1    data_type     [output],
          @param2    data_type     [output]
as
begin
      [declare the variable to use in stored]
      { the statements }
end
go

+ To execute stored procedure:

execute procedure_name
exec procedure_name

+ To drop stored procedure:

drop procedure procedure_name
drop proc procedure_name

+ To update stored procedure:

alter proc procedure_name
          @param1    data_type     [output],
          @param2    data_type     [output]
as
begin
      [declare the variable to use in stored]
      { the statements }
end
go

Note: SQL Server will replace the stored procedure name “procedure_name” by the stored procedure have the same name.

2. Input Parameter and Output Parameter

2.1/ Input Parameter (Default)

+ It allows you to pass values to stored procedure to solve the problem

+ Example 1: Input parameter

create proc usp_Hello
          @input    varchar(150), // Input Parameter
as
begin
      printf 'Hello' + ' ' + '@input'
end
go

In this example, I’ve passed the Input parameter name “@input”. When we execute “usp_Hello”, we must pass the value “type varchar(150)” for this stored to execute

exec usp_Hello 'World'
go

=> Result: Hello World

2.2/ Output Parameter

+ The output parameter used to retrieve the result from the stored procedure.
+ Example 1: Output parameter

create proc usp_Sum
          @num1    int,        // Input parameter
          @num2    int,        // Input parameter
          @result  int  output // Output parameter
as
begin
      set @result = @num1 + @num2
end
go

———- Execute ———-

declare @res int
exec usp_Sum 1, 2, @res output
print @res
go

=> Result: 1 + 2 = 3

+ In the example above, We see the @result retrieve the result from the (@num1 + @num2).
+ To execute the stored procedure has the output parameter. We must declare the variable and specified that (output) to store the result

3. Examples

+ Example 1: Subtract two number

+ Example 2: Multiply two number – using output parameter