The ‘NULL’ Debate, and a few other interesting facts

http://blogs.msdn.com/b/sqljourney/archive/2012/04/18/the-null-debate-and-a-few-other-interesting-facts.aspx?CommentPosted=true#commentmessage

Printing Time variable and string on same line in SQL

print 'There are ' + CAST(@OfficeStartTime AS VARCHAR) + ' alias combinations did not match a record'

SQL case when then else

CASE WHEN ebv.db_no IN (22978,23218,23219) THEN 'WECS 9500' ELSE 'WECS 9520' END

SQL Nested cursors

-- nestedcursor 'dave'

ALTER procedure nestedcursor
@LoginId varchar(50)
as

begin
declare @FileId int = null
declare @Sender varchar(200) = null
declare @Recepient varchar(200) = null
declare @RecStatus char(1)
declare @IsProcessed char(1)
declare @ProcessedOn datetime = null
declare @EstProcessedOn datetime = null
declare @ProcessType char(1) = null
declare @TemplateId int = null
declare @SeqNum int = null
declare @OfficeStartTime time
declare @CurrentTime time
declare @Status BIT
declare @TemplateCode varchar(20) = null
declare @StageCode int
declare @AlertType varchar(1) = null
 
 select @OfficeStartTime = CONVERT(TIME(0), DATEADD(MINUTE, 60*office_start_time, 0)),
@CurrentTime = CONVERT(VARCHAR(8),GETDATE(),108)
 from mst_comp
 where comp_id = (select comp_id
from mst_user
where login_id = 'ABCA')

--print 'Cursor start'

declare @TrackLogCursor CURSOR

if @CurrentTime < @OfficeStartTime
begin

SET @TrackLogCursor = CURSOR
FOR

select 1 as [file_id],'Shekhar' as Sender,'Satish' as Recepient,'P' as rec_status,'N' as is_processed,ttracklog.tr_end_dt as processed_on,
  hdrtrack.tpt_code,ttracklog.stage_code,ttracklog.tr_seq_num
from   trn_track ttracklog join trn_file hdrtrack on (hdrtrack.tracking_num=ttracklog.tracking_num)
  join trn lead on (lead.lead_id=hdrtrack.lead_id and lead.rec_status='A')
where ttracklog.tr_date is null and CONVERT(DATE, ttracklog.tr_end_dt) = CAST(CURRENT_TIMESTAMP AS DATE)
order by ttracklog.tracking_num desc


end
  else if @CurrentTime > @OfficeStartTime
begin

SET @TrackLogCursor = CURSOR
FOR

select 1 as [file_id],'Shekhar' as Sender,'Satish' as Recepient,'P' as rec_status,'N' as is_processed,ttracklog.tr_end_dt as processed_on,
  hdrtrack.tpt_code,ttracklog.stage_code,ttracklog.tr_seq_num
from   trn_track ttracklog join trn_file hdrtrack on (hdrtrack.tracking_num=ttracklog.tracking_num)
  join trn lead on (lead.lead_id=hdrtrack.lead_id and lead.rec_status='A')
where  ttracklog.tr_date is null and CONVERT(DATE, ttracklog.tr_end_dt) = CAST(CURRENT_TIMESTAMP AS DATE) and CAST(ttracklog.lastupd_stamp AS TIME) > @OfficeStartTime
order by ttracklog.tracking_num desc
   
end



--print 'Open Cursor'

OPEN @TrackLogCursor

FETCH NEXT FROM @TrackLogCursor INTO @FileId,@Sender,@Recepient,@RecStatus,@IsProcessed,@ProcessedOn,@TemplateCode,@StageCode,@SeqNum

WHILE @@FETCH_STATUS = 0

BEGIN

begin

declare @AlertTypeCursor CURSOR
SET @AlertTypeCursor = CURSOR
FOR

select alertconfig.alert_type ,'' ,mgateway.template_id,alertgatway.gateway_id
from   mst_stage links
  left outer join mst_stage_link alertconfig on(links.stage_link_id=alertconfig.stage_link_id and alertconfig.rec_status='A')
  left outer join mst_stage_link_gat alertgatway on (alertgatway.alert_config_id=alertconfig.alert_config_id and alertgatway.rec_status='A')
  left outer join mst_alert_ga mgateway on (mgateway.gateway_code=alertgatway.gateway_id and mgateway.rec_status='A')
  left outer join mst_alert_t malert on (malert.alert_type_code=alertconfig.alert_type and malert.rec_status='A')
where links.tpt_cd = @TemplateCode and links.next_stage = @StageCode
order by links.stage_code,links.next_stage

OPEN @AlertTypeCursor

FETCH NEXT FROM @AlertTypeCursor INTO @AlertType,@EstProcessedOn,@TemplateId,@ProcessType

WHILE @@FETCH_STATUS = 0

BEGIN

print @TemplateCode
print @StageCode


FETCH NEXT FROM @AlertTypeCursor INTO @AlertType,@EstProcessedOn,@TemplateId,@ProcessType

END

CLOSE @AlertTypeCursor

DEALLOCATE @AlertTypeCursor


end  


FETCH NEXT FROM @TrackLogCursor INTO @FileId,@Sender,@Recepient,@RecStatus,@IsProcessed,@ProcessedOn,@TemplateCode,@StageCode,@SeqNum

END

CLOSE @TrackLogCursor

DEALLOCATE @TrackLogCursor

--start cursor for insert update record in table

end

SQL Cursors

http://www.tutorialspoint.com/plsql/plsql_cursors.htm

SQL cursor example 2

-- [CRM].[insert] 'dave'

Alter procedure [CRM].[insert]
@LoginId varchar(50)
as

begin
declare @FileId int = null
declare @Sender varchar(200) = null
declare @Recepient varchar(200) = null
declare @RecStatus char(1)
declare @IsProcessed char(1)
declare @ProcessedOn datetime = null
declare @EstProcessedOn datetime = null
declare @ProcessType char(1) = null
declare @TemplateId int = null
declare @SeqNum int = null
declare @OfficeStartTime time
declare @CurrentTime time
declare @Status BIT
 
 select @OfficeStartTime = CONVERT(TIME(0), DATEADD(MINUTE, 60*office_start_time, 0)),
@CurrentTime = CONVERT(VARCHAR(8),GETDATE(),108)
 from mst_comp
 where comp_id = (select comp_id
from mst_user
where login_id = 'ABCA')

--print 'Cursor start'

declare @TrackLogCursor CURSOR

if @CurrentTime < @OfficeStartTime
begin

SET @TrackLogCursor = CURSOR
FOR

select 1 as [file_id],'Shekhar' as Sender,'Satish' as Recepient,'P' as rec_status,'N' as is_processed,tl.tr_end_dt as processed_on,
  tl.tr_end_dt as est_processed_on,'S' as process_type,1 as template_id,tl.tr_seq_num
from trn_track tl
where tl.tr_date is null and CONVERT(DATE, tl.tr_end_dt) = CAST(CURRENT_TIMESTAMP AS DATE)

end
  else if @CurrentTime > @OfficeStartTime
begin

SET @TrackLogCursor = CURSOR
FOR

select 1 as [file_id],'Shekhar' as Sender,'Satish' as Recepient,'P' as rec_status,'N' as is_processed,tl.tr_end_dt as processed_on,
  tl.tr_end_dt as est_processed_on,'S' as process_type,1 as template_id,tl.tr_seq_num
from trn_track tl
where tl.tr_date is null and CONVERT(DATE, tl.tr_end_dt) = CAST(CURRENT_TIMESTAMP AS DATE)
 and CAST(lastupd_stamp AS TIME) > @OfficeStartTime
   
end



--print 'Open Cursor'



OPEN @TrackLogCursor

FETCH NEXT FROM @TrackLogCursor INTO @FileId,@Sender,@Recepient,@RecStatus,@IsProcessed,@ProcessedOn,@EstProcessedOn,@ProcessType,@TemplateId,
@SeqNum

WHILE @@FETCH_STATUS = 0

BEGIN

begin


IF EXISTS  (SELECT  1
FROM trn_alert
WHERE tracking_seq_num = @SeqNum and rec_status = 'P')
Update trn_alert
set est_processed_on = @EstProcessedOn,
updated_on = GETDATE()
where tracking_seq_num = @SeqNum

ELSE
 insert into trn_alert([file_id],Sender,Recepient,rec_status,is_processed,processed_on,est_processed_on,process_type,template_id,
               updated_by,updated_on,tracking_seq_num)
 values(@FileId,@Sender,@Recepient,@RecStatus,@IsProcessed,@ProcessedOn,@EstProcessedOn,@ProcessType,@TemplateId,@LoginId,GETDATE(),@SeqNum)

end  


FETCH NEXT FROM @TrackLogCursor INTO @FileId,@Sender,@Recepient,@RecStatus,@IsProcessed,@ProcessedOn,@EstProcessedOn,@ProcessType,@TemplateId,
@SeqNum

END

CLOSE @TrackLogCursor

DEALLOCATE @TrackLogCursor

--start cursor for insert update record in table

end

SQL Function to extract time from Datetime2

  select CAST(lastupd_stamp AS TIME)
 from trn