what is the Error in sql query

Question

4.00/5 (1 vote)

See more:

SQL-Server-2008

hello everyone,

Please tell me what is the error in below sql query..

declare @userid int=null
declare @complaintstatusid int=null
declare @fromdate date=null
declare @todate date =null
declare @month date=null



DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders = 
  COALESCE(
    @PivotColumnHeaders + ',[' + cast(AssetType as varchar) + ']',
   '[' + cast(AssetType as varchar)+ ']'
  )
FROM [MH.AssetType_Master] A where isdeleted ='false'
--select @PivotColumnHeaders 

DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
select * from 
(
select S.SchoolName, A.AssetType, S.SchoolId,um.Username +'' ''+um.Surname as Name,csm.ComplaintStatus
from [MH.Complaints] C 
inner join [Mh.AssetType_Master] A 
on A.AssetTypeId = C.AssetTypeID 
inner join [Mh.school_Master] S
 on S.SchoolId = C.SchoolID
 inner join [MH.UserSchoolAssociation] usa
 on S.SchoolId=usa.SchoolId
 inner join [MH.User_Master] um
 on usa.UserId=um.LoginId
 inner join [MH.ComplaintStatus_Master] csm
 on  C.ComplaintStatusId=csm.ComplaintStatusId
 
 
where Month(c.Reporteddate)= coalesce('+CONVERT(nvarchar(max),Month(@month))+',Month(c.Reporteddate))


) PivotData
Pivot
(
  COUNT(SchoolId)
    FOR AssetType IN (
      ' + @PivotColumnHeaders + '
    )
  ) AS PivotTable
'
select @PivotTableSQL 
--EXECUTE(@PivotTableSQL)

when i select @pivottablesql query it returns me NULL

Please help me ..

Regards,
Krunal

Posted 6-Sep-12 2:20am

krunal25

Add a Solution

Comments

ZurdoDev 6-Sep-12 8:23am

Then something you are concatenating, like @PivotColumnHeaders, is likely null. NULL + anything is still NULL.

[no name] 6-Sep-12 8:26am

Hi ryanb31,

Thanks .. it worked , but please let me know how this query could be executed if i pass null values i.e. no values to the parameters.. then it should return all the records..

Please help...

Regards,
Krunal

3 solutions

Solution 2

You can use Isnull keyword, You can decide what to decide if null value comes.

SQL

isnull(CONVERT(nvarchar(max),Month(@month)),'')

In the above code, i just pass empty string('') if its null.
am

SQL

declare @userid int=null
declare @complaintstatusid int=null
declare @fromdate date=null
declare @todate date =null
declare @month date=null



DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders =
  COALESCE(
    @PivotColumnHeaders + ',[' + cast(Name as varchar) + ']',
   '[' + cast(Name as varchar)+ ']'
  )
FROM employee A
--select @PivotColumnHeaders


DECLARE @PivotTableSQL NVARCHAR(MAX)
select @PivotTableSQL = 'select * from
(
select S.SchoolName, A.AssetType, S.SchoolId,um.Username +'' ''+um.Surname as Name,csm.ComplaintStatus
from [MH.Complaints] C
inner join [Mh.AssetType_Master] A
on A.AssetTypeId = C.AssetTypeID
inner join [Mh.school_Master] S
 on S.SchoolId = C.SchoolID
 inner join [MH.UserSchoolAssociation] usa
 on S.SchoolId=usa.SchoolId
 inner join [MH.User_Master] um
 on usa.UserId=um.LoginId
 inner join [MH.ComplaintStatus_Master] csm
 on  C.ComplaintStatusId=csm.ComplaintStatusId


where Month(c.Reporteddate)= coalesce('+isnull(CONVERT(nvarchar(max),Month(@month)),'null')+',Month(c.Reporteddate))


) PivotData
Pivot
(
  COUNT(SchoolId)
    FOR AssetType IN (
      ' + @PivotColumnHeaders + '
    )
  ) AS PivotTable
'
select @PivotTableSQL

Posted 6-Sep-12 2:41am

Santhosh Kumar Jayaraman

Updated 6-Sep-12 3:01am

v3

Comments

[no name] 6-Sep-12 8:50am

HI santhosh,

It is not working... it is giving output as...

<pre>
select * from ( select S.SchoolName, A.AssetType, S.SchoolId,um.Username +' '+um.Surname as Name,csm.ComplaintStatus,c.reporteddate as ReportedDate from [MH.Complaints] C inner join [Mh.AssetType_Master] A on A.AssetTypeId = C.AssetTypeID inner join [Mh.school_Master] S on S.SchoolId = C.SchoolID inner join [MH.UserSchoolAssociation] usa on S.SchoolId=usa.SchoolId inner join [MH.User_Master] um on usa.UserId=um.LoginId inner join [MH.ComplaintStatus_Master] csm on C.ComplaintStatusId=csm.ComplaintStatusId where Month(c.Reporteddate)= coalesce(,Month(c.Reporteddate)) ) PivotData Pivot ( COUNT(SchoolId) FOR AssetType IN ( [Content],[CPU],[Database],[Furniture],[Hardware],[Inquiry],[Laptop],[LMS],[Miscellaneous],[Network],[Operating System],[Printer],[Projector],[RF device & Remote],[Server],[SMPS],[Software],[UPS] ) ) AS PivotTable
</pre>

