--要先转换一下类型
SET @SQL=N'SELECT * INTO ##myTable FROM (SELECT '+cast(@fieldName as nvarchar)+
' as myCol FROM '+@tableName+' where '+@tableOID+'='''+@zhuValueOID+''') a' EXEC sp_executesql @SQL
SET @SQL=N'SELECT * INTO ##myTable FROM (SELECT '+cast(@fieldName as nvarchar)+
' as myCol FROM '+@tableName+' where '+@tableOID+'='''+@zhuValueOID+''') a' EXEC sp_executesql @SQL
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
--转换后两边再加单引号
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
--转换后两边再加单引号改成這樣,還不如直接寫Select @relShowValue=@fieldName
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
/*
1aa06 1 2006 12:00AM
*/這樣寫應該是沒有任何問題的,不知道樓主是不是調用的時候哪裡有問題了。將注釋代碼去掉,三次均運行正常
GO
SET ANSI_NULLS ON
GOALTER 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既然调试存储过程都出错了,执行还能对啊