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...

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...

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...

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...

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...

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 SCINNER JOIN SYS.procedures P ON P.OBJECT_ID = SC.IDAND P.NAME LIKE 'SP%' AND TEXT LIKE '%DECLARE%CURSO...

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...

Read More