Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SP insert into Master & Details

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 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".

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='+@dept

Case 1: Change @entity datatype as varchar
Case 2: Do covertion like EntityID='+CAST(@entity AS varchar)+ ', DeptID='


--
Chandu
Go to Top of Page

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 1
Must declare the table variable "@Ids".

Please advise.

Thank you.

Regards,
Micheale




Go to Top of Page

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,CondID

how can we include a table variable in column-list?



--
Chandu
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -