JQuery - Best Practices

Best practices:

1. Save selection in a variable if the selection needs to make multiple time.
   Explanation:  jQuery doesn't do caching of selections and for every selection lot of code runs behind the scene
2. Use 'if($("element").length)' instead of  'if($("element"))' to make sure selection has records
   Explanation:  Any selection using $() always returns an object and object always evaluates to TRUE, even if selection doesn't contain any elements.
   'if($("element"))' will return '[]' which is treated as empty object 

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;

Convert data type of a column

Background:
This post will cover few options to convert a column data type without loosing actual data. All the scripts will work fine based on the assumption that value in column can actually be converted and data will not be lost. For example, INT to VARCHAR conversion is relatively straight forward, no possible data loose. Whereas, VARCHAR to INT might fail if that column has non-numeric value or has value which is too big (INT column range is -2,147,483,648 to 2,147,483,647).


Option-1 is simpler, actual data type change statement is one line. Option-2 is much complicated approach where a dummy column is used. I have added Option-2 for two reasons, 1) that's the one I created first 2) in some scenarios Option-2 might help others to consider as base approach and build deployment/database structure modification script on top of that.


Option-1: using ALTER COLUMN



DECLARE 
@tableName VARCHAR(100) = 'wk_fact_ctr_transaction',
@targetColumnName VARCHAR(100) = 'account',
@targetColumnDataType VARCHAR(100) = 'INT',
@columnExists SMALLINT = 0,
@sql NVARCHAR(MAX)
;


-- Check column exists in the table
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;


IF @columnExists IS NOT NULL AND @columnExists > 0
BEGIN
PRINT 'Column exists in the table';
BEGIN TRY
SET @sql = 'ALTER TABLE '+@tableName+' ALTER COLUMN '+@targetColumnName+' '+@targetColumnDataType;
EXECUTE(@sql);
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;


SELECT 
@ErrorMessage = ERROR_MESSAGE() + ' - COLUMN CONVERSION NOT POSSIBLE.',
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;
END
ELSE
PRINT 'Column does not exist in the table';

Option-2: ALTER COLUMN by introducing a dummy column



DECLARE 
@tableName VARCHAR(100) = 'wk_fact_ctr_transaction',
@targetColumnName VARCHAR(100) = 'account',
@dummyColumnName VARCHAR(100) = 'account_temp',
@targetColumnDataType VARCHAR(100) = 'VARCHAR(100)',
@dummyColumnDataType VARCHAR(100) = 'INT',
@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;


IF @columnExists IS NOT NULL AND @columnExists > 0
BEGIN
PRINT 'Column exists in the table';
BEGIN TRY
SET @sql = 'ALTER TABLE '+@tableName+' ADD '+@dummyColumnName+' '+@dummyColumnDataType;
EXECUTE(@sql);


SET @sql = 'UPDATE '+@tableName+' SET '+@dummyColumnName+'='+@targetColumnName;
EXECUTE(@sql);


SET @sql = 'ALTER TABLE '+@tableName+' DROP COLUMN '+@targetColumnName;
EXECUTE(@sql);


SET @sql = 'ALTER TABLE '+@tableName+' ADD '+@targetColumnName+' '+@targetColumnDataType;
EXECUTE(@sql);


SET @sql = 'UPDATE '+@tableName+' SET '+@targetColumnName+'='+@dummyColumnName;
EXECUTE(@sql);


SET @sql = 'ALTER TABLE '+@tableName+' DROP COLUMN '+@dummyColumnName;
EXECUTE(@sql);
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;


SELECT 
@ErrorMessage = ERROR_MESSAGE() + ' - COLUMN CONVERSION NOT POSSIBLE.',
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;
END
ELSE
PRINT 'Column does not exist in the table';