Rick Caminiti dotCOM

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION getWeekByDoW

(

      – Add the parameters for the function here

      @date datetime,

    @day    varchar(3)

)

RETURNS datetime

AS

BEGIN

      DECLARE @dowNum   int,

                  @truncDate datetime,

                  @retDate datetime;

 

      select @truncDate = cast(convert(varchar, @date, 101) as datetime)

           ,@dowNum =

                  case

                        when @day = ‘Sun’

                              then 1

                        when @day = ‘Mon’

                              then 2

                        when @day = ‘Tue’

                              then 3

                        when @day = ‘Wed’

                              then 4

                        when @day = ‘Thu’

                              then 5

                        when @day = ‘Fri’

                              then 6

                        when @day = ‘Sat’

                              then 7

                        else 0

                  end

 

      select @retDate = DATEADD(DD, @dowNum - DATEPART(DW, @truncDate), @truncDate)

      RETURN @retDate

END

GO

 

select      getWeekByDoW(getdate(), ‘Sun’) Sun,

            getWeekByDoW(getdate(), ‘Mon’) Mon,

            getWeekByDoW(getdate(), ‘Tue’) Tue,

            getWeekByDoW(getdate(), ‘Wed’) Wed,

            getWeekByDoW(getdate(), ‘Thu’) Thu,

            getWeekByDoW(getdate(), ‘Fri’) Fri,

            getWeekByDoW(getdate(), ‘Sat’) Sat

 

Sun	Mon	Tue	Wed	Thu	Fri	Sat
6/28/2009	6/29/2009	6/30/2009	7/1/2009	7/2/2009	7/3/2009	7/4/2009

 

I needed to loop through a large table by date, one month at a time and process the data.  This is the basic cursor that I wrote in T-SQL.  It’s pretty straight-forward.

First, you declare the cursor as a select statement.

Then you open the cursor (make sure you CLOSE it and DEALLOCATE it when you’re done!!)

Declare cursor-scope variables to fetch the data into and fetch the data from the cursor.

Loop through the data while the fetch status is “SUCCESS”.

Then check to make sure the fetch status indicates that the row was loaded properly and do whatever processing you need to do.

When done processing, fetch the next row and continue looping.

After the looping, close the cursor and deallocate it from memory.

DECLARE cur CURSOR

      FOR select distinct  cast(convert(varchar(25),dateadd(dd,-(day([date_column])-1),[date_column]),101) as datetime) begdate

                                    ,cast(convert(varchar(25),dateadd(dd,-(day(dateadd(mm,1,[date_column]))-1),dateadd(mm,1,[date_column])),101) as datetime) enddate

              from [dbo].[StagingTable]

             order by 1;

OPEN cur

      DECLARE @begdate datetime, @enddate datetime;

      FETCH NEXT from cur INTO @begdate, @enddate

      WHILE (@@FETCH_STATUS <> -1)

      BEGIN

            IF (@@FETCH_STATUS <> -2)

            BEGIN

                        INSERT INTO [dbo].[DestTable]

                                       ([col1]

                                       ,[col2]

                                       ,[col3]

                                       ,[col4]

                                       ,[col5])

                        SELECT [col1]

                                ,[col2]

                                ,[col3]

                                ,[col4]

                                ,[col5]

                          FROM [dbo].[StagingTable]

                         WHERE [date_column] >= @begdate

                           AND [date_column] < @enddate;

/*SELECT @begdate begdate, @enddate enddate, @@ROWCOUNT insrows;*/

                        DELETE

                          FROM [dbo].[StagingTable]

                         WHERE [date_column] >= @begdate

                           AND [date_column] < @enddate;

/*SELECT @begdate begdate, @enddate enddate, @@ROWCOUNT delrows;*/

            END  

      FETCH NEXT from cur INTO @begdate, @enddate

      END

CLOSE cur

DEALLOCATE cur

GO

 

 

Return value	Description
0	The FETCH statement was successful.
-1	The FETCH statement failed or the row was beyond the result set.
-2	The row fetched is missing.

 

 Somehow, there’s no built-in function equivalent to Oracle’s LPAD or RPAD in SQL.  There are several ways to do this, in fact, I posted another way to do RPAD at http://rickcaminiti.com/bi/t-sql-equivalent-of-oracles-rpad/.  Unfortunately, this leaor
