| Author |
 Topic  |
|
|
micnie_2020
Posting Yak Master
Malaysia
188 Posts |
 Posted - 12/04/2012 : 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 INT
SET @pos=0
DECLARE @s as int
DECLARE @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 selection
WHILE charindex(',',@id)>0
BEGIN
SET @s = cast(substring(@id,0, charindex(',',@id)) as int)
print @s
BEGIN 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
COMMIT
SET @id = substring(@id, charindex(',',@id)+1, LEN(@id) - @pos) --remove the first item from the list
select Success='Success'
END
Error Msg:-
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@Ids". |
Edited by - micnie_2020 on 12/04/2012 03:52:42
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 12/04/2012 : 03:52:35
|
In this line, you are concatenating string with integer value(@entity)
set @sSQL=@sSQL+' select TypeID,EntityID='+@entity+',DeptID='+@dept
Case 1: Change @entity datatype as varchar
Case 2: Do covertion like EntityID='+CAST(@entity AS varchar)+ ', DeptID='
--
Chandu |
 |
|
|
micnie_2020
Posting Yak Master
Malaysia
188 Posts |
Posted - 12/04/2012 : 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 1
Must declare the table variable "@Ids".
Please advise.
Thank you.
Regards,
Micheale
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 12/04/2012 : 04:21:39
|
I think one error is in this line....
'select @Ids,Description,CondID
how can we include a table variable in column-list?
--
Chandu |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 12/04/2012 : 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 |
|
|
| |
Topic |
|
|
|
SP insert into Master & Details
