June 21, 2007 at 4:04 am
Hi all,
I have table with one column as identity column.Now I want to the value of the newly inserted value of the identity column to another using update statement.I am using this query.But this not working.can some help
--Stored Procedure Start
Create Procedure sp_GetIDForRecovery
--Created BY Danasekarane.A,21-07-2007
(
@File_ID bigint,
@User_ID nvarchar(6),
@Filename varchar(100),
@FileModifiedon datetime,
@Filesize bigint,
@System nvarchar(50),
@BookID bigint,
@StageID varchar(50),
@DivisionID varchar(50),
@WMSID smallint,
@Activityid int,
@JobCode varchar(100),
@RecoveryID bigint output
)
as
insert wms_TrnRecoveryDetails ([User_ID],[FileName],FileModifiedDate,Filesize,system,Bookid,stageid,Divisionid,wmsid,Activityid,jobcode,[FileID])
values (select @User_ID,@Filename,@FileModifiedon,@Filesize,@System,@BookID,@StageID,@DivisionID,
@WMSID,@Activityid,@JobCode,scope_identity())
Select @RecoveryID=scope_identity()
return @RecoveryID
--Stored Procedure End
DECLARE @now datetime
Declare @Ids int
SELECT @now = GETDATE()
Exec sp_GetIDForRecovery 10,'48','222',@now,122,'123',12,'13','34',2,34,'43',@Ids
print @Ids
Dana
June 21, 2007 at 5:05 am
I assume the FileID is the identity column. Try this for the insert statement.
insert wms_TrnRecoveryDetails ([User_ID],[FileName], FileModifiedDate, Filesize, system, Bookid, stageid, Divisionid, wmsid, Activityid, jobcode)
values (@User_ID, @Filename, @FileModifiedon, @Filesize, @System, @BookID, @StageID, @DivisionID, @WMSID, @Activityid, @JobCode)
You don't need to specify the identity column in the insert. You also don't use the keyword 'select' if you're inserting values
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 21, 2007 at 8:18 am
Thanks for the Reply Gila,
I want to insert the Transaction_ID values from FILE_ID value.How can I
June 21, 2007 at 8:55 am
Not quite sure of what you want to do. But to return the Identity value you can you @@IDENTITY or Scopy_Identity() after your insert statement. Like this
Select @RecoveryID=scope_identity()
Select @RecoveryID=@@IDENTITY
Here is the code I used to recreate the sitution. I plugged in a 12 for file ID. Removed the “Return” statement and in the Exec of the SP add the word “Output” to show that the parameter is an Output parameter. Hope this helps.
Steve
/****** Object: StoredProcedure [dbo].[sp_GetIDForRecovery] Script Date: 06/21/2007 10:38:16 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetIDForRecovery]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetIDForRecovery]
GO
/****** Object: Table [dbo].[wms_TrnRecoveryDetails] Script Date: 06/21/2007 10:36:27 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[wms_TrnRecoveryDetails]') AND type in (N'U'))
DROP TABLE [dbo].[wms_TrnRecoveryDetails]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[wms_TrnRecoveryDetails]') AND OBJECTPROPERTY(id, N'IsUserTABLE') = 1)
BEGIN
CREATE TABLE [dbo].[wms_TrnRecoveryDetails](
[scope_identity] [bigint] IDENTITY(1,1) NOT NULL,
[User_ID] nvarchar(6),
[FileName] varchar(100),
FileModifiedDate datetime,
Filesize bigint,
system nvarchar(50),
Bookid bigint,
stageid varchar(50),
Divisionid varchar(50),
wmsid smallint,
Activityid int,
jobcode varchar(100),
[FileID] Bigint
) ON [PRIMARY]
END
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Create Procedure sp_GetIDForRecovery
--Created BY Danasekarane.A,21-07-2007
(@File_ID bigint,
@User_ID nvarchar(6),
@Filename varchar(100),
@FileModifiedon datetime,
@Filesize bigint,
@System nvarchar(50),
@BookID bigint,
@StageID varchar(50),
@DivisionID varchar(50),
@WMSID smallint,
@Activityid int,
@JobCode varchar(100),
@RecoveryID bigint output
)
as
insert wms_TrnRecoveryDetails ([User_ID],[FileName],FileModifiedDate,Filesize,system,Bookid,stageid,Divisionid,wmsid,Activityid,jobcode,[FileID])
values (@User_ID,@Filename,@FileModifiedon,@Filesize,@System,@BookID,@StageID,@DivisionID,
@WMSID,@Activityid,@JobCode,12)
Select @RecoveryID=scope_identity()
--Select @RecoveryID=@@IDENTITY
--Print @RecoveryID
--Stored Procedure End
And to run the procedure….
DECLARE @now datetime
Declare @Ids bigint
SELECT @now = GETDATE()
Exec sp_GetIDForRecovery 10,'48','222',@now,122,'123',12,'13','34',2,34,'43',@Ids OUTPUT
print '@Ids = ' + Cast(@Ids as varchar)
SELECT [scope_identity]
,[User_ID]
,[FileName]
,[FileModifiedDate]
,[Filesize]
,[system]
,[Bookid]
,[stageid]
,[Divisionid]
,[wmsid]
,[Activityid]
,[jobcode]
,[FileID]
FROM [SWL_KnowledgeBase].[dbo].[wms_TrnRecoveryDetails]
June 21, 2007 at 11:10 pm
Thanks Steve,
It worked well. But How can I set the value of the FileID , Which is returned in the Recovery ID
Dana
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply