如何调用表变量

jskscxy

我声明了表变量，也成功将记录追加到表变量，

接下来我要调用表变量生成交叉表，但系统总提示我要声明表变量

ALTER procedure test
@PostID as int
AS
DECLARE @strSql as varchar(6000), @strTmpCol as varchar(1000)

DECLARE @tblTemp TABLE(OrderBy int,EmployeeID_Name varchar(50),ProductID int,ProductName varchar(50),ProductSpec varchar(50),Qty int)

INSERT INTO @tblTemp(OrderBy,EmployeeID_Name,ProductID,ProductName,ProductSpec,Qty)
SELECT dbo.产品表.排序, CONVERT(varchar, dbo.btj_Qry计件明细.计件雇员)
      + dbo.btj_Qry计件明细.姓名 AS 工号姓名, dbo.产品表.产品编号,
      dbo.产品表.产品名称, dbo.产品表.产品规格, dbo.btj_Qry计件明细.良品数
FROM dbo.产品表 INNER JOIN
      dbo.btj_Qry计件明细 ON
      dbo.产品表.产品编号 = dbo.btj_Qry计件明细.产品编号
WHERE (dbo.btj_Qry计件明细.计件日期 BETWEEN CONVERT(DATETIME,
      '2006-09-26 00:00:00', 102) AND CONVERT(DATETIME, '2006-10-25 00:00:00', 102))
      AND (dbo.btj_Qry计件明细.计件职场 = @PostID)


ECUTE ('DECLARE corss_cursor CURSOR FOR SELECT DISTINCT EmployeeID_Name from [url=mailto:'+@tblTemp+']'+@tblTemp+'[/url] for read only ')            
BEGIN
SET NOCOUNT ON
  SET @strSql ='SELECT OrderBy,ProductID,ProductName,ProductSpec, Sum(Qty) AS QtyTotal'

  OPEN corss_cursor
WHILE (0=0)
  BEGIN
    FETCH NEXT FROM corss_cursor
    INTO @strTmpCol
    IF (@@fetch_status<>0) BREAK
        SET @strSql = @strSql + ', Sum(CASE EmployeeID_Name WHEN ''' + @strTmpCol + ''' THEN Qty ELSE Null END) AS ['+ @strTmpCol+']'
  END
SET @strSql = @strSql + ' from [url=mailto:'+@tblTemp+']'+@tblTemp+'[/url] group by OrderBy,ProductID,ProductName,ProductSpec Order by OrderBy'

EXECUTE(@strSql)

  IF @@error <>0 RETURN @@error
  CLOSE corss_cursor
  DEALLOCATE corss_cursor RETURN 0

END

lwwvb

建议不要用ACCESS 2003直接写存储过程,相当垃圾.你可以用SQL 2000的查询分析器来输入,出错了也知道在哪行里.