HELP with output - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

HELP with output

Author

Topic

mike009
Starting Member

15 Posts

Posted - 2011-09-07 : 09:06:10

Hello

I have a stored procedure to insert row in a table
i would like to call this stored procedure into another procedure and get the inserted id to be used in the last procedure

what i did i combine both in one stored procedure but i don't know how to set the inserted.id to a varaible
here what i did:



create PROCEDURE [dbo].[PROC_INSERT_TASK_DOCUMENT_ALL_TRANS_TEMP]



@subjectValue varchar(500),
@documentTypeValue int,
@creationDateValue dateTime,
@uniqueId varchar (850),
@documentPathValue varchar (300),



AS
BEGIN transaction
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	Declare @DOC_INS_ID int
	
	begin    -- Insert statements for procedure here
			INSERT INTO [dbo].[DOCUMENT]
				   ([DOCUMENT_SUBJECT]
				   ,[DOCUMENT_TYPE_ID]
				   ,[DOCUMENT_CREATION_DATE]
				   ,[UNIQUE_ID])
				   output inserted.DOCUMENT_ID
			 VALUES
				   (@subjectValue
				   ,@documentTypeValue
				   ,@creationDateValue
				   ,@uniqueId)
			set @DOC_INS_ID = (select DOCUMENT_ID from Inserted) -- DOESN't owrk
   END

	
INSERT INTO [LAM].[dbo].[TASK_DOCUMENT]
           ([DOCUMENT_ID]
           ,[DOCUMENT_PATH])
           output inserted.TASK_DOCUMENT_ID
     VALUES
           (@DOC_INS_ID
           ,@documentPathValue)

commit Transaction

Kristen
Test

22859 Posts

Posted - 2011-09-07 : 09:18:43

set @DOC_INS_ID = (select DOCUMENT_ID from Inserted) -- DOESN't owrk

should be

set @DOC_INS_ID = scope_identity()

mike009
Starting Member

15 Posts

Posted - 2011-09-07 : 14:50:39

Hello

If i use Scope_idintity() i got error inserting null

Msg 515, Level 16, State 2, Procedure PROC_INSERT_TASK_DOCUMENT_ALL_TRANS_TEMP, Line 82
Cannot insert the value NULL into column 'DOCUMENT_ID', table 'LAM.dbo.TASK_DOCUMENT'; column does not allow nulls. INSERT fails.

I think Scope_Idintity() doesnot return the id in case of update???

here is the full code



ALTER PROCEDURE [dbo].[PROC_INSERT_TASK_DOCUMENT_ALL_TRANS_TEMP]



@subjectValue varchar(500),
@documentTypeValue int,
@creationDateValue dateTime,
@uniqueId varchar (850),
@documentPathValue varchar (300),

-- TASK PARAMS
@taskTypeValue int,
@legalCaseValue int,
@partnerValue int,
@taskDescriptionValue varchar (500),
@taskTotalDurationValue decimal(18,0),
@taskFacturatedDurationValue decimal(18,0)

AS
BEGIN transaction
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	declare @tmp_table table(docID int, tskID int)
	
	Declare @DOC_INS_ID int, @TSK_INS_ID int
	
	if exists(select * from [dbo].[DOCUMENT] where [UNIQUE_ID] = @uniqueId)
		begin
		
			update[dbo].[DOCUMENT]
			
			 set 
			   [DOCUMENT_SUBJECT]=@subjectValue,
			   [DOCUMENT_TYPE_ID] = @documentTypeValue,
			   [DOCUMENT_CREATION_DATE]= @creationDateValue 
			   
			   where [UNIQUE_ID] = @uniqueId
			   
			 set @DOC_INS_ID = scope_identity() -- assign resault to var
		end
    else
		begin    -- Insert statements for procedure here
			INSERT INTO [dbo].[DOCUMENT]
				   ([DOCUMENT_SUBJECT]
				   ,[DOCUMENT_TYPE_ID]
				   ,[DOCUMENT_CREATION_DATE]
				   ,[UNIQUE_ID])
				   
			 VALUES
				   (@subjectValue
				   ,@documentTypeValue
				   ,@creationDateValue
				   ,@uniqueId)
			set @DOC_INS_ID = scope_identity() -- assign resault to var
		END

	-- insert task
	INSERT INTO [LAM].[dbo].[TASK]
           ([TASK_TYPE_ID]
           ,[LEGAL_CASE_ID]
           ,[PROVIDER_ID]
           ,[TASK_DESCRIPTION]
           ,[TASK_TOTAL_DURATION_H]
           ,[TASK_FACTURATED_DURATION_H])

     VALUES
           (@taskTypeValue
           ,@legalCaseValue
           ,@partnerValue
           ,@taskDescriptionValue
           ,@taskTotalDurationValue
           ,@taskFacturatedDurationValue)
           
	  set @TSK_INS_ID = scope_identity()
    -- Insert statements for procedure here
INSERT INTO [LAM].[dbo].[TASK_DOCUMENT]
           ([TASK_ID]
           ,[DOCUMENT_ID]
           ,[DOCUMENT_PATH])
           output inserted.TASK_DOCUMENT_ID
     VALUES
           (@TSK_INS_ID
           ,@DOC_INS_ID
           ,@documentPathValue)

commit Transaction

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-09-07 : 15:08:03

For the UPDATE portion, you already know the value to use; it's @uniqueId.

=======================================
The first rule of Tautology Club is the first rule of Tautology Club. -xkcd, (Honor Societies)

mike009
Starting Member

15 Posts

Posted - 2011-09-08 : 03:48:40

UniqueId is not the primary key, it's another value another column
I would like to have the primary key of the Updated row.

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-08 : 04:58:49

You can create a temptable in d procedure and insert your TASK_DOCUMENT_ID(primary Key into it) using OUTPUT clause.. something like this

CREATE TABLE #track_TASK_DOCUMENT_ID(TASK_DOCUMENT_ID INT)

update[dbo].[DOCUMENT]

set
[DOCUMENT_SUBJECT]=@subjectValue,
[DOCUMENT_TYPE_ID] = @documentTypeValue,
[DOCUMENT_CREATION_DATE]= @creationDateValue
OUTPUT inserted.TASK_DOCUMENT_ID INTO #track_TASK_DOCUMENT_ID
where [UNIQUE_ID] = @uniqueId

Hope this will solve your issues.

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

mike009
Starting Member

15 Posts

Posted - 2011-09-08 : 05:29:59

Thank you all

I solved by using select:



if exists(select * from [dbo].[DOCUMENT] where [UNIQUE_ID] = @uniqueId)
		begin
		
			update[dbo].[DOCUMENT]
			
			 set 
			   [DOCUMENT_SUBJECT]=@subjectValue,
			   [DOCUMENT_TYPE_ID] = @documentTypeValue,
			   [DOCUMENT_CREATION_DATE]= @creationDateValue 
			   --output inserted.DOCUMENT_ID
			   where [UNIQUE_ID] = @uniqueId
			   
			 set @DOC_INS_ID = (select [DOCUMENT_ID] from [dbo].[DOCUMENT] where [UNIQUE_ID] = @uniqueId) -- assign resault to var
		end
    else
		begin    -- Insert statements for procedure here
			INSERT INTO [dbo].[DOCUMENT]
				   ([DOCUMENT_SUBJECT]
				   ,[DOCUMENT_TYPE_ID]
				   ,[DOCUMENT_CREATION_DATE]
				   ,[UNIQUE_ID])
				   --output inserted.DOCUMENT_ID
			 VALUES
				   (@subjectValue
				   ,@documentTypeValue
				   ,@creationDateValue
				   ,@uniqueId)
			set @DOC_INS_ID = scope_identity() -- assign resault to var
		END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources