Uncategorized

—-Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
LastDay_PreviousMonth
—-Last Day of Current Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
LastDay_CurrentMonth
—-Last Day of Next Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))
LastDay_NextMonth
ResultSet:
LastDay_PreviousMonth
———————–
2007-07-31 23:59:59.000

LastDay_CurrentMonth
———————–
2007-08-31 23:59:59.000

LastDay_NextMonth
———————–
2007-09-30 23:59:59.000

If you want to find last day of month of any day specified use following script.
–Last Day of Any Month and Year
DECLARE @dtDate DATETIME
SET @dtDate = ‘8/18/2007’
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))
LastDay_AnyMonth
ResultSet:
LastDay_AnyMonth
———————–

declare @body1 nvarchar(MAX)
set @body1 = N’

‘
print @body1
EXEC msdb.dbo.sp_send_dbmail @recipients=’sunil@iglobeconsulting.com’,
@subject = ‘My Mail Test’,
@body = @body1,
@profile_name = ‘Prakash Rathod’,
@body_format = ‘HTML’ ;

We used to use uniqueidentifier so many times in our database, since it is one of the unique field in table, we may need to put it in dynamic SQL quite a few times but I have seen so many times that even seasoned developer don’t know how to use UniqueIdentifier in dynamic SQL, may be in Store procedure in SQL Server. This is the reason I tempted to write something for this topic.

Let us see it practically:

–create table for testing
if OBJECT_ID(‘IDTester’) is not null drop table IDTester
create table IDTester
(
ID uniqueidentifier default newid(),
name varchar(20)
)
GO

–insert few records
insert into IDTester(name)
select ‘Ritesh’ union all
select ‘Rajan’ union all
select ‘Bihag’ union all
select ‘Abhijit’
GO

–let us see what we come up with
select * from IDTester
GO

–create simple SP
Create proc SPIDTester
@ID uniqueidentifier
as
begin
select * from IDTester where ID=@ID
end
GO

—-I got ‘7F1D8BC8-48AA-437E-B19F-4ABD139AD5E5’ for first record
—-you may get something else as a ID of first records.
exec spidtester ‘7F1D8BC8-48AA-437E-B19F-4ABD139AD5E5′
GO

–let us create another SP with dynamic SQL but it will show us an error
Create proc SPIDTester2
@ID uniqueidentifier
as
begin
declare @sql varchar(max)
set @sql=’select * from IDTester where ID=’ + @ID
exec (@sql)
end
GO
–if you will try to create above SP, you will be greeted with
–following error
–Msg 402, Level 16, State 1, Procedure SPIDTester2, Line 6
–The data types varchar and uniqueidentifier are incompatible in the add operator.

–you have to use sp_executeSQL to get rid of above error
–with additional parameter
create proc SPIDTester2
@ID uniqueidentifier
as
begin
declare @sql nvarchar(max)
set @sql=’select * from IDTester where ID=@I’
exec sp_executesql @sql,N’@I uniqueidentifier’,@I=@ID
end
GO

–let us see whether SP actually works
exec spidtester2 ‘7F1D8BC8-48AA-437E-B19F-4ABD139AD5E5′
GO

———————————– Pass more then one parameter —————————————-

DECLARE @SQL_String NVARCHAR(max)
DECLARE @Parameter_Definition NVARCHAR(max)

SET @SQL_String = N’
EXEC GetEmail2 @EmployeeId = @EmployeeId_input, @Email = @Email_out OUTPUT
‘

SET @Parameter_Definition = N’
@EmployeeId_input uniqueidentifier,
@Email_out nvarchar(50) OUTPUT’

DECLARE @EmployeeId uniqueidentifier
DECLARE @Email nvarchar(50)

SET @EmployeeId = ‘997B3351-F876-414B-9C63-B90EC967B69B’

EXECUTE sp_executesql @SQL_String, @Parameter_Definition, @EmployeeId_input = @EmployeeId, @Email_out = @Email OUTPUT

SELECT @Email as Email

GO