Help with, Insert / Update and Return ID in 1 SP

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

SQL Server 2008 Forums

Transact-SQL (2008)

Help with, Insert / Update and Return ID in 1 SP

Author

Topic

eljapo4
Posting Yak Master

100 Posts

Posted - 2012-09-21 : 05:26:20

Hi can anyone help me with the following SP? I'm trying to Insert a new record, get the ID of the new record to update the ImageFileName field and then i would like to return the ImageFileName or the new ID. So far I have this:



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Admin_insHeatPump] 
@HeatPumpRangeID int,
@ModelNumber varchar(20), 
@HeatPumpTypeID int, 
@Dimensions varchar(20), 
@ElectricalSupplyID int, 
@Voltage varchar(20), 
@StartingCurrent varchar(20), 
@Weight int, 
@MaxFlowTemp int,
@Benefit1 varchar(200), 
@Benefit2 varchar(200), 
@Benefit3 varchar(200), 
@Benefit4 varchar(200), 
@Benefit5 varchar(200), 
@PDFDatasheetPath varchar(300), 
@PDFBrochurePath varchar(300), 
@ImageFilename varchar(300) output, 
@Filetype varchar(50),
@ModifiedBy varchar(100),
@NewHeatPumpID int output
	
AS

BEGIN TRANSACTION

	INSERT INTO [DR_HeatPumpCalc].[dbo].[tbl_lkupHeatPumps]
			   ([HeatPumpRangeID]
			   ,[ModelNumber]
			   ,[HeatPumpTypeID]
			   ,[Dimensions]
			   ,[ElectricalSupplyID]
			   ,[Voltage]
			   ,[StartingCurrent]
			   ,[Weight]
			   ,[MaxFlowTemp]
			   ,[Benefit1]
			   ,[Benefit2]
			   ,[Benefit3]
			   ,[Benefit4]
			   ,[Benefit5]
			   ,[PDFDatasheetPath]
			   ,[PDFBrochurePath]
			   ,[ImageFilename]
			   ,[Filetype]
			   ,[LastDateModified]
			   ,[ModifiedBy])
		 VALUES
			   (@HeatPumpRangeID,  
			   @ModelNumber, 
			   @HeatPumpTypeID, 
			   @Dimensions,  
			   @ElectricalSupplyID, 
			   @Voltage,  
			   @StartingCurrent,  
			   @Weight, 
			   @MaxFlowTemp, 
			   @Benefit1, 
			   @Benefit2, 
			   @Benefit3, 
			   @Benefit4, 
			   @Benefit5, 
			   @PDFDatasheetPath,  
			   @PDFBrochurePath, 
			   NULL,
			   @Filetype, 
			   GETDATE(),
			   @ModifiedBy )
			   
	--get the ID of the newly created Heat Pump			
	SET @NewHeatPumpID = SCOPE_IDENTITY()

	IF @@ERROR <> 0 GOTO BailOut

	--Update new HeatPump [ImageFilename]
        SET @ImageFileName = 'HeatPump - ' + @NewHeatPumpID + @Filetype
	UPDATE [DR_HeatPumpCalc].[dbo].[tbl_lkupHeatPumps]
	SET [ImageFilename] = @ImageFileName
	WHERE [HeatPumpID] = @NewHeatPumpID	  
				
	IF @@ERROR <> 0 GOTO BailOut
	COMMIT
	--Return new HeatPumpID
        RETURN @NewHeatPumpID
        RETURN @ImageFileName
	
COMMIT
GOTO Complete

BailOut:
ROLLBACK

Complete:

this is giving me an error: Conversion failed when converting the varchar value 'HeatPump - ' to data type int.

eljapo4
Posting Yak Master

100 Posts

Posted - 2012-09-21 : 05:48:17

this is the answer i was after:

