PARAMETERIZED CODE IN EXECUTE SQL TASK

We have gotten the introduction about Execute SQL Task in my previous post. Now here we look into, how to handle parameterized code in Execute SQL Task.

The Execute SQL Task supports, below parameter types :-

• Input :- for input param
• Output:- for output param
• ReturnValue:- for return codes.

Parameters can be mapped to user defined variables or system variables.

PARAMETER NAME AND MARKER: -

Parameter name and marker used to set the mapping between parameter and variables. It depends on the used Connection and Connection Manager.

In the case of EXCEL and OLEDB connection type, 

• We use “?” as parameter marker. For example :-

SELECT *

FROM tbl1

WHERE name = ?

• To provide values to parameters marker, variables are mapped to parameter names in parameter mapping tab of Execute SQL Task. Then, the Execute SQL task uses the ordinal value of the parameter name (OR we can say Ordinal Value of parameter marker) in the parameter list to load values from variables to parameters.

Connection Type	Parameter Marker	Parameter Name	Example
Excel & OLE DB	?	0,1,2,3...	SELECT * FROM tbl WHERE name = ?

EXCEL, OLE DB connection managers require that the SQL command use question marks (?) as parameter markers and 0-based numeric values as parameter names.

USING PARAMETER IN “WHERE” CLAUSE:-

We use WHERE clause with SELECT, INSERT, UPDATE and DELETE commands to specify filters that define the conditions each row in the source tables must meet to qualify for an SQL command. Parameters provide the filter values in the WHERE clauses.

We can use parameter marker to dynamically provide the parameter values, for example :-

SELECT *

FROM tbl

WHERE name = ?

AND city = ?

In the above example, parameter name should be 0 and 1 respectively in the case of OLE DB and EXCEL connection.

USING PARAMETERS WITH STORED PROCEDURE:-

We can also use parameter mapping to execute stored procedure. For example,

As an input parameter in the case of Excel and OLE DB connection:-

EXEC proc_Name ?

As an output parameter in the case of Excel and OLE DB connection:-

EXEC proc_Name ? OUTPUT

GETTING VALUES OF RETURNCODE: -

A stored procedure can return an integer value, called a return code, to indicate the execution status of a procedure. To implement return codes in the Execute SQL task, we have to use parameters of the ReturnValue type.

Example of input parameter in the case of EXCEL AND OLE DB connection: -

EXEC ? = proc_Name 1