Santhosh Kumar Jayaraman 6-Sep-12 8:54am

i believe you reported that you are getting output as null, but i hope now the query is coming..Then what you want now?which one is not working?

[no name] 6-Sep-12 8:56am

Hi Santhosh,

Actually now i am executing this query using execute(@PivotTableSQL), but the problem is near coalesece .... where Month(c.Reporteddate)= coalesce(,Month(c.Reporteddate))
You could see that it is coalesce(,Month(c.Reporteddate)) instead it should be colaesce(NULL,Month(c.Reporteddate))

Please help .. and thanks for your prompt response..

Regards,
Krunal

Santhosh Kumar Jayaraman 6-Sep-12 8:57am

i didnt understand . What you want to fetch if @month is null

[no name] 6-Sep-12 9:00am

I want all the records in my table if the @month is null..and if the parameter for @month is something like 2012-08-02 then it will fetch all the records of August month else it will fetch records of all the month.

I hope you understood...

Santhosh Kumar Jayaraman 6-Sep-12 9:02am

I have updated for your previous thing. Check my solution now it ll be colaesce(NULL,Month(c.Reporteddate))

[no name] 6-Sep-12 9:04am

Thanks a lot... it worked awesome....Thank you very much.....

Santhosh Kumar Jayaraman 6-Sep-12 9:07am

Check my another solution..it will fetch you allrecords if you pass@month is null and if you pass some month it willfetch only that month details.

Solution 1

Hello

check below code line which is written

SQL

where Month(c.Reporteddate)= coalesce('+CONVERT(nvarchar(max),Month(@month))+ ',Month(c.Reporteddate))

in above code you are using @month variable and you define then variable as a null like as below and you never assign that variable if you assigned value to @month variable it will work fine

SQL

declare @month date=null

Example

SQL

where Month(c.Reporteddate)= coalesce('+CONVERT(nvarchar(max),Month(10))+',Month(c.Reporteddate))

Posted 6-Sep-12 2:34am

Dhol Gaurav

Comments

[no name] 6-Sep-12 8:37am

Hi Dhol Gaurav,

I understood, and thanks for replying.. but i need the result even if the parameters are blank or null..

Please help...

Regards,
Krunal

Santhosh Kumar Jayaraman 6-Sep-12 8:42am

check my solution.

Dhol Gaurav 6-Sep-12 8:43am

Hello try below below query it might help you

where Month(c.Reporteddate)= coalesce('+ CASE WHEN @month IS NULL THEN 'NULL' ELSE CONVERT(nvarchar(max),Month(@month)) END+',Month(c.Reporteddate))

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Santhosh Kumar Jayaraman · Accepted Answer · 2012-09-06T03:07:00

check this, if @month is null, am excluding where clause..So this will fetch you all records if you dont pass any month

SQL

declare @userid int=null
declare @complaintstatusid int=null
declare @fromdate date=null
declare @todate date =null
declare @month date=null



DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders =
  COALESCE(
    @PivotColumnHeaders + ',[' + cast(AssetType as varchar) + ']',
   '[' + cast(AssetType as varchar)+ ']'
  )
FROM AssetType_master A
--select @PivotColumnHeaders


DECLARE @PivotTableSQL NVARCHAR(MAX)
select @PivotTableSQL = 'select * from
(
select S.SchoolName, A.AssetType, S.SchoolId,um.Username +'' ''+um.Surname as Name,csm.ComplaintStatus
from [MH.Complaints] C
inner join [Mh.AssetType_Master] A
on A.AssetTypeId = C.AssetTypeID
inner join [Mh.school_Master] S
 on S.SchoolId = C.SchoolID
 inner join [MH.UserSchoolAssociation] usa
 on S.SchoolId=usa.SchoolId
 inner join [MH.User_Master] um
 on usa.UserId=um.LoginId
 inner join [MH.ComplaintStatus_Master] csm
 on  C.ComplaintStatusId=csm.ComplaintStatusId'
 if CONVERT(nvarchar(max),Month(@month)) is not null
 select @PivotTableSQL=@PivotTableSQL+' where Month(c.Reporteddate)= coalesce('+CONVERT(nvarchar(max),Month(@month))+',Month(c.Reporteddate))'

set @PivotTableSQL=@PivotTableSQL+') PivotData
Pivot
(
  COUNT(SchoolId)
    FOR AssetType IN (
      ' + @PivotColumnHeaders + '
    )
  ) AS PivotTable
'
print @PivotTableSQL

what is the Error in sql query

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us