ves no way to do an LPAD.

So here’s the scenario:  You have, let’s say an account number that is stored as a number in one system and as a char in another system with leading zeros.  So, 123 might look like 000123 in the other system.  So if you need to represent 123 as 000123, you need to turn it into a char(6) and add 3 zeros to the begining.  That’s exactly what this LPAD function would do. 

CREATE FUNCTION LPAD

               (@SourceString VARCHAR(MAX),

                @FinalLength  INT,

                @PadChar      CHAR(1))

RETURNS VARCHAR(MAX)

AS

  BEGIN

    RETURN

      (SELECT Replicate(@PadChar,@FinalLength - Len(@SourceString)) + @SourceString)

  END

GO

 

 

REATE FUNCTION RPAD

               (@SourceString VARCHAR(MAX),

                @FinalLength  INT,

                @PadChar      CHAR(1))

RETURNS VARCHAR(MAX)

AS

  BEGIN

    RETURN

      (SELECT @SourceString + Replicate(@PadChar,@FinalLength - Len(@SourceString)))

  END

GO

 

 In the example above, you would accomplish this like so:

EXEC dbo.LPAD(Cast(123 AS VARCHAR),6,'0')
OR

SELECT NewString = dbo.LPAD(Cast(123 AS VARCHAR),6,'0')

Here’s where I found it. http://www.sqlusa.com/bestpractices2005/padleadingzeros/

I always thought the only way to have a computed or derived column in SQL was to use some kind of a view in place, but I found out that you can actually create a computed column on a table and that you can even “persist” the data and have it indexed.

I’m only guessing that behind the scenes, SQL treats this somewhat like a trigger, like a before-trigger, where just prior to the transaction being posted, the calculations are applied and the column is populated, then the full transaction is applied.

Here’s the article where I found this information.

And here’s an example:

use database

 

create table t

( a int, b int);

 

--adding the computed column

alter table t add

x as case a 

            when -1 then

                  b

            else

                  a

            end persisted not null

--using "persisted" is required if you want to make column "not null".  I believe "persisted" is somewhat implied if computed column is derived from PK columns.

 

--just showing that you can create an index on the new column

create index z on t (x)

 

 

insert into t values (1, -1)

insert into t values (2, -1)

insert into t values (-1, 4)

insert into t values (-1, 3)

insert into t values (-1, -1)

 

select * from t

 

 

 

--you can see that the computed column worked.

a       b       x     

1       -1      1     

2       -1      2     

-1      4       4     

-1      3       3     

-1      -1      -1   

Here’s a quick and dirty way to send email via MSSQL.  For some reason, we’re not allowed to send email using the Email Task in SSIS, so I chose this option.  Unfortunately, even if you have access to execute sp_SQLSMTPMail, you might not have access to run xp_cmdshell.

 

I ended up having to create my SSIS package with a SQL Task that runs this proc, then I had to run it with a SQL Agent Job with a proxy account that had access to xp_cmdshell.  Fun stuff.

 

 

 

 

declare @developerperson varchar(80)

declare @message_body varchar(1000)

 

set @message_body =

‘Dear ‘ + @developerperson + ‘Use \n for line breaks and escape your ‘’s’

 

 

EXECUTE dbo.sp_SQLSMTPMail

      @vcTo = ‘You@you.com’,

      @vcCC= ‘Them@them.com’,

      @vcSubject = ‘Super Cool SQL Email’,

      @vcBody = @message_body,

      @vcFrom = ‘Me@me.com’,

      @vcSMTPServer = ‘SMTPName’,

      @vcSenderName = ‘Some One’,

      @vcServerName = @@SERVERNAME –no need to specify because this is the default anyway

Or, lets say you want to email query results as an attachment:

EXECUTE dbo.sp_SQLSMTPMail

  @vcTo=‘you@you.com’

, @vcCC=‘them@them.com’

, @vcSubject=‘Here is your wonderful report that is terribly formatted as a txt file attachment’

, @vcquery= ’select col1, col2, col3, col4 from database.schema.table’

 

 

 

 

 

 

 

 

Here’s the actual Proc definition: Read more…