Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

Scope_Identity assigned value from proc execution....

I am trying to have a proc call other procedures and assign a value of the scope identity for the cross reference table.

Simply put:

exec SPROC_INSAddress

exec SPROC_INSPerson

Both of these procs get executed in the "master proc" and in doing so the first proc needs to return scope_identity and so does the second such that they can be linked together.

I am an Oracle guy hating SQL Server with this not having a sequence.  How nice would that be?  

Please help me with this issue experts...

Thanks in advance,

B
declare @v_AddressID as bigint
      
      begin
				exec SPROC_INSAddress
				    @i_Address1
				  , @i_Address2	
				, @i_Address3				
				  , @i_Address4		
				  , @i_SuiteAptNo		
				  , @i_City					
				  , @i_StateProvince
				  , @i_ZipCode
				  , @i_ZipSuffix
				  , @i_Country
				  , @i_Region
				  , @i_AddressType
				  , @i_AddressDefault
				  , @i_Alpha
				  , @i_Delta
				  , @i_DeltaCo 	= @v_AddressID
			
				exec SPROC_INSXREFCompanyAddress
					@i_ID
				  , @v_AddressID
				  , @i_Alpha
				  , @i_Delta
				  , @i_DeltaCo
		
		end

Open in new window

0
cyimxtck
Asked:
cyimxtck
  • 2
  • 2
1 Solution
 
cyimxtckAuthor Commented:
here is the proc that returns the scope identity.
ALTER PROCEDURE [dbo].[SPROC_INSAddress]
      @i_Address1					nvarchar(125)
    , @i_Address2					nvarchar(125)
	, @i_Address3					nvarchar(125)
	, @i_Address4					nvarchar(125)
	, @i_SuiteAptNo					nvarchar(25)
	, @i_City						nvarchar(50)
	, @i_StateProvince				nvarchar(50)
	, @i_ZipCode					nvarchar(10)
	, @i_ZipSuffix					nvarchar(10)
	, @i_Country					nvarchar(50)
	, @i_Region						bigint
	, @i_AddressType				nchar(1)
	, @i_Default					bit
	, @i_Alpha						smalldatetime
	, @i_Delta						bigint
    , @i_DeltaCo                    bigint
	
AS

BEGIN

declare @o_AddressID    as  bigint

SET NOCOUNT ON

    INSERT dbo.T_Address
	(
	  Address1
	, Address2
	, Address3
	, Address4
	, SuiteAptNo
	, City
	, StateProvince
	, ZipCode
	, ZipSuffix
	, Country
	, Region
	, AddressType
	, AddressDefault
	, Alpha
	, Delta
    , DeltaCo
	)
	
    VALUES 
    (
    @i_Address1
  , @i_Address2
  , @i_Address3			
  , @i_Address4
  , @i_SuiteAptNo		
  , @i_City			
  , @i_StateProvince
  , @i_ZipCode
  , @i_ZipSuffix
  , @i_Country
  , @i_Region
  , @i_AddressType
  , @i_Default
  , @i_Alpha
  , @i_Delta
  , @i_DeltaCo	
    )
    
    set @o_AddressID = cast(SCOPE_IDENTITY() as bigint)
    return @o_AddressID
		    
END

Open in new window

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
you need to modify the procedure "SPROC_INSAddress" to return the  SCOPE_IDENTITY()
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
declare @v_AddressID as bigint
declare @ScopeIdentity int
     
      begin
                        exec @ScopeIdentity  =  SPROC_INSAddress
                            @i_Address1
                          , @i_Address2      
                        , @i_Address3                        
                          , @i_Address4            
                          , @i_SuiteAptNo            
                          , @i_City                              
                          , @i_StateProvince
                          , @i_ZipCode
                          , @i_ZipSuffix
                          , @i_Country
                          , @i_Region
                          , @i_AddressType
                          , @i_AddressDefault
                          , @i_Alpha
                          , @i_Delta
                          , @i_DeltaCo       = @v_AddressID
                 
-- now "@ScopeIdentity" holds the scopeidentity value
                        exec SPROC_INSXREFCompanyAddress
                              @i_ID
                          , @v_AddressID
                          , @i_Alpha
                          , @i_Delta
                          , @i_DeltaCo
           
            end
0
 
cyimxtckAuthor Commented:
Perfectly illustrated in SQL Servers significant limitation of a sequence!
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now