String concatenation issue related to NULL values and solution for it

I am writing this blog to avoid issues related to string concatenation.

“+” is used to concatenate strings, prior to 2012. As you can see in the query below, two strings are being concatenated. Unfortunately this query doesn’t return expected results and it returns NULL.

To fix that, select query should be rewritten to replace null.

In Sql Sever 2012, Concat() is clearly a simpler way to concatenate strings. It improves code readability and returns the expected results since it automatically (implicitly) converts null to blank space.

CREATE TABLE #temp (

emp_name nvarchar(200) NOT NULL,

emp_middlename nvarchar(200) NULL,

emp_lastname nvarchar(200) NOT NULL

);

INSERT INTO #temp VALUES( 'Something', NULL, ' US' );

INSERT INTO #temp VALUES( 'Something', NULL, ' India' );

SELECT CONCAT( emp_name, emp_middlename, emp_lastname ) AS Result FROM #temp;

DROP TABLE #temp

You can use concat function instead of "+" operator.

Happy Coding!!

Working with ROW_NUMBER, sort expression and sort order without dynamic query

create table #Cricketers(

ID int identity(1,1),

Name nvarchar(255),

Comments nvarchar(255)

)

insert into #Cricketers

select 'David Warner','T20-opening batsman'

union all

select 'R Ashwin','All rounder'

union all

select 'Virender Sehwag','Test-opening batsman'

union all

select 'Rahul Dravid','Former Captain'

union all

select 'MS Dhoni','Captain, Team India'

declare @OrderByDirection NVARCHAR(200)='A'

declare @OrderBy NVARCHAR(200)='Comments'

select *,ROW_NUMBER() OVER (

ORDER BY CASE WHEN @OrderBy = 'Name'

AND @OrderByDirection = 'D'

THEN Name END DESC,

CASE WHEN @OrderBy = 'Name'

AND @OrderByDirection != 'D'

THEN Name END,

CASE WHEN @OrderBy = 'ID'

AND @OrderByDirection = 'D'

THEN ID END DESC,

CASE WHEN @OrderBy = 'ID'

AND @OrderByDirection != 'D'

THEN ID END,

CASE WHEN @OrderBy = 'Comments'

AND @OrderByDirection = 'D'

THEN Comments END DESC,

CASE WHEN @OrderBy = 'Comments'

AND @OrderByDirection != 'D'

THEN Comments END

) AS RowNumber

from #Cricketers

drop table #Cricketers


This way you can write the query. We use CASE.. WHEN to work with multiple conditions.

Happy Coding!!

Paging and sorting in sql server

Suppose you have to implement Paging and sorting then following code snippet will help you.

​create table #Student(id int identity(1,1), name nvarchar(100), birthdate datetime)

declare @PageSize int=5

declare @MinRowNumber int=1

declare @MaxRowNumber int= @MinRowNumber + @PageSize-1

declare @SortExpression nvarchar(100)='Name' ---'birthdate' Pass here column name.

declare @SortOrder nvarchar(100)='D' --'A' pass here sort order D- for descending and A for ascending.

insert #Student

select 'Viru','1986-12-07'

union all

select 'Sachin','1960-01-31'

union all

select 'Gambhir','1950-06-23'

union all

select 'Shreekant','1983-10-04'

union all

select 'Kapil','1981-03-17'

union all

select 'Yuvi','1986-03-02'

union all

select 'Dhoni','1987-12-05'

union all

select 'Zak','1975-08-17'

union all

select 'Bhajji','2007-08-06'

union all

select 'Ashwin','2010-04-22'

union all

select 'Ojha','2012-02-26'

union all

select 'Ravindra','2012-10-04'

SELECT RowNumber, name, birthdate,id FROM

(select *,ROW_NUMBER() OVER (ORDER BY

CASE WHEN @SortExpression = 'Name' AND @SortOrder='D' THEN Name END DESC,

CASE WHEN @SortExpression = 'Name' AND @SortOrder='A' THEN Name END ASC,

CASE WHEN @SortExpression = 'birthdate' AND @SortOrder='D' THEN birthdate END DESC,

CASE WHEN @SortExpression = 'birthdate' AND @SortOrder='A' THEN birthdate END ASC

) AS RowNumber from #Student) AS Student

WHERE RowNumber BETWEEN @MinRowNumber AND @MaxRowNumber

drop table #student


Happy Coding!!

Tables without Primary Key in Database

​

Some times you are analyzing the database to check which tables don't have primary keys then  in that case follwing query will help you to find out the tables:

Below SQL will give you the list of table in you Database not having Primary Keys.

SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName

FROM sys.tables

WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0

ORDER BY SchemaName, TableName;

Happy Coding!! 

Sending SMTP email from SQL Server using CDO

In one article we check how to send an email from Gmail server.

https://blogabhijeet.blogspot.in/2011/03/send-mail-through-gmail-smtp.html

Today we will check how to send an email from SQL Server.

exec sendMail_With_CDOMessage
'SMTP server name Or SMTP IP'
, '1'
,'25'
,'SMTP User name'
,'SMTP User Password'
,'From which email'
,'To which email'
,'Email Subject'
,'Email Body Message'
,'Email attachment from the SQL with full window path <optional>'

Make sure that your SMTP is confugure to have relay option from the sending server.
SMTP can be configure on the server itself with IIS. Or you can use external SMTP server to send email.

It is very useful when you want to send some automated message from SQL server itself based on certain task completion or failure.

You need to give grant permisions on following stored procedures. Following procedures are present in Master Database so you need to give grant permision to your user in master database.
sp_oacreate ,sp_OADestroy,sp_OAGetErrorInfo ,sp_OASetProperty,sp_OAMethod 

CREATE PROCEDURE sendMail_With_CDOMessage
@smtpserver nvarchar(120)
,@smtpauthenticate nvarchar(120)
,@smtpserverport nvarchar(120)
,@sendusername nvarchar(120)
,@sendpassword nvarchar(120)
,@From nvarchar(120)
,@To nvarchar(120)
,@Subject nvarchar(120)=" "
,@Body nvarchar(4000) =" "
,@attachment nvarchar(400) = null
/*********************************************************************
This stored procedure takes the above parameters and sends an e-mail.
All of the mail configurations are hard-coded in the stored procedure.
Reference to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp
***********************************************************************/

AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)

--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
if @hr <> 0
print 'CDO.Message Create failed'

--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
if @hr <> 0
print 'sendusing sp_OASetProperty failed'

-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @smtpserver
if @hr <> 0
print 'smtpserver sp_OASetProperty failed'

exec @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").value', @smtpauthenticate
if @hr <> 0
print 'smtpauthenticate sp_OASetProperty failed'

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").Value', @smtpserverport
if @hr <> 0
print 'smtpserverport sp_OASetProperty failed'

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value', @sendusername
if @hr <> 0
print 'sendusername sp_OASetProperty failed'

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value', @sendpassword
if @hr <> 0
print 'sendpassword sp_OASetProperty failed'



-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
if @hr <> 0
print 'Configuration.Fields.Update sp_OAMethod failed'

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
if @hr <> 0
print 'To sp_OASetProperty failed'
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
if @hr <> 0
print 'From sp_OASetProperty failed'
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
if @hr <> 0
print 'Subject sp_OASetProperty failed'

--adding an attachment: pwf
IF @attachment IS NOT NULL
EXEC @hr = sp_OAMethod @iMsg,'AddAttachment', NULL, @attachment
if @hr <> 0
print 'AddAttachment sp_OASetProperty failed'


-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
if @hr <> 0
print 'TextBody sp_OASetProperty failed'

EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
if @hr <> 0
print 'Send sp_OASetProperty failed'


-- Sample error handling.
IF @hr <>0
BEGIN
    EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
    IF @hr = 0
    BEGIN
        SELECT @output = ' Source: ' + @source
        PRINT @output
        SELECT @output = ' Description: ' + @description
        PRINT @output
    END
ELSE
    BEGIN
        print ' sp_OAGetErrorInfo failed.'
        RETURN
    END
END

-- Do some error handling after each step if you need to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

Above code snippet will help you to send an email from SQL Server.

Happy Coding!!