General Notes

Convert.ToDateTime(date1).ToString(“dd-MMM-yyyy”);

—–

declare @myxml xml;

select @myxml = ‘<ParameterValues>

<ParameterValue>
<Name>TO</Name>
<Value>myemail@mail.com</Value>
</ParameterValue>

<ParameterValue>
<Name>BCC</Name>
<Value>myemail@mail.com</Value>
</ParameterValue>

<ParameterValue>
<Name>IncludeReport</Name>
<Value>True</Value>
</ParameterValue>

<ParameterValue>
<Name>Subject</Name>
<Value>Customers in Switaly</Value>
</ParameterValue>

</ParameterValues>’

–select @myxml

–select @myxml.query(‘/ParameterValues/ParameterValue/Value[../Name = ”Subject”]’)
select @myxml.query(‘/ParameterValues/ParameterValue/Value12’)

=======================

select * from wsiagingreport
select [XmlQuerySql].value(‘(/ROOT/Slot1/SlotPeriodValue)[1]’, ‘varchar(200)’) as Slot1PeriodValue,
[XmlQuerySql].value(‘(/ROOT/Slot1/SlotPeriodType)[1]’, ‘varchar(200)’) as SlotPeriodType,
[XmlQuerySql].value(‘(/ROOT/Slot1/ThresholdType)[1]’, ‘varchar(200)’) as ThresholdType,
[XmlQuerySql].value(‘(/ROOT/Slot1/ThresholdValue)[1]’, ‘varchar(200)’) as ThresholdValue
from wsiagingreport

====================================

Except in sqlserver is equivalent to Minus in ORACLE

===

usage of case when then else end
………………………….
select @Slot1_Count = count(*) from wsiincident
where @Slot1_PeriodValue >=
case when @Slot1_PeriodType =’Hour’
     then datediff(hour, sourcetoolcreateddate,getdate())  
     when @Slot1_PeriodType =’Day’
     then datediff(Day, sourcetoolcreateddate,getdate())
     when @Slot1_PeriodType =’Week’
     then datediff(Week, sourcetoolcreateddate,getdate())
     when @Slot1_PeriodType =’Fortnight’
     then datediff(Day, sourcetoolcreateddate,getdate())/14
     when @Slot1_PeriodType =’Month’
     then datediff(Month, sourcetoolcreateddate,getdate())
     when @Slot1_PeriodType =’Quarter’
     then datediff(Quarter, sourcetoolcreateddate,getdate())
     when  @Slot1_PeriodType =’Year’
     then datediff(Year, sourcetoolcreateddate,getdate())
     else 0 end

===================================

select datediff(Year,’2003-12-12′,getdate())
declare @ss nvarchar(100)
set @ss = ‘h’
select case when @ss =’h’ then datediff(hour,’2003-12-12′,getdate())
   when @ss =’m’ then datediff(minute,’2003-12-12′,getdate())
   when @ss =’s’ then datediff(second,’2003-12-12′,getdate())
   else 0 end

============================================
suppose in a sp u need like this

create proc p_c
as
begin
select * from table
where names in (‘s,g,d’)
end  .. this will work

but
create proc p_c
@strings nvarchar(1000)
as
begin
select * from table
where names in (@strings )
end  .. this wont work as strings is a parameter.

To solve this problem
create a splitstring function and split the string and insert into a temporary table

create proc p_c
@strings nvarchar(1000)
as
begin
select * from table
where names in (select names from db0.splitstrings(@strings))
end  .. this will work
splitstring function is:
ALTER FUNCTION [dbo].[SplitStrings](@String varchar(8000), @Delimiter char(1))       

      returns @temptable TABLE (items varchar(8000))       

as       

begin       

    declare @idx int       

    declare @slice varchar(8000)       

 

    select @idx = 1       

        if len(@String)<1 or @String is null  return       

 

    while @idx!= 0       

    begin       

        set @idx = charindex(@Delimiter,@String)       

        if @idx!=0       

            set @slice = left(@String,@idx – 1)       

        else       

            set @slice = @String       

 

        if(len(@slice)>0)  

            insert into @temptable(Items) values(@slice)       

 

        set @String = right(@String,len(@String) – @idx)       

        if len(@String) = 0 break       

    end   

return       

end 

 

 

 

usage : where id in (select id from [SplitStrings](‘a,b’,’,’))
===================================

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: