Search This Blog

Wednesday, June 23, 2010

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

Tuesday, June 15, 2010

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

Friday, June 11, 2010

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]')