存储过程中的问题,急啊

[xln1325]

在存储过程中,动态的得到表中不同字段的值,
SET @SQL=N'SELECT * INTO ##myTable FROM (SELECT '+@fieldName+' as myCol FROM '+@tableName+' where '+@tableOID+'='''+@zhuValueOID+''') a'
EXEC sp_executesql @SQL
set @relShowValue=convert(varchar(255),(SELECT * FROM ##myTable))
DROP TABLE ##myTable


@fieldName:是表中的字段,如果这个字段是datetime,int型时,它就报数据据不兼容的错误,如果是varchar型的,就没有问题,郁闷啊!

[WangZWang]

--要先转换一下类型
SET @SQL=N'SELECT * INTO ##myTable FROM (SELECT '+cast(@fieldName as nvarchar)+
' as myCol FROM '+@tableName+' where '+@tableOID+'='''+@zhuValueOID+''') a'

EXEC sp_executesql @SQL

[OracleRoob]

--@fieldName转换为nvarchar型


SET @SQL=N'SELECT * INTO ##myTable FROM (SELECT '+cast(@fieldName as nvarchar(50))+' as myCol FROM '+@tableName+' where '+@tableOID+'='''+@zhuValueOID+''') a'
EXEC sp_executesql @SQL
set @relShowValue=convert(varchar(255),(SELECT * FROM ##myTable))
DROP TABLE ##myTable

[xln1325]

不行啊,还是类型冲突

[OracleRoob]

--@fieldName转换为nvarchar型
--转换后两边再加单引号


SET @SQL=N'SELECT * INTO ##myTable FROM (SELECT '''+cast(@fieldName as nvarchar(50))+''' as myCol FROM '+@tableName+' where '+@tableOID+'='''+@zhuValueOID+''') a'
EXEC sp_executesql @SQL
set @relShowValue=convert(varchar(255),(SELECT * FROM ##myTable))
DROP TABLE ##myTable

[xln1325]

不行,这样子报的错是##myTable 不存在,还有....

[paoluo]

cast(@fieldName as nvarchar)

這個轉換應該沒有用，這是字段名，又不是字段。

字段名是什麼類型，應該沒有任何影響的啊。

[paoluo]

--@fieldName转换为nvarchar型
--转换后两边再加单引号

改成這樣，還不如直接寫

Select @relShowValue=@fieldName

[paoluo]

Create Table TEST(ID Int,Name Varchar(10),TestDate DateTime)
Insert TEST Select 1,'aa','2006-06-01'
Union All Select 2,'bb','2006-06-02'
Union All Select 3,'cc','2006-06-03'
GO
Declare @SQL Nvarchar(4000)
Declare @fieldName Nvarchar(100)
Declare @tableName Nvarchar(100)
Declare @relShowValue Nvarchar(4000)
Set @tableName='TEST'
Set @fieldName='ID'
--Set @fieldName='Name'
--Set @fieldName='TestDate'
SET @SQL=N'SELECT * INTO ##myTable FROM (SELECT TOP 1 '+@fieldName+' as myCol FROM '+@tableName+') a'
EXEC sp_executesql @SQL
set @relShowValue=convert(varchar(255),(SELECT * FROM ##myTable))
Select @relShowValue
DROP TABLE ##myTable
GO
Drop Table TEST
--Result
/*
1

aa

06 1 2006 12:00AM
*/

這樣寫應該是沒有任何問題的，不知道樓主是不是調用的時候哪裡有問題了。

將注釋代碼去掉，三次均運行正常

[xln1325]

我把语句放在查询分析器中单独试的时候,也是没有问题的,但是一进存储过程调试就出问题??

[paoluo]

完整的代碼貼出來看看。

另外是調試存儲過程的時候出錯，還是執行存儲過程的時候出錯？？

[xln1325]

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO









ALTER PROCEDURE SP_Build_ModifyDate(
@Sample_CheckFlowOID uniqueidentifier
)
AS
--------------------------------------------------
-- 返回行集:
-- 1 ErrNum (错误代码) 2 ErrMsg (错误信息) 3 Sample_FentTaskOID 打样任务单OID
--------------------------------------------------
SET NOCOUNT ON

SET XACT_ABORT ON

declare @Sample_FentRequisitionOID uniqueidentifier,
@Sample_FentTaskOID uniqueidentifier,
@relFkValue uniqueidentifier,
@SQL NVARCHAR(4000),
@relShowValue varchar(255),
@relFkValueStr varchar(255),
@OrderNo int,
@ModifyContent varchar(50),
@fieldChineseName varchar(50),
@fieldType varchar(50),
@depict varchar(50),
@dateTime datetime


declare @tableName varchar(50),
@tableOID varchar(50),
@zhuValueOID varchar(255),
@fieldName varchar(50), --要修改的字段名
@relatTable varchar(100), --关联表名
@relatField varchar(100) --关联表字段

declare @relTableOID varchar(60) --关联表的主键字段

declare @ErrNum int,
@ErrMsg varchar(100)


select @ErrNum = 0, @ErrMsg = '',@OrderNo=1,@depict=''

IF @Sample_CheckFlowOID IS NULL or convert(varchar(255),@Sample_CheckFlowOID) = ''
begin
select @ErrNum = -1, @ErrMsg = 'Sample_CheckFlowOID无效！'
select @ErrNum 'ErrNum', @ErrMsg 'ErrMsg'
RETURN @@ERROR
end


BEGIN TRANSACTION

delete from Sample_CheckFlowDTL where Sample_CheckFlow_FK=@Sample_CheckFlowOID
--得到要修改的表
select @tableName=tableName,@tableOID=tableOID,@ModifyContent=ModifyContent from Sample_FieldChange where Sample_FieldChangeOID=
(
select Sample_FieldChange_FK from Sample_CheckFlow where Sample_CheckFlowOID=@Sample_CheckFlowOID
)

--得到要修改表的关键值
select @Sample_FentRequisitionOID=Sample_FentRequisitionOID,@Sample_FentTaskOID=Sample_FentTaskOID from Flow_10010 where Sample_FentRequisitionOID=(select Sample_FentRequisition_FK from Sample_CheckFlow where Sample_CheckFlowOID=@Sample_CheckFlowOID)
if @tableName='Sample_FentRequisition'
select @zhuValueOID=convert(varchar(255),@Sample_FentRequisitionOID)
if @tableName='Sample_FentTask'
select @zhuValueOID=convert(varchar(255),@Sample_FentTaskOID)


--得到需要修改的字段
declare MyCursor CURSOR FOR
select fieldName,relatTable,relatField,fieldChineseName,fieldType from Sample_FieldChangeDTL where Sample_FieldChange_FK=(select Sample_FieldChange_FK from Sample_CheckFlow where Sample_CheckFlowOID=@Sample_CheckFlowOID) and isModify=1
OPEN MyCursor
FETCH next FROM MyCursor into @fieldName,@relatTable,@relatField,@fieldChineseName,@fieldType
WHILE @@FETCH_STATUS = 0
BEGIN
if @relatTable is not null and @relatTable<>''
begin
--先取得表中的字段值
SET @SQL=N'SELECT * INTO ##myTable FROM (SELECT '+@fieldName+' FROM '+@tableName+' where '+@tableOID+'='''+@zhuValueOID+''') a'
EXEC sp_executesql @SQL
set @relFkValue=(SELECT * FROM ##myTable)
DROP TABLE ##myTable

set @relFkValueStr=convert(varchar(255),@relFkValue)

--取得关联表中的值
select @relTableOID=@relatTable+'OID'

SET @SQL=N'SELECT * INTO ##myTable FROM (SELECT '+@relatField+' FROM '+@relatTable+' where '+@relTableOID+'='''+@relFkValueStr+''') a'
EXEC sp_executesql @SQL
set @relShowValue=(SELECT * FROM ##myTable)
DROP TABLE ##myTable

insert into Sample_CheckFlowDTL(Sample_CheckFlowDTLOID,Sample_CheckFlow_FK,OrderNo,ModifyContent,tableName,tableOID,tableOIDValue,fieldName,fieldChineseName,depict,fieldType,oldValue)
values(newid(),@Sample_CheckFlowOID,@OrderNo,@ModifyContent,@tableName,@tableName+'OID',@zhuValueOID,@fieldName,@fieldChineseName,@depict,@fieldType,@relShowValue)


end
else
begin




SET @SQL=N'set @relShowValue=((SELECT '+@fieldName+' FROM '+@tableName+' where '+@tableOID+'='''+@zhuValueOID+'''))'
EXEC sp_executesql @SQL

set @relShowValue=convert(varchar(255),(SELECT * FROM ##myTable))
DROP TABLE ##myTable


insert into Sample_CheckFlowDTL(Sample_CheckFlowDTLOID,Sample_CheckFlow_FK,OrderNo,ModifyContent,tableName,tableOID,tableOIDValue,fieldName,fieldChineseName,depict,fieldType,oldValue)
values(newid(),@Sample_CheckFlowOID,@OrderNo,@ModifyContent,@tableName,@tableName+'OID',@zhuValueOID,@fieldName,@fieldChineseName,@depict,@fieldType,@relShowValue)

end

set @OrderNo=@OrderNo+1
FETCH next FROM MyCursor into @fieldName,@relatTable,@relatField,@fieldChineseName,@fieldType
END

close MyCursor
DEALLOCATE MyCursor





if @@ERROR<>0
begin
select @ErrNum = @@ERROR, @ErrMsg = '失败！'
ROLLBACK TRANSACTION
select @ErrNum 'ErrNum', @ErrMsg 'ErrMsg'
RETURN @@ERROR
end

COMMIT TRANSACTION

-- return data in resultset
select @ErrNum 'ErrNum', @ErrMsg 'ErrMsg'

RETURN @@ERROR
--------------------------------------------------









GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

既然调试存储过程都出错了,执行还能对啊

[paoluo]

我把语句放在查询分析器中单独试的时候,也是没有问题的,但是一进存储过程调试就出问题??

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
你說放在查詢分析器中單獨試OK，我還以為你是可以將存儲過程建好。

[xln1325]

刚才试了一个OK,可能是字段长度的问题,再试试吧!苦呀

[xln1325]

问题已经解决了,只要把上面的if里的##myTable换个名字就行了