CODEWITHSUNDEEP
sqlsql-server-2012
G.S Bhangal
G.S Bhangal

Reputation: 3280

Stored Procedure Doesn't return required result

I have a table EmployeeAttendance and Users and when i try to get the records using the below query and passing null value to the @userName parameter it doesn't return any records...there might be some problem in the query...

Can any one help me on this

 declare @month int =12, 
@year int=1,
@orgID int=1,
@userName nvarchar(100) = null;

DECLARE  @lastDay int;

-- calculations
DECLARE @startDate datetime, @endDate datetime
SET @startDate =  convert(varchar, @year) + '-' + convert(varchar, @month) + '-1'
set @endDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@startDate)+1,0))
set @lastDay =  day(@endDate) --last day of month

declare @day int
set @day = 2


declare @days varchar(max)
set @days = '[1]'
WHILE (@day <= @lastDay)
BEGIN
   set @days = @days + ',[' + convert(varchar, @day) + ']'
   set @day = @day + 1
END

declare @query varchar(max)

set @query = '
SELECT USerID,Name,' + @days  +
'
FROM
(

SELECT u.ID as UserID, u.FirstName + '' ''' + ' ' + ' + u.LastName as Name, InTime,Outtime,isPresent, day(Date) as day FROM users u
LEFT join EmployeeAttendance e on e.USerID = u.ID AND [Date] between '''+ cast( @startDate as varchar)+' '' and '''+ cast( @endDate as varchar)+' '' 
left join Employee emp on emp.UserID= u.ID

where emp.OrganisationID =' +cast( @orgID as varchar)+'

and(u.UserName like ''%'+@userName+  '%'' OR '''+@userName+''' IS NULL)

) AS SourceTable
PIVOT
(
MAX(InTime)
FOR day IN ( ' + @days + ')' + '
) AS PivotTable


;'

print @query;

exec(@query);





GO

And when i pass the @userName with some particular string then it returns correct results but if i pass the null value to the @userName then it return the nothing.

So i want, when i pass null value to @userName it return all the results.

Upvotes: 0

Views: 66

Answers (1)

PeterRing
PeterRing

Reputation: 1797

Oh yes. There is a problem. If you ad null to a string the whole string will be null. 'abc'+null = null

Dont declare it to null 

@userName nvarchar(100) = '';

And change

(u.UserName like ''%'+@userName+  '%'' OR '''+@userName+''' ='''' )

Upvotes: 1

Related Questions