How many ways can procedures be executed?

Procedures can be executed via 2 ways:

Static way:

Via passing the parameters directly to the procedure and execute it, in multiple ways:

-- Create Stored Procedure

CREATE PROCEDURE usp_TestParameter

@p_First VARCHAR(50),

@p_Second VARCHAR(50)

AS

SELECT @p_First FirstParameter,

     @p_Second SecondParameter

GO

Method 1:

In this we will just pass the parameters , and the results

-- Execute SP

EXEC usp_TestParameter 'One','Two'

GO

EXEC usp_TestParameter 'Two', 'One'

Drawback for this method is the sequence of parameter will change the results, these issues are faced mainly when there are large number of parameters which are executed, to overcome this issue there is a solution as mentioned below:

Method 2:

In this right the parameter name and followed by the value of the parameter, this will solve the issue, below is the sample for the same:

-- Execute SP

EXEC usp_TestParameter @p_First='One', @p_Second='Two'

GO

EXEC usp_TestParameter @p_Second='Two', @p_First='One'

GO

Dynamic way:

Via passing the parameters from tables and executing the query using sp_executesql

Statements:

--create a table for the input values

create table input_values( col1 varchar(50),col2 varchar(50))

  insert into input_values

  select 'One','Two'

  select * from input_values

--declare a variable for the query

  declare @V_query nvarchar(500)

  set @V_query =(select 'exec usp_TestParameter @p_First='''+ col1 + ''', @p_Second=''' +col2 +'''' from input_values) – set the data for the query variable

  select @V_query – Check the query variable

  exec sp_executesql @statement =@V_query – execute the query variable

Below is the execution shown in SQL: