How to Call a Stored Procedure

Although there are a few ways to call a stored procedure, only one way is actually recommended. The best way is to explicitly specify the stored procedure parameters in the Execute statement. You do this because if the order of the parameters were to change, then the procedure would error out (or even worse pass the incorrect values to the parameters).

Let’s create a stored procedure as an example:

[cc lang=”sql”]
CREATE PROCEDURE spReturnFullName
(
@FirstName varchar(50),
@LastName varchar(50),
@Age int,
@ClientName varchar(100) OUTPUT
)
AS
SET NOCOUNT ON
BEGIN

SELECT
@ClientName = @FirstName + @LastName

END
[/cc]

Now that we have our procedure, let’s call the procedure using EXECUTE. We can also use the EXEC statement as a replacement for Execute, it is merely a simplified way to call the same statment. So below we are specifically specifying the parameters of the procedure. Unfortunately, SQL Server allows procedures to be executed without specifying the parameter name. The way we are doing it below is not only self-documenting, but as mentioned, it will not allow the wrong values to be passed to a parameter.

[cc lang=”sql”]
DECLARE @ClientNameReturn varchar(100)

EXEC spReturnFullName
@FirstName = ‘Vincent’,
@LastName = ‘Vega’,
@Age = 40,
@ClientName = @ClientNameReturn OUTPUT

SELECT @ClientNameReturn
[/cc]

This procedure also has an output parameter, which is denoted by the OUTPUT reserved word.