动态SQL问题
动态SQL问题
提问于 2012-11-30 22:22:14
我有一个动态SQL，它位于存储过程中，但是当我运行存储过程时，我看不到任何结果。这非常奇怪，因为当我从字符串中剥离SQL，并将其作为SQL查询运行时，我确实得到了结果。我已经尝试打印出动态SQL，这样我就可以看到发生了什么，但这也不起作用。因此，我不知道我做错了什么，我想问一下，是否有人能看到哪里错了。以下是查询：
SELECT @SQL = @SQL + 'Select Production_Site, CSN, Target, Action, Fail '
SELECT @SQL = @SQL + 'From syn_products prod, '
SELECT @SQL = @SQL + '(select Production_Site, CSN, SUM([Target]) AS Target,SUM([Action]) AS Action,SUM([Fail]) AS Fail '
SELECT @SQL = @SQL + ' from '
SELECT @SQL = @SQL + ' ( '
SELECT @SQL = @SQL + ' select Production_Site, value, Period, YEAR, week, CSN '
SELECT @SQL = @SQL + ' from t_Pqe_Grocery '
SELECT @SQL = @SQL + ' unpivot ( '
SELECT @SQL = @SQL + ' value '
SELECT @SQL = @SQL + ' for col in (Grocery_Packaging_And_Coding, Grocery_Measurable, '
SELECT @SQL = @SQL + ' Grocery_Appearance, Grocery_Aroma, '
SELECT @SQL = @SQL + ' Grocery_Flavour, Grocery_Texture)) unp '
SELECT @SQL = @SQL + ' ) src '
SELECT @SQL = @SQL + ' pivot '
SELECT @SQL = @SQL + ' ( '
SELECT @SQL = @SQL + ' count(value) '
SELECT @SQL = @SQL + ' for value in ([Target], [Action], [Fail]) '
SELECT @SQL = @SQL + ' ) piv '
SELECT @SQL = @SQL + ' where Production_Site IN ( ''' + @Site + ''') AND YEAR BETWEEN ' + CONVERT(varchar(50),CONVERT(BIGINT,@ToYear))+ 'AND '+ CONVERT(varchar(50),CONVERT(BIGINT,@FromYear))+ 'AND Period BETWEEN ' + CONVERT(varchar(50),CONVERT(BIGINT,@ToPeriod))+ ' AND '+ CONVERT(varchar(50),CONVERT(BIGINT,@FromPeriod))+ 'AND Week BETWEEN ' + CONVERT(varchar(50),CONVERT(BIGINT,@ToWeek))+ ' AND '+CONVERT(varchar(50),CONVERT(BIGINT,@FromWeek))+ ' GROUP BY Production_Site CSN'
SELECT @SQL = @SQL + ' ) pit'
SELECT @SQL = @SQL + ' WHERE prod.pProductCode = pit.CSN AND prod.pPowerBrand = ''POW'''
EXECUTE(@SQL)回答 3
Stack Overflow用户
回答已采纳
发布于 2012-11-30 23:05:33
有时，以不同的方式设置查询格式有助于查找查询中的任何错误。您的查询字符串中缺少一些空格：
declare @sql varchar(max)
declare @Site varchar(10) = 'testSite'
declare @ToYear int = 2010
declare @FromYear int = 2012
declare @ToPeriod int = 45
declare @FromPeriod int = 56
declare @ToWeek int = 10
declare @FromWeek int = 1
SET @SQL =
'Select Production_Site, CSN, Target, Action, Fail
From syn_products prod
inner join
(
select Production_Site, CSN, SUM([Target]) AS Target,SUM([Action]) AS Action,SUM([Fail]) AS Fail
from
(
select Production_Site, value, Period, YEAR, week, CSN
from t_Pqe_Grocery
unpivot
(
value
for col in (Grocery_Packaging_And_Coding,
Grocery_Measurable, Grocery_Appearance,
Grocery_Aroma, Grocery_Flavour, Grocery_Texture)
) unp
) src
pivot
(
count(value)
for value in ([Target], [Action], [Fail])
) piv
where Production_Site IN ( ''' + @Site + ''')
AND YEAR BETWEEN ' + CONVERT(varchar(50),CONVERT(BIGINT,@ToYear))+ ' AND '+ CONVERT(varchar(50),CONVERT(BIGINT,@FromYear))
+ ' AND Period BETWEEN ' + CONVERT(varchar(50),CONVERT(BIGINT,@ToPeriod))+ ' AND '+ CONVERT(varchar(50),CONVERT(BIGINT,@FromPeriod))
+ ' AND Week BETWEEN ' + CONVERT(varchar(50),CONVERT(BIGINT,@ToWeek))+ ' AND '+CONVERT(varchar(50),CONVERT(BIGINT,@FromWeek))
+ ' GROUP BY Production_Site CSN
) pit
on prod.pProductCode = pit.CSN
where prod.pPowerBrand = ''POW'''
select @sql现在正在打印-参见SQL Fiddle with Demo --我还将查询更改为使用ANSI join语法，而不是逗号分隔的join。
Stack Overflow用户
发布于 2012-11-30 22:28:04
这些可能是语法错误：
... CONVERT(BIGINT,@ToYear))+ 'AND '+ ...
^--- no space
... @FromYear))+ 'AND Period BETWEEN ...
^---no space
... @FromPeriod))+ 'AND Week BETWEEN
^-- yet again no spaceStack Overflow用户
发布于 2012-11-30 23:07:48
您的变量之一可能是NULL。将NULL值连接到字符串中将导致空字符串。当给定NULL字符串时，PRINT和EXECUTE均为..
首先，您需要将@SQL参数设置为空字符串，或者更改第一行以设置该值，而不是连接它。然后，您可能需要执行某种类型的检查，以验证参数是否为非空，如果参数为非空，则删除条件，或替换为其他条件：
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = ''
SELECT @SQL = @SQL + ... -- now build the SQL Statement
SELECT @SQL = @SQL + ' where Production_Site IN ( ''' + ISNULL(@Site, '') + ''' ... -- check for NULLs here
PRINT ISNULL(@SQL, 'NULL) -- this should now print something even if the SQL is NULL最后，要小心SQL注入攻击！避免将参数连接到像这样的动态SQL语句中。相反，应该参数化动态SQL，并将参数与EXECUTE语句一起传递。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/13647019
复制相关文章
相似问题
腾讯云开发者
