Dynamic SQL, writing an UPDATE Statement

I came across a situation, where I had to write a different sort of update statement. Following was the user requirement.

UPDATE two columns A and B in a table, say MY_TABLE based on the following condition.

• Update column A of MY_TABLE if a certain set of conditions met else dont update it
• Update column B of MY_TABLE if a second set of certain conditions are met else dont update it

Following is the code that can be used to perform this task.

DECLARE @UPDATEQUERY VARCHAR(1000) ;
DECLARE @COMMA VARCHAR(3) ;


SET @UPDATEQUERY = 'UPDATE MY_TABLE SET'
SET   @COMMA = '   '  
IF (SET OF FIRST CONDITIONS )
BEGIN
SET @UPDATEQUERY = @UPDATEQUERY + ' A =  '+ @var1 --COLUMN A IS ASSIGNED VALUE OF VARIABLE @VAR1
SET @COMMA = ' , ';
END

 
IF (SET OF SECOND CONDITIONS)

BEGIN
SET @UPDATEQUERY =@UPDATEQUERY+@COMMA
SET @UPDATEQUERY = @UPDATEQUERY + ' B= ''Y'' '--ASSIGNING COLUMN B A VALUE 'Y'
END

SET @UPDATEQUERY = @UPDATEQUERY + ' WHERE C = '+ @var2


--CHECKING IF ANY OF THE TWO COLUMNS IS BEING UPDATE THEN EXEC THE DYNAMIC QUERY.
IF(CHARINDEX('A',@UPDATEQUERY,1 )<>0 AND CHARINDEX('B',@UPDATEQUERY,1 )<>0 )
EXEC(@UPDATEQUERY)

ELSE

SET @UPDATEQUERY = NULL;

Read More

How to Comma Separate characters/words in a String

If you have a string of characters/words separated by comma and you want to comma separate each character/word in the string then you can use the following function.
Most often you come across this situation when you are passing an argurment to a Stored Procedure and the agruement being passed is a string of lets say ID_NO seperated by comma. In the Stored procedure you want to evaluate against each ID_NO in the string then you can use this function to comma separate each ID_NO

CREATE FUNCTION [dbo].[fn_CommaSeparatedValues]
(
@String VARCHAR(500)
) 

RETURNS @Seperated TABLE
 (
Id  INT IDENTITY(1, 1) NOT NULL,
Value VARCHAR(200)
 )

AS

BEGIN


DECLARE @Value VARCHAR(200)
DECLARE @StartPos bigint
DECLARE @EndPos bigint
DECLARE @LengthOfString int
DECLARE @ReachedEnd Char(1)
DECLARE @IndefOfComma INT
SET @StartPos=1
SET @EndPos=0
SET @LengthOfString=LEN(@String)
SET @ReachedEnd='N'

WHILE @ReachedEnd<>'Y'
BEGIN
SET @EndPos=CHARINDEX(',',@String,@StartPos)

 IF @EndPos>0

BEGIN
SET @Value = SUBSTRING(@String, @StartPos,@EndPos-@StartPos)
SET @StartPos=@EndPos+1
END

ELSE

BEGIN
SEt @ReachedEnd='Y'
SET @Value = SUBSTRING(@String, @StartPos,@LengthOfString-(@StartPos-1))
END

INSERT INTO @Seperated(Value) VALUES(@Value)

END
RETURN
END

Read More

Function to Remove Non Numeric Characters from String/Column

In order to remove non Numeric values from a String, you can use the following function. This Function uses two important String functions PATINDEX and STUFF.  The first Argument of Patindex explains a pattern, ^ sign show NOT and 0-9 showing range. So  this function will look for all those characters in the string passed which are not in the range 0-9.
STUFF will later replace the non Numeric character with a blank space.

CREATE FUNCTION dbo.RemoveNonNumeric(@String varchar(1000))
RETURNS VARCHAR(50)
BEGIN
DECLARE @Cur_Index INT
SET @Cur_Index = PATINDEX('%[^0-9]%',@String)

WHILE @Cur_Index > 0
BEGIN
SET @String = STUFF(@String,@Cur_Index,1,'')
SET @Cur_Index = PATINDEX('%[^0-9]%',@String)

END

RETURN @String

END
GO

Read More