Friday, March 2, 2007

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.

No comments:

Post a Comment

IIS Manager Crashes on Load/Start Windows 7, IIS 7.5

IIS Manager Crashes on Start  Windows 7, IIS 7.5, Power Shell 5.1. Here is the error I got in the event viewer. IISMANAGER_CRASH IIS Ma...