May 1, 2002 at 7:55 am
On new insert i need to retrieve the newly created id field. it was working until i was using sql, but i am using the FOR XML and it gives me an error that this property is not supported. Is there a way in which i could retrieve the newly created id from the DB in an XML format.
May 1, 2002 at 9:07 am
Could you post your select?
Andy
May 1, 2002 at 10:21 am
CREATE PROCEDURE usp_BankAccountInsert
@intID numeric, @vchTitle2 varchar(50), @vchNumber varchar(25), @vchName varchar(100)
AS
INSERT INTO tblBankAccount (intID, vchTitle2, vchNumber, intID)
VALUES (@intID, @vchTitle2, @vchNumber, @vchName)
SELECT @intAcctID = @@IDENTITY
FOR XML AUTO
GO
May 1, 2002 at 11:13 am
Your ID column have to be designated as Identity column.
Usually it created automatically when you insert all other values.
If you want to insert an explicit value into identity column you have to
SET IDENTITY_INSERT to ON.
Look into BOL on a good explanation for this.
May 1, 2002 at 11:15 am
intID is Fk and intAcctID is the pk for this insert. sorry abt the ambiguity 🙂
May 1, 2002 at 12:17 pm
For XML requires a table name in a from clause. You can work around by doing something like this:
create table Test2 (id int identity(1,1))
insert into test2 default values
select @@identity as ID into #temp
select * from #temp for xml auto
drop table #temp
That said, Im not sure why you wouldnt just return it as an output parameter and skip the XML altogether.
Andy
May 1, 2002 at 12:22 pm
Declare @intAcctID
Insert into tablename(...) values (...)
SELECT @intAcctID = @@IDENTITY
SELECT intAcctID
FROM tblAccount
WHERE intAcctID = @intAccountID
FOR XML AUTO, ELEMENTS
I need this since we're using templates to call SP's.
May 1, 2002 at 1:23 pm
Not bad. I'd suggest you use Scope_Identity() rather than @@Identity though.
Andy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply