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:
@ClientName VARCHAR(100) OUTPUT
SET NOCOUNT ON
@ClientName = @FirstName + @LastName
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.
@FirstName = 'Vincent',
@LastName = 'Vega',
@Age = 40,
@ClientName = @ClientNameReturn OUTPUT
This procedure also has an output parameter, which is denoted by the OUTPUT reserved word.