T-SQL Programming - Useful Tips - Dynamic Query

Code-1: Dynamic query checks column name exists in the table


DECLARE 
@tableName VARCHAR(100) = 'table_name',
@targetColumnName VARCHAR(100) = 'column_name',
@columnExists SMALLINT = 0,
@sql NVARCHAR(MAX)
;

SET @sql = 'SELECT @cnt=COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '''+@tableName+''' AND COLUMN_NAME = '''+@targetColumnName+'''';
PRINT @sql;
EXECUTE sp_executesql @sql, N'@cnt SMALLINT OUTPUT',@columnExists OUTPUT;

Points to consider:

1. All text parameters for sp_executesql have to be 'N' type (Note: string to the code page that corresponds to the default collation of the database or column) (See Code-1, Code-2)
2. Parameter (in/out) definition count has to match with parameters provided (see Code-2, four parameters declared for dynamic query, two are in and two are out parameters,)
3. If there are any output type parameters defined, than corresponding parameters have to mark as OUTPUT also (See Code-2)
4. Input or output parameters assignment can have same or different name (see Code-2, all variables used inside dynamic query and declared outside match with corresponding one)

Code-2: Dynamic query takes two input parameters, does two mathematics operations and returns results in two output parameters

DECLARE 

@inParam1 INT = '2',

@inParam1 INT = '4',

@outParam1 INT,

@outParam2 INT,

;

SET @sql = 'SELECT @inParam1, @inParam2';
SET @sql = @sql + 'SET @outParam1=@inParam1 + @inParam2';
SET @sql = @sql + 'SET @outParam1=@inParam1 * @inParam2';
PRINT @sql;

EXECUTE sp_executesql @sql, N'@inParam1 INT, @inParam2 INT,@outParam1 INT OUTPUT, @outParam2 INT OUTPUT', @inParam1, @inParam2, @outParam1 OUTPUT, @outParam2 OUTPUT;