Trim Non-Alpha characters from string

Here is the code to trim non-alpha characters
from the string. This script uses tally table approach.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go 

--=============================================

-- Author: Atif Sheikh

-- Create date: 28-05-2010

-- Description: Trim Non Alpha Characters

--=============================================

--Select dbo.fnTrimNonAlphaCharacters('2131231Atif123123 234234Sheikh6546')

CREATE FUNCTION [dbo].[fnTrimNonAlphaCharacters]

(

   @pString varchar(max)

)

RETURNS varchar(max)

AS

BEGIN

   Declare @vRetString varchar(max)

   Set @vRetString = ''

   ;with
   wcte as (

      Select Top(len(@pString)) *

      from (Select row_number() over (order by a.object_id) N

            from sys.columns a, sys.columns b

           ) Main

    )SELECT @vRetString = @vRetString + SUBSTRING(@pString,N,1)

     FROM wcte a

     WHERE N <= LEN(@pString)

     And (Ascii(SUBSTRING(@pString,N,1)) between 97 and 122

     Or Ascii(SUBSTRING(@pString,N,1)) between 65 and 90

     Or Ascii(SUBSTRING(@pString,N,1)) = 32)

     ORDER BY N

  Return @vRetString

END

Database Mirroring Automating Failover for Multiple SQL Server Databases With No Witness

If Someone is performing Mirroring in SQL Server 2005, he should look at the following articles...


Database Mirroring Automating Failover for Multiple SQL Server Databases


Also


Database Mirroring Automating Failover for Multiple SQL Server Databases With No Witness


Enjoy...

Parsing / breaking character separated values in a table in one query

Consider a table having following data,
Declare @vTable Table(id int,val varchar(100))

Insert into @vTable Values (1,'Atif Sheikh2 Sheikh123')

Insert into @vTable Values (2,'Asif Sheikh1 Sheikh2 Sheikh3')

Now if you want to break
the space separated values in the abovetable as;

ID     Val
1       Atif
1       Sheikh2
1       Sheikh123
2       Asif
2       Sheikh1
2       Sheikh2
2       Sheikh3

Here is the query;
;with wcte (id,val,newpos) as

(
Select id,substring(val,0,case when charindex(' ',val,0) = 0 then Len(val) else charindex(' ',val,0) + 1 end) as val,charindex(' ',val,0) + 1 newpos from
@vTable

Union all

Select b.id,substring(b.val,newpos,case when charindex(' ',b.val,newpos) = 0 then Len(b.val) else charindex(' ',b.val,newpos) -newpos end) as val

,charindex(' ',b.val,newpos) + 1 newpos

from @vTable b

Inner Join wcte on wcte.id = b.ID

and wcte.newpos <> 1

)Select ID,Val from wcte order by id

Let me explain the above mentioned query. As you can see that i have used a
recursive function CTE.

The first query in the CTE query is;
Select id,substring(val,0,case when charindex(' ',val,0) = 0 then Len(val) else charindex(' ',val,0) + 1 end) as val,charindex(' ',val,0) + 1 newpos from
@vTable
This creates the main record for parsing the string. This
loads the first value in the required data set and to use in the second part.
Here I used the newpos column to get the position to
parse the next value in the character separated string. It is just the length
of the previous parsed value. The second part is;

Select b.id,substring(b.val,newpos,case when charindex(' ',b.val,newpos) = 0 then Len(b.val) else charindex(' ',b.val,newpos) -newpos end) as val

,charindex(' ',b.val,newpos) + 1 newpos

from @vTable b

Inner Join wcte on wcte.id = b.ID

and wcte.newpos > 1

This query uses the posintion in newpos column and parses the next value. And as is is recursive, it goes on until the newpos
> 1. newpos, as you can
see in the first part of the query, is the charindex of the character which is
used to separate the values in the string.

Thats it...

Concatenating row values into one grouped column

Sometimes, small things create alot of problems. Like, a core issue discussed in here.
Consider we have a table as;

Declare @vTable Table(id int,val varchar(100))

Insert into @vTable Values (1,'Atif')
Insert into @vTable Values (1,'Sheikh')
Insert into @vTable Values (2,'Asif')
Insert into @vTable Values (2,'Sheikh')
Insert into @vTable Values (2,'Sheikh2')
Insert into @vTable Values (2,'Sheikh3')

 
Now we need to concatenate values as;
 
ID      Vals
1       Atif Sheikh
2       Asif Sheikh Sheikh2 Sheikh3
 
 
There may be alot of ways to perform this but using XML Path here is a savior. The query is;
 
Select Distinct id,
Stuff((Select ' ' + [val] from @vTable t2 where id = Main.ID for XML Path('')),1,1,'') as Vals
from @vTable Main

Blogger Buzz: Blogger integrates with Amazon Associates

Blogger Buzz: Blogger integrates with Amazon Associates

Basic Rules for coding in User Defined Functions

1.Ownership

a)The developer writing the Function must provide his / her Full Name, Create
Date, and brief Description of the Function. This commented information should
be the first part of the Stored Procedure.

-- Author : Atif Sheikh
-- Create date : 25-Aug-08
-- Description : Brief Description


2.SET Options

a)The Following options should be set in Each Stored Procedure,

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET NOCOUNT ON

3.Naming Conventions

a)Function name should start with ‘fn. The next alphabet after ‘fn’ should be
CAPITAL and each recognizable word in the name of Stored Procedure should start
with CAPITAL letter. For example ‘fnMyFunction’

b)Parameters of Function should start with ‘@p’. For Example, @pFunctionVariable.
The next alphabet after ‘@p’ should be CAPITAL and each recognizable word in the
name of variable should start with CAPITAL letter.

c)Return variable of Function should start with ‘@pRet’. For Example,
@pRetVariable_OUT. The next alphabet after ‘@p’ should be CAPITAL and each
recognizable word in the name of variable should start with CAPITAL letter.

d)Scope variables should start with ‘@’ sign, following with the initials of the
data type of the respective variable. The next alphabet after ‘@’ + initials of
the data type should be CAPITAL and each recognizable word in the name of
variable should start with CAPITAL letter. For example, @intIntegerVariable,
@varcVarcharVariable, @varbVarbinaryVariable, etc.


4.Data Types

a)The data Types used in the Function should be the SQL Server Native data Types.
If any User Defined data type is used in the Function, the comment must be added
in the code.

b)The length and precision should be provided in accordance with the usage of the
variable.


5.Code Indenting

a)The Code of the Function should be properly Indented and readable. The spacing
can be controlled by using TAB key (equal to 4 spaces).

b)Each Block of code should begin with BEGIN keyword and end with END keyword.


6.SELECT Statements

a)SELECT Statements should contain the column names.


7.Table Or Scalar

a)Select right type of function depending upon the return values. If it is a
single value, go for Scalar. If it is a table, then go for table valued function.


7.Performance

a)Always use function in the outer most query if your query is nested.. In this
way you will be applying
function on the final result set. Using it in the inner queries will definitely
effect performance badly. For Example;

-- Wrong approach
-- This query is applying function on all records in employee.
Select * from
(
Select EmployeeID,Name,
fnGetMinimumSalary(tblDesignation.SalaryID) as MinimumSalary
from tblEmployee
Left Outer Join tblDesignation on tblDesignation.ID = tblEmployee.DesignationID
) Main
Inner Join tblEmployeeDetail on tblEmployeeDetail.EmployeeID = Main.EmployeeID
Where tblEmployeeDetail.CountryCode = 'PK'

-- Right approach
-- This query is applying Function only on the Employee with Country Code 'PK'
Select *,fnGetMinimumSalary(tblDesignation.SalaryID) as MinimumSalary
from
(
Select EmployeeID,Name,
SalaryID
from tblEmployee
Left Outer Join tblDesignation on tblDesignation.ID = tblEmployee.DesignationID
) Main
Inner Join tblEmployeeDetail on tblEmployeeDetail.EmployeeID = Main.EmployeeID
Where tblEmployeeDetail.CountryCode = 'PK'

Basic rules for coding in Stored Procedures in SQL Server

Following are few impoortant tips for coding Stored Procedure in SQL Server;

1.Ownership

a)The developer writing the Stored Procedure must provide his / her Full Name,
Create Date, and brief Description of the Stored Procedure. This commented
information should be the first part of the Stored Procedure.

-- Author: Atif Sheikh
-- Create date : 25-Aug-08
-- Description : Brief Description


2.SET Options

a)The Following options should be set in Each Stored Procedure,

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET NOCOUNT ON


3.Naming Conventions

a)Stored Procedure name should start with ‘usp’. The next alphabet after ‘usp’
should be CAPITAL and each recognizable word in the name of Stored Procedure
should start with CAPITAL letter. For example ‘uspInsertProcedure’

b)INPUT parameters of Stored Procedure should start with ‘@p’ and end with ‘_IN’.
For Example, @pInputVariable_IN. The next alphabet after ‘@p’ should be CAPITAL
and each recognizable word in the name of variable should start with CAPITAL
letter.

c)OUTPUT parameters of Stored Procedure should start with ‘@p’ and end
with ‘_OUT’. For Example, @pOutputVariable_OUT. The next alphabet after ‘@p’
should be CAPITAL and each recognizable word in the name of variable should
start with CAPITAL letter.

d)Scope variables should start with ‘@’ sign, following with the initials of the
data type of the respective variable. The next alphabet after ‘@’ + initials of
the data type should be CAPITAL and each recognizable word in the name of
variable should start with CAPITAL letter. For example, @intIntegerVariable,
@varcVarcharVariable, @varbVarbinaryVariable, etc.



4.Data Types

a)The data Types used in the Stored Procedure should be the SQL Server Native data
Types. If any User Defined data type is used in the Stored Procedure, the
comment must be added in the code.

b)The length and precision should be provided in accordance with the usage of the
variable.


5.Return IDENTITY

a)In order to return the IDENTITY value from INSERT Stored Procedures,
SCOPE_IDENTITY should be used.

b)Proper OUTPUT parameter should be defined in the Stored Procedure to return the
value of IDENTITY.

6.Dynamic SQL

a)Dynamic SQL should be avoided as much as possible. But, if there is the need of
the code, execute the SQL string using ‘sp_Executesql’ with proper parameters.

b)In case of DDL Statements, EXECUTE can be used.


7.Code Indenting

a)The Code of the Stored Procedure should be properly Indented and readable. The
spacing can be controlled by using TAB key (equal to 4 spaces).

b)Each Block of code should begin with BEGIN keyword and end with END keyword.


8.TRANSACTION

a)The DML operations should be controlled by TRANSACTIONS. These Transactions
should be synchronized with the Front-End transactions to avoid any conflict.

b)Do not Set TRANSACTION ISOLATION LEVEL without consulting the Database Department


9.TRY / CATCH

a)TRY / CATCH Block should be the part of every Stored Procedure to control and
record errors.


10.Return ERROR_NUMBER

a)Proper Error Number should be returned from the CATCH Block to the application.


11.Nested SP calls

a)Nested Stored Procedure calls should be avoided. In case of necessity, the
TRANSACTION should be applied to the master Stored Procedure, calling the rest
of the Sps.


12.INSERT Statements

a)INSERT Statements should contain the column names.


13.SELECT Statements

a)SELECT Statements should contain the column names.

Check Invalid XML Message in Service Broker Queue

As we know after five failed concurrent
transactions you will get what is known as Message Poisoning after which your queue
will get disabled . Sometimes, it is required to check the invalid messages in
a service broker queue.

Begin try

Declare @vBin varbinary(max)

Declare @pBQuery nvarchar(max)

Declare @MessageXML XML

Declare vCur Cursor for Select Message_Body from dbo.BLOB_AdtQueue_Remote1_IOW

Open vCur

Fetch Next from vCur into @vBin

while @@Fetch_status = 0

begin

   Select @pBQuery = convert(nvarchar(max),@vBin)

   Select @MessageXML = cast(@pBQuery as XML)

   Fetch Next from vCur into @vBin

end

End Try

Begin Catch

   SELECT

   ERROR_NUMBER() AS ErrorNumber,

   ERROR_SEVERITY() AS ErrorSeverity,

   ERROR_STATE() AS ErrorState,

   ERROR_LINE() AS ErrorLine,

   ERROR_PROCEDURE() AS ErrorProcedure,

   ERROR_MESSAGE() AS ErrorMessage,GETDATE(),

   CONVERT(sysname ,USER_NAME())AS DBUSERNAME,

   CONVERT(SYSNAME,SUSER_SNAME()) AS SYSUSERNAME

   Select @vBin

End Catch

Deallocate vCur

Get Default Values of Parameters of Stored Procedures

If you are dealing and quering on the
schema of your database, you probably need this script;

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--
=============================================

-- Author:        Atif

-- Create date:24-May-2010

-- Description:    Get Default Value of a Parameter of SP or Function

--
=============================================

ALTER FUNCTION [dbo].[fnGetParameterDefaultValue]

(    

    @pSPName varchar(1000)='',

    @pParameterName varchar(100),

    @pDataType varchar(100),

    @pType bit=0 --0 for Stored Procedure and 1 for Function

)

RETURNS varchar(1000)

AS

BEGIN    

    Declare @pOutPut varchar(1000)

    Declare @vStartPosition int

    Declare @vStartPosition2 int    

    Declare @vStartPosition3 int

    Declare @vStartPosition4 int

    Declare @vStartPosition5 int

    Declare @vSPText varchar(max)

    Declare @vSPText2 varchar(max)