--Update new HeatPump [ImageFilename]
SET @ImageFileName = 'HeatPump - ' +Convert(varchar(50),@NewHeatPumpID) + @Filetype
UPDATE [DR_HeatPumpCalc].[dbo].[tbl_lkupHeatPumps]
SET [ImageFilename] = @ImageFileName
WHERE [HeatPumpID] = @NewHeatPumpID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-21 : 12:53:53

quote:

Originally posted by eljapo4

Hi can anyone help me with the following SP? I'm trying to Insert a new record, get the ID of the new record to update the ImageFileName field and then i would like to return the ImageFileName or the new ID. So far I have this:



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Admin_insHeatPump] 
@HeatPumpRangeID int,
@ModelNumber varchar(20), 
@HeatPumpTypeID int, 
@Dimensions varchar(20), 
@ElectricalSupplyID int, 
@Voltage varchar(20), 
@StartingCurrent varchar(20), 
@Weight int, 
@MaxFlowTemp int,
@Benefit1 varchar(200), 
@Benefit2 varchar(200), 
@Benefit3 varchar(200), 
@Benefit4 varchar(200), 
@Benefit5 varchar(200), 
@PDFDatasheetPath varchar(300), 
@PDFBrochurePath varchar(300), 
@ImageFilename varchar(300) output, 
@Filetype varchar(50),
@ModifiedBy varchar(100),
@NewHeatPumpID int output
	
AS

BEGIN TRANSACTION

	INSERT INTO [DR_HeatPumpCalc].[dbo].[tbl_lkupHeatPumps]
			   ([HeatPumpRangeID]
			   ,[ModelNumber]
			   ,[HeatPumpTypeID]
			   ,[Dimensions]
			   ,[ElectricalSupplyID]
			   ,[Voltage]
			   ,[StartingCurrent]
			   ,[Weight]
			   ,[MaxFlowTemp]
			   ,[Benefit1]
			   ,[Benefit2]
			   ,[Benefit3]
			   ,[Benefit4]
			   ,[Benefit5]
			   ,[PDFDatasheetPath]
			   ,[PDFBrochurePath]
			   ,[ImageFilename]
			   ,[Filetype]
			   ,[LastDateModified]
			   ,[ModifiedBy])
		 VALUES
			   (@HeatPumpRangeID,  
			   @ModelNumber, 
			   @HeatPumpTypeID, 
			   @Dimensions,  
			   @ElectricalSupplyID, 
			   @Voltage,  
			   @StartingCurrent,  
			   @Weight, 
			   @MaxFlowTemp, 
			   @Benefit1, 
			   @Benefit2, 
			   @Benefit3, 
			   @Benefit4, 
			   @Benefit5, 
			   @PDFDatasheetPath,  
			   @PDFBrochurePath, 
			   NULL,
			   @Filetype, 
			   GETDATE(),
			   @ModifiedBy )
			   
	--get the ID of the newly created Heat Pump			
	SET @NewHeatPumpID = SCOPE_IDENTITY()

	IF @@ERROR <> 0 GOTO BailOut

	--Update new HeatPump [ImageFilename]
        SET @ImageFileName = 'HeatPump - ' + @NewHeatPumpID + @Filetype
	UPDATE [DR_HeatPumpCalc].[dbo].[tbl_lkupHeatPumps]
	SET [ImageFilename] = @ImageFileName
	WHERE [HeatPumpID] = @NewHeatPumpID	  
				
	IF @@ERROR <> 0 GOTO BailOut
	COMMIT
	--Return new HeatPumpID
        RETURN @NewHeatPumpID
        RETURN @ImageFileName
	
COMMIT
GOTO Complete

BailOut:
ROLLBACK

Complete:

this is giving me an error: Conversion failed when converting the varchar value 'HeatPump - ' to data type int.

you cant return multiple values from Stored Procedure using RETURN

Actually you dont need to return them using RETURN as you already have them as OUTPUT

you just need to declare two variable and pass them in EXEC to receive output values

see

http://www.sqlteam.com/article/stored-procedures-returning-data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources