找不到为什么这是错误的 - 返回错误 [英] Can't find why this is incorrect - returns error

问题描述

我在网上找到了TSQL - 解决它你的方式 - 查找表中每列的NULL值的百分比，但是当我运行它时出现以下错误：



消息102，等级15，状态1，行20

'）'附近的语法不正确。



我找不到原因。

  SET   NOCOUNT   ON  
  DECLARE   @ Statement   NVARCHAR （MAX）= '  ' 
  DECLARE   @ Statement2   NVARCHAR （MAX）= ' ' 
  DECLARE   @ Fina lStatement   NVARCHAR （MAX）= ' '  
  DECLARE   @ TABLE_SCHEMA  SYSNAME = ' 生产' 
  DECLARE   @TABLE_NAME  SYSNAME = ' 产品' 
  SELECT  
  @ Statement  =  @ Statement  + '  SUM（CASE WHEN' + COLUMN_NAME + ' < span class =code-string> IS NULL THEN 1 ELSE 0 END）AS' + COLUMN_NAME + ' ，' +  CHAR （ 13 ），
  @ Statement2  =  @ Statement2  + COLUMN_NAME + '  * 100 / OverallCount AS' + COLUMN_NAME + ' ，' +  CHAR （ 13 ） FROM  
 INFORMATION_SCHEMA.COLUMNS 
  WHERE  TABLE_NAME =  @ TABLE_NAME   AND  TABLE_SCHEMA =  @ TABLE_SCHEMA  
  IF   @@ ROWCOUNT  =  0  
  RAISERROR （' 表或视图schema％s和名称％s不存在o你没有适当的权限。'， 16 ， 1 ， @ TABLE_SCHEMA ， @ TABLE_NAME ）
  ELSE  
  BEGIN  
  SELECT   @ FinalStatement  = 
 '  SELECT' +  LEFT （ @ Statement2 ，LEN（ @ Statement2 ）-2）+   FROM（SELECT' +  LEFT （ @ Statement ，LEN（ @ Statement ）-2）+ 
  ' ，COUNT（*）AS Over allCount FROM' +  @ TABLE_SCHEMA  + '  。' +  @ TABLE_NAME  + ' ） SubQuery' 
  EXEC （ @ FinalStatement ）

解决方案

你好dhMac



你错过了BEGIN的END。查看最后一行。

  SET   NOCOUNT   ON  
 
  DECLARE   @ Statement   NVARCHAR （MAX）= ' ' 
 
  DECLARE   @ Statement2   NVARCHAR （MAX）= ' ' 
 
  DECLARE   @ FinalStatement   NVARCHAR （MAX）= ' ' 
 
  DECLARE   @ TABLE_SCHEMA  SYSNAME = ' 生产' 
 
  DECLARE   @ TABLE_NAME  SYSNAME = '  Product' 
 
  SELECT  
 
  @ Statement  =  @ Statement  + '  SUM（CASE WHEN' + COLUMN_NAME + '  IS NULL THEN 1 ELSE 0 END）AS' + COLUMN_NAME + ' ，' +  CHAR （ 13 ），
 
  @ Statement2  =  @ Statement2  + COLUMN_NAME + '  * 100 / OverallCount AS' + COLUMN_NAME + ' ，' +  CHAR （ 13 ） FROM  
 
 INFORMATION_SCHEMA.COLUMNS 
 
  WHERE  TABLE_NAME =  @ TABLE_NAME   AND  TABLE_SCHEMA =  @ TABLE_SCHEMA  
 
  IF   @@ ROWCOUNT  =  0  
 
  RAISERROR （' 具有模式％s的表或视图和名称％s不存在，或者您没有适当的权限。' ， 16 ， 1 ， @ TABLE_SCHEMA ，  @ TABLE_NAME ）
 
  ELSE  
 
  BEGIN  
 
  SELECT   @ FinalStatement  = 
 
 ' < span class =code-string> SELECT' +  LEFT （ @ Statement2 ，LEN （ @ Statement2 ） -  2）+ '  FROM（SELECT ' +  LEFT （ @ Statement ，LEN（ @Statement ） -  2）+ 
 
 ' ，COUNT（*） AS OverallCount FROM' +  @ TABLE_SCHEMA  + ' 。' +  @ TABLE_NAME  + ' ）SubQuery' 
 
  EXEC  （ @ FinalStatement ）
 
  END  

