INSERT EXISTS Q - 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

INSERT EXISTS Q

Author

Topic

VThou
Starting Member

4 Posts

Posted - 2010-02-01 : 05:34:41

Hey all, have tried searching on this site (INSERT EXISTS) for what I need however am receiving the 'Page not found' site error?

Anyways, the situation is that I am trying to insert a set of values spanning across three tables, obviously all related, using a stored procedure in SQL 2005 server. The stored procedure is called from an ASP .NET web application in the user entering data. The problem I am having is having the stored procedure to first check to see if the record already exists. If it exists return the Primary Key (to be used in the higher up table using SCOPE_IDENTITY()), else create the record and return its Primary Key. However it seems that if the record already exists there is no Primary key being returned. Code as follow:


CREATE PROCEDURE [dbo].[SProc_InsBudgCostIMP] 
	
	@ID INT,
	@budgetCost decimal(19, 0),
	@budgetName varchar(40),
	@costGroup varchar(50)
AS
BEGIN
	
	SET NOCOUNT ON;
	DECLARE @CostTypeID INT, @CostID INT

    
    INSERT INTO Cost_Type
    (
		costTName
    )
    SELECT
    (		
		@costGroup
    )
    WHERE  NOT EXISTS (SELECT 1 FROM Cost_Type WHERE costTName=@costGroup)
    SELECT @CostTypeID = SCOPE_IDENTITY() 
    
    INSERT INTO Budget_Cost
    (
		costName
    )
    SELECT
    (
		@budgetName
    )
    WHERE  NOT EXISTS (SELECT 1 FROM Budget_Cost WHERE costName=@budgetName)
    SELECT @CostID = SCOPE_IDENTITY() 
    
    
	INSERT INTO Budget_Budget_Cost
	(
		auTotal,
		BudgetbudgetID,
		Budget_CostbudgCID		
	)
	VALUES
	(
		@budgetCost,
		@ID,
		@CostID
	)
	
END

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-02-01 : 05:39:53

use like this


    INSERT INTO Cost_Type
    (
		costTName
    )
    SELECT			
		@costGroup
    WHERE  NOT EXISTS (SELECT 1 FROM Cost_Type WHERE costTName=@costGroup)

    SELECT @CostTypeID = CostTypeID FROM Cost_Type WHERE  costTName=@costGroup
    
    INSERT INTO Budget_Cost
    (
		costName
    )
    SELECT
    (
		@budgetName
    )
    WHERE  NOT EXISTS (SELECT 1 FROM Budget_Cost WHERE costName=@budgetName)

    SELECT @CostID = CostID FROM Budget_Cost WHERE costName=@budgetName    
    
	INSERT INTO Budget_Budget_Cost
	(
		auTotal,
		BudgetbudgetID,
		Budget_CostbudgCID		
	)
	VALUES
	(
		@budgetCost,
		@ID,
		@CostID
	)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 05:47:42

it should be


CREATE PROCEDURE [dbo].[SProc_InsBudgCostIMP] 
	
	@ID INT,
	@budgetCost decimal(19, 0),
	@budgetName varchar(40),
	@costGroup varchar(50)
AS
BEGIN
	
	SET NOCOUNT ON;
	DECLARE @CostTypeID INT, @CostID INT

    SELECT @CostTypeID =  CostTypeID 
    FROM Cost_Type 
    WHERE costTName=@costGroup

    SELECT @CostID=CostID 
    FROM Budget_Cost 
    WHERE costName=@budgetName

   IF  @CostTypeID IS NULL
   BEGIN
   INSERT INTO Cost_Type
    (
		costTName
    )
    VALUES(		
		@costGroup
          )
    SET @CostTypeID = SCOPE_IDENTITY() 
  END
  IF @CostID IS NULL
  BEGIN
    INSERT INTO Budget_Cost
    (
		costName
    )
    VALUES
    (
		@budgetName
    )
    
    SELECT @CostID = SCOPE_IDENTITY() 
    END
    
	INSERT INTO Budget_Budget_Cost
	(
		auTotal,
		BudgetbudgetID,
		Budget_CostbudgCID		
	)
	VALUES
	(
		@budgetCost,
		@ID,
		@CostID
	)
	
END

VThou
Starting Member

4 Posts

Posted - 2010-02-01 : 07:28:59

visakh16 / bklr - Many thanks for such a quick response!

I have initially tried visakh16 code and it worked! Will also look at bklr code as both procedures I am having trouble understanding as haven't seen it written this way before (somewhat new to SQL).

Thanks again as this has helped very much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 09:09:13

quote:
Originally posted by VThou

visakh16 / bklr - Many thanks for such a quick response!

I have initially tried visakh16 code and it worked! Will also look at bklr code as both procedures I am having trouble understanding as haven't seen it written this way before (somewhat new to SQL).

Thanks again as this has helped very much!

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources