Microsoft SQL Server Creating and executing a basic stored procedure


Example

Using the Authors table in the Library Database

CREATE PROCEDURE GetName
(
    @input_id INT = NULL,      --Input parameter,  id of the person, NULL default
    @name VARCHAR(128) = NULL  --Input parameter, name of the person, NULL default
) 
AS
BEGIN 
    SELECT Name + ' is from ' + Country 
    FROM Authors 
    WHERE Id = @input_id OR Name = @name
END 
GO

You can execute a procedure with a few different syntaxes. First, you can use EXECUTE or EXEC

EXECUTE GetName @id = 1
EXEC Getname @name = 'Ernest Hemingway'

Additionally, you can omit the EXEC command. Also, you don't have to specify what parameter you are passing in, as you pass in all parameters.

GetName NULL, 'Ernest Hemingway'

When you want to specify the input parameters in a different order than how they are declared in the procedure you can specify the parameter name and assign values. For example

 CREATE PROCEDURE dbo.sProcTemp 
 (
    @Param1 INT,
    @Param2 INT
)
AS
BEGIN

    SELECT
        Param1 = @Param1,
        Param2 = @Param2

END

the normal order to execute this procedure is to specify the value for @Param1 first and then @Param2 second. So it will look something like this

  EXEC dbo.sProcTemp @Param1 = 0,@Param2=1

But it's also possible that you can use the following

  EXEC dbo.sProcTemp @Param2 = 0,@Param1=1

in this, you are specifying the value for @param2 first and @Param1 second. Which means you do not have to keep the same order as it is declared in the procedure but you can have any order as you wish. but you will need to specify to which parameter you are setting the value

Access stored procedure from any database

And also you can create a procedure with a prefix sp_ these procuedres, like all system stored procedures, can be executed without specifying the database because of the default behavior of SQL Server. When you execute a stored procedure that starts with "sp_", SQL Server looks for the procedure in the master database first. If the procedure is not found in master, it looks in the active database. If you have a stored procedure that you want to access from all your databases, create it in master and use a name that includes the "sp_" prefix.

Use Master

CREATE PROCEDURE sp_GetName
(
    @input_id INT = NULL,      --Input parameter,  id of the person, NULL default
    @name VARCHAR(128) = NULL  --Input parameter, name of the person, NULL default
) 
AS
BEGIN 
    SELECT Name + ' is from ' + Country 
    FROM Authors 
    WHERE Id = @input_id OR Name = @name
END 
GO