I found this on the web under "TSQL - Solve it YOUR Way - Finding the percentage of NULL values for each column in a table" but when I run it I get the following error:

Msg 102, Level 15, State 1, Line 20
Incorrect syntax near ')'.

I can't find out why.

SET NOCOUNT ON
DECLARE @Statement NVARCHAR(MAX) = ''
DECLARE @Statement2 NVARCHAR(MAX) = ''
DECLARE @FinalStatement NVARCHAR(MAX) = ''
DECLARE @TABLE_SCHEMA SYSNAME = 'Production'
DECLARE @TABLE_NAME SYSNAME = 'Product'
SELECT
        @Statement = @Statement + 'SUM(CASE WHEN ' + COLUMN_NAME + ' IS NULL THEN 1 ELSE 0 END) AS ' + COLUMN_NAME + ',' + CHAR(13) ,
        @Statement2 = @Statement2 + COLUMN_NAME + '*100 / OverallCount AS ' + COLUMN_NAME + ',' + CHAR(13) FROM
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLE_NAME AND TABLE_SCHEMA = @TABLE_SCHEMA
IF @@ROWCOUNT = 0
        RAISERROR('TABLE OR VIEW with schema "%s" and name "%s" does not exists or you do not have appropriate permissions.',16,1, @TABLE_SCHEMA, @TABLE_NAME)
ELSE
BEGIN
        SELECT @FinalStatement =
                'SELECT ' + LEFT(@Statement2, LEN(@Statement2) -2) + ' FROM (SELECT ' + LEFT(@Statement, LEN(@Statement) -2) +
                ', COUNT(*) AS OverallCount FROM ' + @TABLE_SCHEMA + '.' + @TABLE_NAME + ') SubQuery'
        EXEC(@FinalStatement)

解决方案

Hi dhMac

You are missing the END for BEGIN. See the last line.

SET NOCOUNT ON

DECLARE @Statement NVARCHAR(MAX) = ''

DECLARE @Statement2 NVARCHAR(MAX) = ''

DECLARE @FinalStatement NVARCHAR(MAX) = ''

DECLARE @TABLE_SCHEMA SYSNAME = 'Production'

DECLARE @TABLE_NAME SYSNAME = 'Product'

SELECT

        @Statement = @Statement + 'SUM(CASE WHEN ' + COLUMN_NAME + ' IS NULL THEN 1 ELSE 0 END) AS ' + COLUMN_NAME + ',' + CHAR(13) ,

        @Statement2 = @Statement2 + COLUMN_NAME + '*100 / OverallCount AS ' + COLUMN_NAME + ',' + CHAR(13) FROM

INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @TABLE_NAME AND TABLE_SCHEMA = @TABLE_SCHEMA

IF @@ROWCOUNT = 0

        RAISERROR('TABLE OR VIEW with schema "%s" and name "%s" does not exists or you do not have appropriate permissions.',16,1, @TABLE_SCHEMA, @TABLE_NAME)

ELSE

BEGIN

   SELECT @FinalStatement =

                'SELECT ' + LEFT(@Statement2, LEN(@Statement2) -2) + ' FROM (SELECT ' + LEFT(@Statement, LEN(@Statement) -2) +

                ', COUNT(*) AS OverallCount FROM ' + @TABLE_SCHEMA + '.' + @TABLE_NAME + ') SubQuery'

        EXEC(@FinalStatement)

END

这篇关于找不到为什么这是错误的 - 返回错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！