'NULL'附近的语法不正确。 SQL Server 2012中出错 [英] Incorrect syntax near 'NULL'. Error in SQL server 2012

问题描述

在下面的代码中，我在SQL Server 2012中收到错误

消息102，级别15，状态1，行4

'NULL'附近的语法不正确。 />

  SELECT  TBLTRANSUSERS.FirstName + ' ' + TBLTRANSUSERS.LastName + ' （' + TBLTRANSUSERS.UserCode + ' ）'  as  ' 用户名'，TBLTRANSJOBS.JobName  as  ' 作业名称，TBLTRANSDOCUMENTS.DocName  as  ' 贷款名称'，转换 （ Varchar （ 25 ），AssignedDate， 131 ） as  ' 分配日期'，
（ SELECT   RIGHT （'  0' + CAST（Datediff（第二，AssignedDate，GetDate（））/  3600   AS   VARCHAR ）， 2 ）+ ' ：'  +  RIGHT （'  0' + CAST（（Datediff（第二，AssignedDate，GetDate（））/  60 ）％ 60  < span class =code-keyword> AS   VARCHAR ）， 2 ）+  ' ：' +  RIGHT （'  0' + CAST（Datediff（second，AssignedDate，GetDate（））％ 60   AS   VARCHAR ）， 2 ） as  '  Elapsed Time' 
  FROM （（TBLTRANSDOCUMENTS 内部  join  TBLTRANSJOBS  ON  TBLTRANSJOBS.JobID = TBLTRANSDOCUMENTS.JobID）内部 加入 TBLTRANSUSERS   TBLTRANSDOCUMENTS.ExtractionAssigned = TBLTRANSUSERS.UserID） WHERE  KeyedDate 是  NULL  和 
 ExtractionAssigned   不  NULL  

< b>我尝试了什么：



没有解决方案出现上述问题。

解决方案

我们无法解决这个问题 - 我们无法访问您的数据，这可能与问题非常相关。



因此，首先将其削减 - 将其削减到最小，然后查看它是否有效并提供正确的数据。

也许就是这样：

  SELECT  FirstName，LastName，UserCode，TBLTRANSJOBS.JobName，TBLTRANSDOCUMENTS.DocName，AssignedDate 
  FROM  TBLTRANSDOCUMENTS 
 内部  join  TBLTRANSJOBS  ON  TBLTRANSJOBS.JobID = TBLTRANSDOCUMENTS.JobID 

然后慢慢添加位，直到问题再次出现。

当它出现时，请查看在你添加的内容中，看看它会如何影响数据。



但是我们不能为你做任何事情！

< blockquote>将代码格式化为相关块是一个好主意 - 它有助于找到未闭合/错误嵌套的代码块...

在你的情况下有一个缺失的）（结束括号）在结尾，在IS NOT NULL之后...

除了解决方案1和2中的有效注释之外，还有一些建议...



1.将输出的格式保留到UI层而不是数据层......所以不要使用

 TBLTRANSUSERS.FirstName + ' ' + TBLTRANSUSERS.LastName + ' （' + TBLTRANSUSERS.UserCode + ' ）'  as  '  用户名' 

只需返回所需的所有部件，然后让业务或UI层对其进行格式化。即

 TBLTRANSUSERS.FirstName，TBLTRANSUSERS.LastName，TBLTRANSUSERS.UserCode 

这样做的直接好处是您的代码可以在不想连接字段的情况下重复使用。它也将更有效地运行。



2.特别是不要将日期转换为SQL中的字符串。非常低效，这也意味着调用代码必须重新进行数据转换。

 转换（ Varchar （ 25 ），AssignedDate， 131 ） as   ' 指定日期'，

应该只是

 AssignedDate  as  ' 指定日期'，

3。尽量避免在字段名称中使用空格的习惯（分配日期，用户名）。这是一个真正的PITA必须使用方括号来引用列名称，并且可能导致难以发现的错误被引入到您的代码中。



4.保持一致！您已使用表名明确地标识了许多列除了 AssignedDate ...哪个表来自哪个？ WHERE子句中的列也是如此。您可能还想考虑为表格使用简短的ALIAS名称 - 它会节省一些打字，也可以使事情更清晰



5.这

（
  SELECT   RIGHT （'   0' + CAST（Datediff（第二名，AssignedDate，GetDate（））/  3600  < span class =code-keyword> AS   VARCHAR ）， 2 ）+ ' ： + 
  RIGHT （ '  0' + CAST（（Datediff（second，AssignedDate，GetDate（））/  60 ）％ 60   AS   VARCHAR ）， 2 ）+ ' ：' + 
  RIGHT （'  0' + CAST（Datediff（second，AssignedDate，GetDate（））％ 60   AS   VARCHAR ）， 2  
） as  '  Elapsed Time' 

真的很糟糕，但无论如何都属于UI层。如果你真的必须以秒为单位返回时间差。



尝试应用这些更改并比较SQL事件探查器的返回值（运行SQL Server Profiler | Microsoft Docs [ ^ ]此外还有关于CodeProject的文章）

In following code I am getting Error in SQL Server 2012
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'NULL'.

SELECT TBLTRANSUSERS.FirstName + ' ' + TBLTRANSUSERS.LastName + '(' + TBLTRANSUSERS.UserCode + ')' as 'User Name',TBLTRANSJOBS.JobName as 'Job Name', TBLTRANSDOCUMENTS.DocName as 'Loan Name',Convert(Varchar(25),AssignedDate,131) as 'Assigned Date',
(SELECT RIGHT('0' + CAST(Datediff(second,AssignedDate,GetDate()) / 3600 AS VARCHAR),2) + ':' + RIGHT('0' + CAST((Datediff(second,AssignedDate,GetDate()) / 60) % 60 AS VARCHAR),2) + ':' + RIGHT('0' + CAST(Datediff(second,AssignedDate,GetDate()) % 60 AS VARCHAR),2) as 'Elapsed Time'
FROM ((TBLTRANSDOCUMENTS inner join TBLTRANSJOBS ON TBLTRANSJOBS.JobID = TBLTRANSDOCUMENTS.JobID) inner join TBLTRANSUSERS on TBLTRANSDOCUMENTS.ExtractionAssigned=TBLTRANSUSERS.UserID) WHERE KeyedDate is NULL and 
ExtractionAssigned is not NULL

What I have tried:

No Solutions are coming in the above problem.

解决方案

We can't solve that for you - we don't have any access to your data and that's probably going to be very relevant to the problem.

So start by cutting it down - rip it to the minimum, and see if it works and provides the right data.
Perhaps just this:

SELECT FirstName, LastName, UserCode, TBLTRANSJOBS.JobName, TBLTRANSDOCUMENTS.DocName, AssignedDate
FROM TBLTRANSDOCUMENTS 
inner join TBLTRANSJOBS ON TBLTRANSJOBS.JobID = TBLTRANSDOCUMENTS.JobID

Then slowly add bits back until the problem reoccurs.
When it does, look at what you added and see how that would affect the data.

But we can't do any of that for you!

Formatting your code to relevant block is a good idea - it helps to find unclosed/wrongly nested code blocks...
In your case there is a missing ) (closing bracket) at the end, after IS NOT NULL...

Further to the valid comments in solutions 1 and 2 here is some more advice...

1. Leave the formatting of output to the UI layer and not the data layer ... so instead of

TBLTRANSUSERS.FirstName + ' ' + TBLTRANSUSERS.LastName + '(' + TBLTRANSUSERS.UserCode + ')' as 'User Name'

just return all the parts required and let the business or UI layer format it accordingly. I.e.

TBLTRANSUSERS.FirstName, TBLTRANSUSERS.LastName, TBLTRANSUSERS.UserCode

The immediate benefit of this is that your code can be reused for cases where you don't want to concatenate the fields. It will also run more efficiently.

2. Especially do not convert dates to strings in your SQL. Very inefficient and it also means the calling code has to do a data conversion back to date.

Convert(Varchar(25),AssignedDate,131) as 'Assigned Date',

should just be

AssignedDate as 'Assigned Date',

3. Try to get out of the habit of using spaces in field names ('Assigned Date', 'User Name'). It's a real PITA having to use square brackets to refer the column name and can lead to hard-to-find errors being introduced into your code.

4. Be consistent! You have used Table Names to unambiguously identify many of your columns except for AssignedDate ... which table is that coming from? The same is true of the columns in the WHERE clause. You might also want to consider using short ALIAS names for tables - it will save some typing and can also make things a little clearer

5. This

(
	SELECT RIGHT('0' + CAST(Datediff(second,AssignedDate,GetDate()) / 3600 AS VARCHAR),2) + ':' + 
		RIGHT('0' + CAST((Datediff(second,AssignedDate,GetDate()) / 60) % 60 AS VARCHAR),2) + ':' + 
		RIGHT('0' + CAST(Datediff(second,AssignedDate,GetDate()) % 60 AS VARCHAR),2
) as 'Elapsed Time'

is truly awful but in any event belongs in the UI layer. At most return the time difference in seconds if you really must.

Try applying these changes and compare the return from the SQL Profiler (Run SQL Server Profiler | Microsoft Docs[^] plus there are articles here on CodeProject)

这篇关于'NULL'附近的语法不正确。 SQL Server 2012中出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！