我定义了一个表变量用于返回DECLARE @TempTable TABLE(
Department varchar(500)
)
这里的@CurrDepartment和@CurrOrgNameK 已经赋值SELECT @CurrDepartment=@CurrDepartment+@CurrOrgNameK
这句话给@CurrDepartment赋值
然后我想把这个变量的值插入表变量
INSERT INTO @TempTable (Department) value (@CurrDepartment) 这句话报错了消息 102,级别 15,状态 1,过程 FN_ReportType,第 51 行
Incorrect syntax near 'value'.
Department varchar(500)
)
这里的@CurrDepartment和@CurrOrgNameK 已经赋值SELECT @CurrDepartment=@CurrDepartment+@CurrOrgNameK
这句话给@CurrDepartment赋值
然后我想把这个变量的值插入表变量
INSERT INTO @TempTable (Department) value (@CurrDepartment) 这句话报错了消息 102,级别 15,状态 1,过程 FN_ReportType,第 51 行
Incorrect syntax near 'value'.
解决方案 »
- sql 数字1234如何转换成一二三四
- 局域网电脑PC2访问另一台电脑PC1 的 MS SQL Servers 数据库,PC2要装MS SQL Server吗?
- 基于SQL Server 2K 进行ADO 编程遇到的问题
- SQLSERVER 复制与订阅的问题
- 企业管理器不能用?
- 行列转换
- 关于distinct的一个奇怪问题....
- 请求帮助用ado2.6把sql server的数据导入excel中出错
- 出来一个怪怪的问题,请各位帮帮忙,谢谢!!!
- Delphi5.0+SQL Server7.0的程序在98下正常,在NT4下不能显示货币的小数位.
- SQLServer 查询结果如何导出为Foxpro的.dbf文件?
- 关于生成数据统计表单的问题
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go--=========================================================
--Edit:Calvin.cai 2008.3.6
--函数名:
--FN_ReportType
--参数
--
--处理逻辑
--=========================================================ALTER FUNCTION [dbo].[FN_ReportType]()
RETURNS @ReturnValue TABLE (
Department varchar(500)
)
AS
BEGIN
DECLARE @CurrDepartment varchar(500)
DECLARE @CurrOrgNameC varchar(50)
DECLARE @CurrOrgNameK varchar(50) DECLARE @CurrOrgID INT
DECLARE @CurrOrgIDC INT
DECLARE @CurrOrgIDK INT
DECLARE @TempTable TABLE(
Department varchar(500)
)
SELECT @CurrOrgID =[OrgID] FROM T_Organization WHERE ParentId is NULL ;
SELECT @CurrOrgID =[OrgID] FROM T_Organization WHERE ParentId = @CurrOrgID;
DECLARE currCursor CURSOR FOR
SELECT OrgID,OrgName FROM T_Organization WHERE ParentId = @CurrOrgID--处集合
Open currCursor
FETCH NEXT FROM currCursor
INTO @CurrOrgIDC,@CurrOrgNameC IF(@@FETCH_STATUS=0)
BEGIN
SELECT @CurrDepartment=@CurrOrgNameC;
DECLARE currCursork CURSOR FOR
SELECT OrgID,OrgName FROM T_Organization WHERE ParentId = @CurrOrgIDC;--科集合
OPEN currCursorK
FETCH NEXT FROM currCursork
INTO @CurrOrgNameK,@CurrOrgNameK
IF(@@FETCH_STATUS=0)
BEGIN
SELECT @CurrDepartment=@CurrDepartment+@CurrOrgNameK+'人員' ;
INSERT INTO @TempTable (Department) values (@CurrDepartment);--插入表变量
FETCH NEXT FROM currCursork
INTO @CurrOrgNameK,@CurrOrgNameK
END
CLOSE currCursork
DEALLOCATE currCursork--注销内层循环游标
FETCH NEXT FROM currCursor
INTO @CurrOrgIDC,@CurrOrgNameC
END
CLOSE currCursor
DEALLOCATE currCursor--注销外层循环游标
RETURN
END
报错
消息 208,级别 16,状态 6,过程 FN_ReportType,第 66 行
Invalid object name 'dbo.FN_ReportType'.
--改这样看看:
ALTER FUNCTION [dbo].[FN_ReportType]()
RETURNS @ReturnValue TABLE (Department varchar(500))
AS
BEGIN
DECLARE @CurrDepartment varchar(500)
DECLARE @CurrOrgNameC varchar(50)
DECLARE @CurrOrgNameK varchar(50) DECLARE @CurrOrgID INT
DECLARE @CurrOrgIDC INT
DECLARE @CurrOrgIDK INT
DECLARE @TempTable TABLE(Department varchar(500))
SELECT @CurrOrgID =[OrgID] FROM T_Organization WHERE ParentId is NULL ;
SELECT @CurrOrgID =[OrgID] FROM T_Organization WHERE ParentId = @CurrOrgID;
DECLARE currCursor CURSOR FOR SELECT OrgID,OrgName FROM T_Organization WHERE ParentId = @CurrOrgID--处集合
Open currCursor
FETCH NEXT FROM currCursor INTO @CurrOrgIDC,@CurrOrgNameC while(@@FETCH_STATUS=0)
BEGIN
SELECT @CurrDepartment=@CurrOrgNameC;
DECLARE currCursork CURSOR FOR SELECT OrgID,OrgName FROM T_Organization WHERE ParentId = @CurrOrgIDC;--科集合
OPEN currCursorK
FETCH NEXT FROM currCursork INTO @CurrOrgNameK,@CurrOrgNameK
while(@@FETCH_STATUS=0)
BEGIN
SELECT @CurrDepartment=@CurrDepartment+@CurrOrgNameK+'人員'
INSERT INTO @TempTable (Department) values (@CurrDepartment);
FETCH NEXT FROM currCursork INTO @CurrOrgNameK,@CurrOrgNameK
END
CLOSE currCursork
DEALLOCATE currCursork--注销内层循环游标
FETCH NEXT FROM currCursor INTO @CurrOrgIDC,@CurrOrgNameC
END
CLOSE currCursor
DEALLOCATE currCursor--注销外层循环游标
RETURN
END--另外楼主这个函数返回表变量@ReturnValue,可在函数中却从未对该变量进行过任何操作。为何?
只有对@TempTable 进行操作
晕死``发无数次才发出去
Invalid object name 'dbo.FN_ReportType'.--另外楼主这个函数返回表变量@ReturnValue,可在函数中却从未对该变量进行过任何操作。为何?
只有对@TempTable 进行操作我第一次写函数 我是照着可以运行的函数写的 如果
RETURNS @ReturnValue TABLE 这里改成
RETURNS @TempTable TABLE 会报这个错误
消息 134,级别 15,状态 1,过程 FN_ReportType,第 12 行
The variable name '@TempTable' has already been declared. Variable names must be unique within a query batch or stored procedure.
declare @ParentId intselect @ParentId = OrgID from T_Organization where ParentId is null
select @ParentId = OrgID from T_Organization where ParentId = @ParentIdselect
Department = a.OrgName + b.OrgName + '人員'
from
T_Organization as a
inner join
T_Organization as b
on a.OrgID = b.ParentId
where
a.ParentId = @ParentId
RETURNS @ReturnValue TABLE 这里改成
RETURNS @TempTable TABLE 会报这个错误
消息 134,级别 15,状态 1,过程 FN_ReportType,第 12 行
The variable name '@TempTable' has already been declared. Variable names must be unique within a query batch or stored procedure. --
函数体内不要再定义 @TempTable
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go--=========================================================
--Edit:Calvin.cai 2008.3.6
--函数名:
--FN_ReportType
--参数
--
--处理逻辑
--=========================================================Create FUNCTION [dbo].[FN_ReportType]()
RETURNS @ReturnValue TABLE (
Department varchar(500)
)
AS
BEGIN
DECLARE @CurrDepartment varchar(500)
DECLARE @CurrOrgNameC varchar(50)
DECLARE @CurrOrgNameK varchar(50) DECLARE @CurrOrgID INT
DECLARE @CurrOrgIDC INT
DECLARE @CurrOrgIDK INT
DECLARE @TempTable TABLE(
Department varchar(500)
)
SELECT @CurrOrgID =[OrgID] FROM T_Organization WHERE ParentId is NULL ;
SELECT @CurrOrgID =[OrgID] FROM T_Organization WHERE ParentId = @CurrOrgID;
DECLARE currCursor CURSOR FOR
SELECT OrgID,OrgName FROM T_Organization WHERE ParentId = @CurrOrgID--处集合
Open currCursor
FETCH NEXT FROM currCursor
INTO @CurrOrgIDC,@CurrOrgNameC WHILE(@@FETCH_STATUS=0)
BEGIN
SELECT @CurrDepartment=@CurrOrgNameC;
DECLARE currCursork CURSOR FOR
SELECT OrgID,OrgName FROM T_Organization WHERE ParentId = @CurrOrgIDC;--科集合
OPEN currCursorK
FETCH NEXT FROM currCursork
INTO @CurrOrgNameK,@CurrOrgNameK
WHILE(@@FETCH_STATUS=0)
BEGIN
SELECT @CurrDepartment=@CurrDepartment+@CurrOrgNameK+'人員' ;
INSERT INTO @TempTable (Department) values (@CurrDepartment);--插入表变量
FETCH NEXT FROM currCursork
INTO @CurrOrgNameK,@CurrOrgNameK
END
CLOSE currCursork
DEALLOCATE currCursork--注销内层循环游标
FETCH NEXT FROM currCursor
INTO @CurrOrgIDC,@CurrOrgNameC
END
CLOSE currCursor
DEALLOCATE currCursor--注销外层循环游标
INSERT INTO @ReturnValue
SELECT * FROM @TempTable RETURN
END最后的我改好的可以运行的函数是这样的
谢谢楼上各位