Author |
Topic |
micnie_2020
Posting Yak Master
232 Posts |
Posted - 2012-12-04 : 03:26:20
|
Hi All,I try to do insert master & details table by select from ID pass in as below:-Alter procedure [dbo].[sp_duplicateCurrentRecord] ( @id VARCHAR(MAX), @table nvarchar(30), @entity int, @dept int --exec [dbo].[sp_duplicateCurrentRecord] '2070,1090','tbl1',6,34 ) AS DECLARE @pos as INTSET @pos=0DECLARE @s as intDECLARE @sSQL Nvarchar(4000) declare@Ids table( InsertedId int not null) Declare @ASN as Varchar(10)select Top 1 @ASN=Left(AssetNo,2)+''+right('000000'+cast(Cast(Right(AssetNo,6) as int)+1 as varchar(20)),6)from tblMaster where EntityID=@entity order by AssetNo desc --print @entity --batch selectionWHILE charindex(',',@id)>0BEGINSET @s = cast(substring(@id,0, charindex(',',@id)) as int)print @sBEGIN TRANSACTION set @sSQL=N'insert into tblM(TypeID,EntityID,DeptID,SegmentID,purchaseddate,posteddate, AssetNo)' set @sSQL=@sSQL+' output inserted.AID into @Ids(InsertedId) ' set @sSQL=@sSQL+' select TypeID,EntityID='+CAST(@entity AS VARCHAR(20))+' ,DeptID='+CAST(@dept AS VARCHAR(20))+' ,SegmentID,purchaseddate,posteddate, '''+@ASN+''' ' set @sSQL=@sSQL+' from tblM where [b]AID='+CAST(@s AS VARCHAR(20))+' ' print @sSQL exec sp_executesql @sSQL set @sSQL=N'insert into '+@table+'(AID,Description,CondID, AssetNo)' set @sSQL=@sSQL+'select [b]@Ids,Description,CondID,'''+@ASN+''' from '+@table+' where AID=cast('+@s+' as int)' print @sSQL exec sp_executesql @sSQL -- Commit the transaction if successful COMMITSET @id = substring(@id, charindex(',',@id)+1, LEN(@id) - @pos) --remove the first item from the listselect Success='Success'ENDError Msg:-Msg 1087, Level 15, State 2, Line 1Must declare the table variable "@Ids". |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-12-04 : 03:52:35
|
In this line, you are concatenating string with integer value(@entity)set @sSQL=@sSQL+' select TypeID,EntityID='+@entity+',DeptID='+@deptCase 1: Change @entity datatype as varcharCase 2: Do covertion like EntityID='+CAST(@entity AS varchar)+ ', DeptID=' --Chandu |
|
|
micnie_2020
Posting Yak Master
232 Posts |
Posted - 2012-12-04 : 03:54:30
|
Tq Bandi.I manage to solved it too.Now i have another problem on the set @sSQL=N'insert into tblM(TypeID,EntityID,DeptID,SegmentID,purchaseddate,posteddate, AssetNo)'set @sSQL=@sSQL+' output inserted.AID into @Ids(InsertedId) 'exec sp_executesql @sSQL set @sSQL=N'insert into '+@table+'(AID,Description,CondID, AssetNo)'set @sSQL=@sSQL+'select [b]@Ids,Description,CondID,'''+@ASN+''' from '+@table+' where AID=cast('+@s+' as int)' Error Msg:-Msg 1087, Level 15, State 2, Line 1Must declare the table variable "@Ids".Please advise.Thank you.Regards,Micheale |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-12-04 : 04:21:39
|
I think one error is in this line....'select @Ids,Description,CondIDhow can we include a table variable in column-list?--Chandu |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-12-04 : 04:48:30
|
In your procedure, you are looping through each input ID.. right?You can avoid that looping by using this condition WHERE ','+@id+',' LIKE '%,'+AID+',%' --Chandu |
|
|
|
|
|