Generic Stored Procedure for Updating different Datatype Columns

Generic Stored Procedure to Update Columns of different datatypes.

userPreferences Table

UserName(varchar) ShowBorder (bit) RecentLinks(int) UserLocation
A 1 4 US
B 0 5 IN

CREATE PROCEDURE [dbo].[UpdateUserPreferences]
@UserPreferenceName Varchar(50),
@UserPreferenceType Varchar(50),
@UserPreferenceValue Varchar(50)
AS
Declare @UpdateSql Varchar(500)

SET @UpdateSql = 'update [tblUserPreferences] Set '+ @UserPreferenceName + ' = Cast('

If (@UserPreferenceType = 'Char' or @UserPreferenceType = 'Varchar')
SET @UpdateSql = @UpdateSql+ '''' + @UserPreferenceValue + '''' + ' As ' + @UserPreferenceType + '(50)) where userName = SYSTEM_USER'
ELSE
SET @UpdateSql = @UpdateSql + @UserPreferenceValue + ' As ' + @UserPreferenceType + ') where userName = SYSTEM_USER'
EXEC (@UpdateSql)

Usage:

EXEC UpdateUserPreferences ‘ShowBorder’,’bit’,’0’

EXEC UpdateUserPreferences ‘UserLocation’,’Varchar’,’AUS’

This is not the optimal solution but works on small tables.Above SP also demonstrate the simple use of dynamic sql statements.

Comments

Popular posts from this blog

Duplicate Folders Freeware - Not Duplicate Files

SSRS Compile Errors

Free SQL Server Management Studio Addin/Plugin/External Tool to get object information quickly