“不正确的语法"在 T-SQL 查询的 SELECT 子句中使用表值函数 [英] "Incorrect syntax" using a table-valued function in SELECT clause of T-SQL query

问题描述

我有以下用于拆分字符串的表值函数.这工作正常，是从网络上的其他地方抄袭的，我认为这不是问题的原因，但在相关的情况下包括在内:

CREATE FUNCTION dbo.StringSplit (@sep char(1), @s varchar(512))退货表作为返回 (WITH Pieces(pn, start, stop) AS (SELECT 1, 1, CHARINDEX(@sep, @s)联合所有SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)从件哪里停止 >0)选择 pn,SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s从件)走

这将启用以下行:

SELECT * FROM dbo.StringSplit('.', 'this.is.a.string.to.split')

生产

<前>PN |秒----+--------1 |这个2 |是3 |一种4 |细绳5 |到6 |分裂

我的目标是从包含大量文件名的表中获取文件扩展名列表.为此，我使用了上面的表值函数来拆分每个文件名:

SELECT文档 ID，doc_file_name，(SELECT TOP 1 s FROM dbo.StringSplit('.', doc_file_name) ORDER BY pn DESC) AS 扩展从ai_docs_core

让 SQL Server 企业管理器检查语法，在包含该函数的行上给我一个语法错误:

<前>消息 102，级别 15，状态 1，第 34 行doc_file_name"附近的语法不正确.

以下没有做我需要的(显然，因为它使用了一个集合变量)，但它不会导致语法错误:

DECLARE @foo VARCHAR(512) = 'my_filename.doc'选择文档 ID，doc_file_name，(SELECT TOP 1 s FROM dbo.StringSplit('.', @foo) ORDER BY pn DESC) AS 扩展从ai_docs_core

所以问题是:为什么使用特定的字段名作为函数的参数会导致语法错误，我该如何实现我想要的?

解决方案

我猜数据库设置为兼容模式 80 或更低.

参见 sp_dbcmptlevel

这通常是发生在 FROM 子句中的原因

你也可以这样写

SELECTa.doc_id,a.doc_file_name,扩展名从ai_docs_core a外敷(SELECT TOP 1 s AS 扩展FROM dbo.StringSplit('.', a.doc_file_name)按 pn DESC 排序) 富

I have the following table-valued function for splitting strings. This works fine, was cribbed from elsewhere on the web, and I would have thought isn't the cause of the problem but is included in case it's relevant:

CREATE FUNCTION dbo.StringSplit (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
  WITH Pieces(pn, start, stop) AS (
    SELECT 1, 1, CHARINDEX(@sep, @s)
    UNION ALL
    SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
    WHERE stop > 0
  )
  SELECT pn,
    SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
  FROM Pieces
)
GO

This enables the following line:

SELECT * FROM dbo.StringSplit('.', 'this.is.a.string.to.split')

to produce

 pn | s
----+--------
 1  | this
 2  | is
 3  | a
 4  | string
 5  | to
 6  | split

My aim in this is to obtain a list of file extensions from a table which contains a large number of filenames. To that end, I used the above table-valued function to split each filename:

SELECT
  doc_id,
  doc_file_name,
  (SELECT TOP 1 s FROM dbo.StringSplit('.', doc_file_name) ORDER BY pn DESC) AS extension
FROM
  ai_docs_core

Having SQL Server Enterprise Manager check the syntax on that gives me a syntax error on the line containing the function:

Msg 102, Level 15, State 1, Line 34
Incorrect syntax near 'doc_file_name'.

The following doesn't do what I need (obviously, because it's using a set variable), but it doesn't cause a syntax error:

DECLARE @foo VARCHAR(512) = 'my_filename.doc'
SELECT
  doc_id,
  doc_file_name,
  (SELECT TOP 1 s FROM dbo.StringSplit('.', @foo) ORDER BY pn DESC) AS extension
FROM
  ai_docs_core

So the question is: why does using a specific field name as a parameter to the function cause a syntax error, and how can I achieve what I want?

解决方案

I would guess that the database is set to compatibility mode 80 or lower.

See sp_dbcmptlevel

This is usually the cause if it happens in a FROM clause

You can also write it like this

SELECT
  a.doc_id,
  a.doc_file_name,
  foo.extension 
FROM
  ai_docs_core a
  OUTER APPLY
  (SELECT TOP 1 s AS extension
   FROM dbo.StringSplit('.', a.doc_file_name)
   ORDER BY pn DESC
  ) foo

这篇关于“不正确的语法"在 T-SQL 查询的 SELECT 子句中使用表值函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！