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

FTP Task in SSIS Package failed when the package is executed via job

I have an SSIS package which was supposed to FTP the files to a specific server as a last task of the package. When I executed the package on BIDS, it worked perfectly however when I run it via job it failed. Each time I get the following error message:
"
Executed as user: mercury/admin. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 10:27:13 PM Error: 2012-02-27 22:32:16.15 Code: 0xC001602A Source: Package1 Connection manager "FTP Connection Manager" Description: An error occurred in the requested FTP operation. Detailed error description: 425 Can't open data connection. 421 Connection timed out. . End Error Error: 2012-02-27 22:32:16.17 Code: 0xC002918B Source: FTP Task FTP Task Description: Unable to send files using "FTP Connection Manager".
"
I searched and found that I had to run the job using a proxy account having the same credentials used to create the package. I created a proxy account and under the JOB step section I selected the proxy account to run job as:


However doing all this didn't solve my problem until I did the following:

Turned Off the firewall on my machine and Boooomm the job perfectly executed the package including the FTP task.

Read More

String Functions - SQL Server 2008 -- CHARINDEX

CHARINDEX ( expression1, expression 2, [starting position]) :

This is one of the very important string functions in SQL Server. This function return the first position of a character or character string found within another character string. Here expression 1 is the string of character that would be searched in expression 2. Starting position is the index in expression 2 where CHARINDEX would start looking for expression 1 in expression 2.
The index of the first Character of expression2 is 1 .

Example:

expression 1 : you

expression 2 : Can you name the fastest animal on Earth. (MAX Length allowed in SS is 8000 characters)

Starting position : 0

In this case, we are trying to get the starting index of the expression1 if it exists in expression 2. SQL Server would start searching for the word "you" in expression 2 from left to right. If we did not specify the starting position, it is null , -ve or zero then the whole expression is searched starting from 1st position from left side.

In this particular case this function would return 5. Since in expression 2 the first character is at position 1, from there if you start counting, you would find the position of y (y of you) is 5th.





If expression 1 does not exist in expression 2 then in this case 0 is returned.

When to use it :
Most of the time when you need a specific substring in a string, you can use this function to get the starting/ending index of the substring.

Read More

How to find stored procedure with specific text in the code

I came across a situation where I had to find the list of all such stored procedures defined in a specific database containing at least one cursor in their code. The following query worked for me.

SELECT DISTINCT P.NAME FROM SYSCOMMENTS SC
INNER JOIN SYS.procedures P ON P.OBJECT_ID = SC.ID
AND P.NAME LIKE 'SP%' AND TEXT LIKE '%DECLARE%CURSOR%'

Read More

String Functions - SQL Server 2008

String functions operate on a string input value, and return a string or a numeric value. All built in string functions are deterministic, as they return the same result for a given set of values. Below we would discuss each function in detail with examples.

ASCII(character) :

This function returns ASCII values against the input character.

Example:

This function take single character as a parameter, if you provide it a string of character. It will give you the ASCII value for the first character in the string and will ignore rest of the characters in the string.

CHAR(integer) :

This functions returns a character value against a specific ASCII value.

Example:

Integer value given as a parameter range from 0-255. For any other value, it returns NULL.

LEN(string) :

This function returns number of characters in the given character string. It does not include the trailing blank spaces.

Example:

If you want to include the trailing spaces in string length, you need to use DATALENGTH() function.

RTRIM(expression) :

If you have a string and you want to get rid of the spaces from the right side of the string then you can use this function.

Here you can see that we have select a string with one space on its left and few spaces on its right.


now if we use Rtrim, these blank spaces from the right side of the resulting string would then be removed.




LTRIM(expression) :

LTRIM works exactly the same the RTRIM works however it works on the left side of the string. It removes any space from the left side of the string.

Read More