print 没有结果
DECLARE @WHSCODE VARCHAR(20),@ITEMCODE VARCHAR(30),@Codebars varchar(30),@datefrom datetime,@dateto datetime
SET @DATEFROM='2010-01-01'
SET @DATETO='2011-01-01'
SET @WHSCODE='1-AZJT01'
SET @ITEMCODE='A'
declare @sql nvarchar(4000)
set @sql='s'
declare @cur varchar(4000) --保存动态游标语句,实现物料模糊查询
declare @type nvarchar(4)
set @cur=N'
declare cur1 cursor for --获取型号列值,作为分组依据
select distinct m.型号
from
(SELECT tt.DocDate,tt.DocEntry, tt.DocNum, tt.Comments,d.ItemCode,d.Quantity,left(T4.[BatchNum],3) AS COLORCODE
,SUBSTRING(T4.[BatchNum],4,2) AS ''型号'',d.WhsCode,tt.U_DJLX,tt.U_JYLX,d.LineTotal,D.ObjType,D.LineNum
FROM dbo.OIGN AS tt
INNER JOIN dbo.IGN1 AS d ON tt.DocEntry = d.DocEntry AND tt.CurSource = ''C''
inner join ibt1 T4 ON T4.BaseType = D.ObjType AND D.DocEntry=T4.BaseEntry AND D.LineNum=t4.BaseLinNum
where isnull(SUBSTRING(T4.[BatchNum],4,2),'''')<>'''' and tt.docdate between '
+convert(varchar(20),@datefrom,112)+N' and '''+convert(varchar(20),@dateto,112)
+N''') M
left join OITM p on p.ITEMCODE=m.itemcode
inner join owhs t5 on M.whscode = t5.whscode
where M.whscode ='+@whscode+N' and M.ItemCode like '''+@itemcode+N'%'''+N'
open cur1
fetch next from cur1 into '+@type+'
while @@fetch_status=0
begin
set @sql='+@sql+'['+@type+'],'+'
fetch next from cur1 into '+@type+'
end
set @sql=stuff('+@sql+',1,1,'''')
set '+@sql+'=left('+@SQL+',len('+@SQL+')-1)
close cur1
deallocate cur1'
PRINT @cur
为什么我print后没有结果啊?
作者: wzsjzjjxy 发布时间: 2011-06-12
没有你的表结构和数据,无法作出进一步的判断.
作者: qianjin036a 发布时间: 2011-06-12
作者: maco_wang 发布时间: 2011-06-12
SQL code
SET @type = ''
作者: xiaoliaoyun 发布时间: 2011-06-12
将select 语句作为游标取值来源,这样就有问题了。
因为你的@type 没有赋值
SQL code
SET @type = ''
作者: wzsjzjjxy 发布时间: 2011-06-12
没有表和数据,无法测试.如果有问题自己稍微修改下
SQL code
DECLARE @WHSCODE VARCHAR(20) ,@ITEMCODE VARCHAR(30) ,@Codebars VARCHAR(30) ,@datefrom DATETIME ,@dateto DATETIME ,@SQL NVARCHAR(4000) ,@type NVARCHAR(4) SET @DATEFROM='2010-01-01' SET @DATETO='2011-01-01' SET @WHSCODE='1-AZJT01' SET @ITEMCODE='A' SET @SQL = '' DECLARE CUR1 CURSOR FOR --获取型号列值,作为分组依据 SELECT DISTINCT M.型号 FROM ( SELECT d.ItemCode ,d.WhsCode ,SUBSTRING(T4.[BatchNum],4,2) AS '型号' FROM dbo.OIGN AS tt INNER JOIN dbo.IGN1 AS d ON tt.DocEntry = d.DocEntry AND tt.CurSource = 'C' INNER JOIN ibt1 T4 ON T4.BaseType = D.ObjType AND D.DocEntry=T4.BaseEntry AND D.LineNum=t4.BaseLinNum WHERE ISNULL(SUBSTRING(T4.[BatchNum],4,2),'')<>'' AND tt.docdate BETWEEN @DATEFROM AND @DATETO ) M LEFT JOIN OITM P on P.ITEMCODE = M.itemcode INNER JOIN owhs T5 on M.whscode = T5.whscode WHERE M.whscode =@WHSCODE AND M.ItemCode LIKE '' + @ITEMCODE + '%' OPEN CUR1 FETCH NEXT FROM CUR1 INTO @type WHILE @@FETCH_STATUS=0 BEGIN SET @sql= @sql + ',[' + @type + ']' FETCH NEXT FROM CUR1 INTO @type END SET @sql = STUFF(@sql,1,1,'') CLOSE CUR1 DEALLOCATE CUR1 SELECT @sql
作者: xiaoliaoyun 发布时间: 2011-06-12
ALTER PROC TEST3
@WHSCODE VARCHAR(20),@ITEMCODE VARCHAR(30),@Codebars varchar(30),@datefrom datetime,@dateto datetime
AS
BEGIN
DECLARE @SQL NVARCHAR(4000)
,@type NVARCHAR(4)
DECLARE CUR1 CURSOR FOR --获取型号列值,作为分组依据
SELECT DISTINCT M.型号
FROM
(
SELECT TT.DOCDATE,d.ItemCode
,d.WhsCode
,SUBSTRING(T4.[BatchNum],4,2) AS '型号'
FROM dbo.OIGN AS tt
INNER JOIN dbo.IGN1 AS d ON tt.DocEntry = d.DocEntry AND tt.CurSource = 'C'
INNER JOIN ibt1 T4 ON T4.BaseType = D.ObjType AND D.DocEntry=T4.BaseEntry AND D.LineNum=t4.BaseLinNum
WHERE ISNULL(SUBSTRING(T4.[BatchNum],4,2),'')<>'') M
LEFT JOIN OITM P on P.ITEMCODE = M.itemcode
INNER JOIN owhs T5 on M.whscode = T5.whscode
WHERE M.whscode =@WHSCODE
AND M.ItemCode LIKE '' + @ITEMCODE + '%' AND M.docdate BETWEEN @DATEFROM AND @DATETO
OPEN CUR1
FETCH NEXT FROM CUR1 INTO @type
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql= @sql + ',[' + @type + ']'
FETCH NEXT FROM CUR1 INTO @type
END
SET @sql = STUFF(@sql,1,1,'')
set @sql=left(@SQL,len(@SQL)-1)
CLOSE CUR1
DEALLOCATE CUR1
PRINT @sql
END
-- exec test '1-ASCK01','A','','2010-01-01','2011-01-31'
可以不用动态SQL,可以这样 LIKE '' + @ITEMCODE + '%'
没有表和数据,无法测试.如果有问题自己稍微修改下
SQL code
DECLARE @WHSCODE VARCHAR(20)
,@ITEMCODE VARCHAR(30)
,@Codebars VARCHAR(30)
,@datefrom DATE……
作者: wzsjzjjxy 发布时间: 2011-06-12
可以不用动态SQL,可以这样 LIKE '' + @ITEMCODE + '%'
没有表和数据,无法测试.如果有问题自己稍微修改下
SQL code
DECLARE @WHSCODE VARCHAR(20)
,@ITEMCODE VARCHAR(30)
,@Codebars VARCHAR(30)
,@datefrom DATE……
作者: wzsjzjjxy 发布时间: 2011-06-12
加上这个条件 AND M.型号 IS NOT NULL
SQL code
WHERE M.whscode =@WHSCODE AND M.ItemCode LIKE '' + @ITEMCODE + '%' AND M.docdate BETWEEN @DATEFROM AND @DATETO AND M.型号 IS NOT NULL
我单独执行select distinct M.型号 这个select 是没问题的。
引用 5 楼 xiaoliaoyun 的回复:
可以不用动态SQL,可以这样 LIKE '' + @ITEMCODE + '%'
没有表和数据,无法测试.如果有问题自己稍微修改下
SQL code
DECLARE @WHSCODE VARCHAR(20)
,@ITEMCODE VARCHAR……
作者: xiaoliaoyun 发布时间: 2011-06-12
不知道你的数据情况,不知道你的表结构,貌似不好判断
作者: wzsjzjjxy 发布时间: 2011-06-12