Syntax help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Syntax help

Author

Topic

eljapo4
Posting Yak Master

100 Posts

Posted - 2012-08-02 : 09:08:45

hi, I've the following piece of SQL in a SP


SET @SQLStmt = 'SELECT TOP 100
M.AuthNumber, 
M.CallDate, 
M.ModelDescription, 						   
C.CustomerForename + '' '' + C.CustomerSurname As CustomerName, 
CASE WHEN C.CustomerStreet = '' THEN
C.CustomerVisitingStreet, C.CustomerVisitingPostcode + '' '' + C.CustomerVisitingTown
ELSE
C.CustomerStreet, C.CustomerPostcode + '' '' + C.CustomerTown 
END As CustomerPostcode,						   
CASE WHEN Status = 0 THEN ''Open'' ELSE ''Closed'' END As Status, 
ISNULL(E.Name,''-'') As Engineer, 
CASE WHEN RouteStatus = 0 THEN ''Not Assigned''
WHEN RouteStatus = 1 THEN ''Awaiting Part'' 
ELSE ''Assigned'' 
END As RouteStatus  
FROM dbo.tbl_Call_Record_Main M 
INNER JOIN dbo.tbl_Call_Record_Customers C ON C.AuthNumber = M.AuthNumber 
LEFT JOIN dbo.tbl_Engineers E ON M.EngineerID = E.EngineerID
WHERE ' + @SearchTerms + ' 
ORDER BY M.CallDate DESC '

I've added the section bold and now when I run my program I am getting an incorrect syntax message ( Incorrect syntax near ','.) I just want to check if C.CustomerStreet is blank.

Any help would be appreciated.

eljapo4
Posting Yak Master

100 Posts

Posted - 2012-08-02 : 09:33:47

realised I was trying the 1 case expression for 2 different fields. Updated code:


SET @SQLStmt = 'SELECT TOP 100 
   M.AuthNumber, 
   M.CallDate, 
   M.ModelDescription, 						   
   C.CustomerForename + '' '' + C.CustomerSurname As CustomerName, 
			   
   CASE WHEN C.CustomerStreet = '' THEN C.CustomerVisitingStreet ELSE C.CustomerStreet END AS CustomerStreet, 
   CASE WHEN C.CustomerPostcode = '' THEN C.CustomerVisitingPostcode + '' '' + C.CustomerVisitingTown
   ELSE C.CustomerPostcode + '' '' + C.CustomerTown END As CustomerPostCode,						   
						   
   CASE WHEN Status = 0 THEN ''Open'' ELSE ''Closed'' END As Status, 
   ISNULL(E.Name,''-'') As Engineer, 
   CASE WHEN RouteStatus = 0 THEN ''Not Assigned''
   WHEN RouteStatus = 1 THEN ''Awaiting Part'' 
   ELSE ''Assigned'' 
   END As RouteStatus  
   FROM dbo.tbl_Call_Record_Main M 
   INNER JOIN dbo.tbl_Call_Record_Customers C ON C.AuthNumber = M.AuthNumber 
   LEFT JOIN dbo.tbl_Engineers E ON M.EngineerID = E.EngineerID
   WHERE ' + @SearchTerms + ' 
   ORDER BY M.CallDate DESC '

EXEC(@SQLStmt)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-02 : 09:57:25

I hope serachterms will always have some value else above statement will fail

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources