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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

micnie_2020
Posting Yak Master

Malaysia
232 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
2241 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
232 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
2241 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
2241 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  
 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.11 seconds. Powered By: Snitz Forums 2000