子查询中无法识别功能变量 [英] Function variable is not recognize in subquery

问题描述

我想编写一个对字段的非null和非空条目进行计数的函数.我的问题是查询没有运行，因为在select语句中无法识别@tableName变量，而且我也不知道为什么

 创建函数dbo.getCount(@cod int，@columnName作为varchar(20)，@tableName作为varchar(20))返回int为开始-计算所有填写的条目返回(从@tableName中选择COUNT(*)其中@columnName<>''和@columnName不为null)结尾;去 

解决方案

如评论中所述，但重申一下，因为我将在此答案后将其删除:

由于多种原因，您不能使用某个函数执行此操作. SELECTCOUNT(*)FROM @TableName 表示计数表变量 @TableName 不是名称为 @TableName 的值的表. WHERE @ColumnName<>''表示标量变量的值没有值''， not ，其中名称为 @ColumnName 的列(在上述表中)没有值''.

您不能在执行此类操作的功能中执行此操作，您需要动态SQL；而且您不能在函数中使用动态SQL(因为不能使用 EXEC 命令).

您可以，但是，可以使用存储过程进行此操作:

  CREATE PROC dbo.GetCount @SchemaName系统名称= N'dbo'，@ TableName系统名称，@ ColumnName系统名称，@ Count int输出AS开始宣告@SQL nvarchar(MAX)，@CRLF nchar(2)= NCHAR(13)+ NCHAR(10);SELECT @SQL = N'SELECT @Count = COUNT(NULLIF('+ QUOTENAME(c.[name])+ N'，''''))'+ @CRLF +N'FROM'+ QUOTENAME(s.name)+ N'.+ QUOTENAME(t.[name])+ N';'来自sys.schemas加入系统表t ON s.schema_id = t.schema_id加入sys.columns c ON t.object_id = c.object_idWHERE s.[名称] = @SchemaNameAND t.[名称] = @TableNameAND c.[名称] = @ColumnName;--PRINT @SQL;-您的调试朋友EXEC sp_executesql @SQL，N'@ Count int OUTPUT'，@Count OUTPUT;结尾去 

然后您像下面那样运行SP(带有示例表):

  CREATE TABLE dbo.TestTable(SomeColumn varchar(10));插入dbo.TestTable(SomeColumn)VALUES('')，('abc')，(NULL);去DECLARE @Count int;EXEC dbo.GetCount @TableName = N'TestTable'，@ColumnName = N'SomeColumn'，@Count = @Count输出;SELECT @Count;-返回1去 

DB<>小提琴

I want to write a function that counts non null and non empty entries of a field. My problem is that the query does not run since the @tableName variable is not recognized in the select statement and I do not know why

create function dbo.getCount(@cod int, @columnName as varchar(20), @tableName as varchar(20))
Returns int as 
Begin

--Count all filled entries
Return (select COUNT(*) from @tableName
where @columnName <> '' and @columnName is not null)

End;
go 

解决方案

As mentioned in the comments, but to reiterate, as I'll delete them after this answer:

You can't do this with a function, for multiple reasons. SELECT COUNT(*) FROM @TableName means count the number of rows in the table variable @TableName not the table who's name is the value of @TableName. WHERE @ColumnName <> '' would mean where the value of the scalar variable doesn't have the value '', not where the column (in the aforementioned table) with the name of value of @ColumnName doesn't have the value ''.

And you can't do this in a function as to do this type of thing, you need dynamic SQL; and you can't use dynamic SQL in a function (as you can't use the EXEC command).

You can, however, do this with a Stored Procedure:

CREATE PROC dbo.GetCount @SchemaName sysname = N'dbo', @TableName sysname, @ColumnName sysname, @Count int OUTPUT AS
BEGIN

    DECLARE @SQL nvarchar(MAX),
            @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

    SELECT @SQL = N'SELECT @Count = COUNT(NULLIF(' + QUOTENAME(c.[name]) + N',''''))' + @CRLF +
                  N'FROM ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.[name]) + N';'
    FROM sys.schemas s
         JOIN sys.tables t ON s.schema_id = t.schema_id
         JOIN sys.columns c ON t.object_id = c.object_id
    WHERE s.[name] = @SchemaName
      AND t.[name] = @TableName
      AND c.[name] = @ColumnName;
    --PRINT @SQL; --Your debugging friend

    EXEC sp_executesql @SQL, N'@Count int OUTPUT', @Count OUTPUT;

END
GO

And you run the SP like below (with sample table):

CREATE TABLE dbo.TestTable (SomeColumn varchar(10));
INSERT INTO dbo.TestTable (SomeColumn)
VALUES(''),('abc'),(NULL);
GO


DECLARE @Count int;
EXEC dbo.GetCount @TableName = N'TestTable', @ColumnName = N'SomeColumn', @Count = @Count OUTPUT;

SELECT @Count; --Returns 1
GO

DB<>Fiddle

这篇关于子查询中无法识别功能变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！