动态SQL中的变量@DEPT使用

[vbsnake]

DECLARE @DEPT VARCHAR(10)
DECLARE @SQL VARCHAR(8000)
SET @SQL=''
SET @DEPT='A'
SELECT @SQL= @SQL+ ','+quotename(B.NAME,'''')+'=isnull(sum(CASE CODE when '+quotename(A.CODE,'''')+' THEN NQTY END),0)' FROM T A(NOLOCK),CODENAME B(NOLOCK)
WHERE A.CODE = B.CODE

set @SQL = 'select DEPT,PKG,LOT'+@SQL+',TOTAL=sum(NQTY) from T
WHERE DEPT LIKE RTRIM('+@DEPT+')+''%''
Group by DEPT,PKG,LOT with rollup'
exec(@SQL)

1.以上语句中 WHERE DEPT LIKE RTRIM('+@DEPT+')+''%''有误,不使用rtrim的时候就没问题，该如何处理呢？
2.如何最终查询结果集的return

[rocklabzhang]

mark

[子陌红尘]

DECLARE @DEPT VARCHAR(10)
DECLARE @SQL VARCHAR(8000)
SET @SQL=''
SET @DEPT='A'
SELECT @SQL= @SQL+ ','+quotename(B.NAME,'''')+'=isnull(sum(CASE CODE when '+quotename(A.CODE,'''')+' THEN NQTY END),0)' FROM T A(NOLOCK),CODENAME B(NOLOCK)
WHERE A.CODE = B.CODE

set @SQL = 'select DEPT,PKG,LOT'+@SQL+',TOTAL=sum(NQTY) from T
WHERE DEPT LIKE RTRIM('''+@DEPT+''')+''%''
Group by DEPT,PKG,LOT with rollup'
exec(@SQL)

[子陌红尘]

如果在存储过程中执行该动态SQL语句，直接从存储过程结果集中获取这个查询的返回。

[zjcxc]

DECLARE @DEPT VARCHAR(10)
DECLARE @SQL VARCHAR(8000)
SET @SQL=''
SET @DEPT='A'
SELECT @SQL= @SQL+ ','+quotename(B.NAME,'''')+'=isnull(sum(CASE CODE when '+quotename(A.CODE,'''')+' THEN NQTY END),0)' FROM T A(NOLOCK),CODENAME B(NOLOCK)
WHERE A.CODE = B.CODE

set @SQL = 'select DEPT,PKG,LOT'+@SQL+',TOTAL=sum(NQTY) from T
WHERE DEPT LIKE '''+RTRIM(@DEPT)+'%''
Group by DEPT,PKG,LOT with rollup'
exec(@SQL)

[zjcxc]

RTRIM不应该放在动态语句中

[子陌红尘]

用exec()执行的动态SQL语句返回的结果集跟普通SQL语句执行返回的结果集一样调用。

[vbsnake]

如果增加 DECLARE @DTE DATETIME
WHERE DEPT LIKE '''+RTRIM(@DEPT)+'%''
改成类似于
WHERE DEPT LIKE '''+RTRIM(@DEPT)+'%'' and MDATE>=@DTE
怎么写啊

[vbsnake]

用exec()执行的动态SQL语句返回的结果集跟普通SQL语句执行返回的结果集一样调用。


是不是直接写return 阿？？？