SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SP insert into Master & Details
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

micnie_2020
Posting Yak Master

Malaysia
228 Posts

Posted - 12/04/2012 :  03:26:20  Show Profile  Reply with Quote
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
2224 Posts

Posted - 12/04/2012 :  03:52:35  Show Profile  Reply with Quote
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

Malaysia
228 Posts

Posted - 12/04/2012 :  03:54:30  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 12/04/2012 :  04:21:39  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 12/04/2012 :  04:48:30  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000