Converting comma separated string into table

From time to time I'm facing with trouble in SQL how to convert  comma separated string into table.
For instance, you may have set of comma delimited parameters that you want to use in select:
"'US','UA','RU','DE'"
You want to use this set of country codes to select clients.
Without converting the comma separated string into table you will have to use dynamic SQL:

  exec 'Select * from clients where Country in (' + @countryList + ')'

This solution is not very good because if you have request bigger then one line, its will be difficult to trace it in case of errors.

There is a small function that converts comma separated string into table:

CREATE FUNCTION [dbo].[fn_CommaSeparatedStringToTable] 
( 
    @CommaSeparatedValues     VARCHAR(MAX),
      @IncludeEmptyStrings    CHAR(1)
) 
RETURNS @Item TABLE  
( 
      RowId int IDENTITY(1, 1) NOT NULL,  
    Value VARCHAR(200) 
)
AS
BEGIN
      DECLARE @IndefOfComma int,@Value VARCHAR(200),@StartPos bigint,@EndPos bigint,@LengthOfString int, @ReachedEnd Char(1)
 

      SET @StartPos=1
      SET @EndPos=0
      SET @LengthOfString=LEN(@CommaSeparatedValues)
      SET @ReachedEnd='N'
 

      WHILE @ReachedEnd<>'Y'
            BEGIN
                  SET @EndPos=CHARINDEX(',',@CommaSeparatedValues,@StartPos) 
                  IF @EndPos>0
                  BEGIN
                      SET @Value = SUBSTRING(@CommaSeparatedValues, @StartPos,@EndPos-@StartPos)  
                      SET @StartPos=@EndPos+1       
                  END
                  ELSE
                  BEGIN
                     SEt @ReachedEnd='Y'
                     SET @Value = SUBSTRING(@CommaSeparatedValues, @StartPos,@LengthOfString-(@StartPos-1))
                  END
                  IF(@Value<>'' OR @IncludeEmptyStrings='Y')
                  INSERT INTO @Item(Value) VALUES(@Value)
      END
      RETURN
END

After using this function, you can use reqular sql queries:

    Select 
        * 
    from 
        clients c
        inner join dbo.fn_CommaSeparatedStringToTable(@countryList, 'N') t on t.Value = c.Country 

As you may see, this way is much more useful.

Get closest Monday SQL function

From time to time I have to build reports for the application I'm working on.
Today they asked me to build the report that can accept any date and build results within Monday to Sunday.

The problem was, how to find date of Monday, since the user may enter any week date.
So, for instance, if the user enters '12/03/10' , for instance, that Friday, the system should find date of Monday '11/29/10'. And so on...

I wrote small sql function that helps to get it:

if exists (select * from sysobjects where name = 'GetMonday')
    drop function GetMonday
go

create  function [dbo].[GetMonday](@date DateTime)
-- Returns closest Monday date that going before passed date
returns datetime
as
begin
    declare @datePart int
    set @datepart = datepart(weekday, @date )

    --in case if sunday treats the system as first day of the week,
    --convert passed sunday date into the last day of the week
    -- 12/26/10 was sunday, so we are testing first what day of the week it returns
    if (datepart(weekday, '12/26/10') = 1 AND @datepart = 1)
    SET @datepart = 8

    return dateadd(dd, (@datePart - 2) * -1, @date)
end
go

select dbo.GetMonday(getdate())

How to find usage of sql within the database

Have you ever was in that situation as I do from time to time ?

• Remember the column or table in the database, but don't remember, where its exactly using and how ?
• Trying to  remember all stored procedures, functions or triggers that uses  some text, not matter its sql word or some name?
• Aasking yourself something like "A month ago, i wrote the procedure that has 'hallo world' text in it. What's the name of it?"

I'm falling in this situation quite often so decided to wrote something to make my life easier.
What I have now is small script that's actually kind of full-text search within database's programmability objects (procedures, function, triggers and so on).

Here is it:

declare @textToSearch varchar(max)
set @textToSearch = '%EmailLog%'

declare @total int;

with t (
    rownumber,
    name)
as
(
    select
        ROW_NUMBER() OVER (ORDER BY so.name) as rownumber,
        so.name 
    from
        syscomments sc 
        inner join sysobjects so on so.id = sc.id 
    where text like @textToSearch
)
select @total =count(*) from t

declare @currentPos int
set @currentPos = 1

declare @name varchar(max)

while (@currentPos <= @total) 
BEGIN 
    with t ( rownumber, name) as ( 
        select 
            ROW_NUMBER() OVER (ORDER BY so.name) as rownumber, 
            so.name 
        from
            syscomments sc 
            inner join sysobjects so on so.id = sc.id 
        where text like @textToSearch 
    ) 
    select @name = [name] from t where rownumber = @currentPos 
    Print '----------------------------------------------------------------------------------------------------' 
    Print @name Print '----------------------------------------------------------------------------------------------------' 
    EXEC sp_helptext @name; 
    Print '----------------------------------------------------------------------------------------------------' 
    set @currentPos = @currentPos + 1 
END 
go


Enter what are you looking for to the @textToSearch variable, between two '%' in example above.

In my example, i tried to find all places (stored procedures, functions, triggers and so on) that has text 'EmailLog'

To make result more readable, switch output view from Grid to Text. Use Ctrl+T or menu Query -> Results To -> Results To Text.

Just run it and  after a ninebt you will have text of each procedure or function that contains specified word