-- Get the text form syscomments (first 4000 characters if length is > 4000)

    if @pType = 0

    begin

        SELECT @vSPtext = (SELECT text FROM syscomments

         WHERE id = object_id(@pSPName) and colid=1 and number = 1)

        Set @vSPtext = SubString(@vSPtext,CharIndex('CREATE PROCEDURE',@vSPtext),4000)

    end

    else

        SELECT @vSPtext = (SELECT text FROM syscomments

         WHERE id = object_id(@pSPName) and colid=1 and number = 0)

      Set @vSPtext = SubString(@vSPtext,CharIndex('CREATE FUNCTION',@vSPtext),4000)

    if IsNull(@vSPtext,'') = ''

    begin

        -- Exit if SP Name Not found in syscomments....

        Select @pOutPut = ''

        RETURN @pOutPut

    end 

    Set @pOutPut = ''
    While 1=1
    Begin
        -- Get the position of the parameter definition.
        Select @vStartPosition = PatIndex('%' + @pParameterName + '%',@vSPText)
        -- Check if parameter exists
        if @vStartPosition > 0
        begin
            -- Get the Definition String
            select @vSPText=RIGHT(@vSPText, DataLength(@vSPText)-(@vStartPosition -1))

            -- Get the string breaker

            if (CharIndex(',',@vSPText) > 0) or (CharIndex('-',@vSPText) > 0)

                or (CharIndex(Char(10),@vSPText) > 0) or (CharIndex('AS',@vSPText)

      > 0)
            begin                
                Set @vStartPosition = CharIndex(',',@vSPText,Len(@pParameterName))-1

                Set @vStartPosition2 = CharIndex('-',@vSPText,Len(@pParameterName))-1

                Set @vStartPosition3 = CharIndex(Char(10),@vSPText,Len(@pParameterName))-1

                Set @vStartPosition4 = CharIndex('AS',@vSPText,Len(@pParameterName))-1

                Set @vStartPosition5 = CharIndex('OUT',@vSPText,Len(@pParameterName)) -1

                if @vStartPosition <= (Len(@pParameterName) + Len(@pDataType) + case when CharIndex('AS',@vSPText) between CharIndex(@pParameterName,@vSPText) And CharIndex(@pDataType,@vSPText) then 2 else 0 end)

                    Set @vStartPosition = 10000000

                if @vStartPosition2 <= (Len(@pParameterName) + Len(@pDataType) + case when CharIndex('AS',@vSPText) between CharIndex(@pParameterName,@vSPText) And CharIndex(@pDataType,@vSPText) then 2 else 0 end)

                    Set @vStartPosition2 = 10000000

                if @vStartPosition3 <= (Len(@pParameterName) + Len(@pDataType) + case when CharIndex('AS',@vSPText) between CharIndex(@pParameterName,@vSPText) And CharIndex(@pDataType,@vSPText) then 2 else 0 end)

                    Set @vStartPosition3 = 10000000

                if @vStartPosition4 <= (Len(@pParameterName) + Len(@pDataType) + case when CharIndex('AS',@vSPText) between CharIndex(@pParameterName,@vSPText) And CharIndex(@pDataType,@vSPText) then 2 else 0 end)

                    Set @vStartPosition4 = 10000000

                if @vStartPosition5 <= (Len(@pParameterName) + Len(@pDataType) + case when CharIndex('AS',@vSPText) between CharIndex(@pParameterName,@vSPText) And CharIndex(@pDataType,@vSPText) then 2 else 0 end)

                    Set @vStartPosition5 = 10000000

                Select Top 1 @vStartPosition = [value]
                from dbo.fnSplit(Cast(@vStartPosition as varchar(10)) + ',' + Cast(@vStartPosition2 as varchar(10))
                                + ',' + Cast(@vStartPosition3 as varchar(10))

                                + ',' + Cast(@vStartPosition4 as varchar(10))

                                + ',' + Cast(@vStartPosition5 as varchar(10)) ,',')    

                order by Cast([value] as int)

            end

            else

            begin

                -- SP text must atleast have AS to break the parameter definition string

                Set @vStartPosition = CharIndex('AS',@vSPText) - 1

            end             

            -- Get the specific Definition String

            Set @vSPText2 = Left(@vSPText,@vStartPosition)

            -- Check if you got the right one by data type...            

            if CharIndex(@pDataType,@vSPText2) > 0

            begin

                --Select 'IN'

                --Select @text2

                if CharIndex('=',@vSPText2) > 0

                begin

                    -- check the default value

                    Select @pOutPut = Right(@vSPText2,DataLength(@vSPText2) - CharIndex('=',@vSPText2))

                    -- We have default value assigned here

                if Right(@pOutPut,1) = ','

                        Set @pOutPut = Left(@pOutPut,DataLength(@pOutPut)-1)

                end

                else

                begin

                    --Set @pOutPut = 'No Default Value Defined...'

                    -- We DO NOT have default value assigned here

                    Set @pOutPut = ''

                end

                --No need to work further with this parameter

                BREAK

            end

            else

            begin   

                --Set @vSPText = SubString(@vSPText,@vStartPosition + Len(@vSPText2),4000)

                -- Cut the SP text short and loop again

                Set @vSPText = SubString(@vSPText,@vStartPosition,4000)

            end

            -- This should never be the case. Just a check....

            if Datalength(@vSPText) < Datalength(@pParameterName)

                Break

        end

        else

        begin

            --Set @pOutPut = 'Parameter Not Found...'

            -- Wrong parameter search...

            Set @pOutPut = ''

            Break

        end

    End

    Select @pOutPut = rtrim(ltrim(@pOutPut))

    RETURN @pOutPut

END

To use this function, here is the proper way;

Select a.[name],b.[name] ,dbo.fnGetParameterDefaultValue('[dbo].[YourSPName]',a.[name],b.[name],0) as defaultVal

from sys.parameters a

inner join sys.types b on b.system_type_id = a.system_type_id

where Object_id = object_id('[dbo].[YourSPName]')