Setting the value returned by a Select statement

[HBMSGuy]

Hello, While working with stored procedures I came across something I didn't know how to handle.

I wanted to make a stored procedure where, One could give it 2 parameters. With these two parameters, it would return the uniqueidentifier of the row that matched, or if no match was found it would create one.

I get stuck at the first part.

ALTER PROCEDURE [dbo].[GetOrCreateAlbumID]
	@BandID						uniqueidentifier,
	@AlbumName					varchar(50),
	@AlbumID					uniqueidentifier OUTPUT
AS
BEGIN
    DECLARE @tempid uniqueidentifier

    SET @tempid = SELECT AlbumID From Album WHERE Album.AlbumName = @AlbumName AND Album.BandID = @bandID

    IF(@tempid != NULL)
        BEGIN
            SET @AlbumID = @tempid
            RETURN 0
        END
    ELSE
        BEGIN
            --Add the new row
            RETURN 0
        END
END

Thanks for the help,
Johnny

[sknake]

Are you looking for an example of an SP using many out parameters? If so then here is one :

GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[SensorImport7]
  @SensorName varchar(30),
  @ServerId int,
  @SerialNumber varchar(16),
  @SensorType tinyint,
  @RowIndex tinyint,
  @JobValueA varchar(8),
  @JobValueB varchar(8),
  @JobState varchar(8),
  @JobHumidity varchar(8),
  @JobTemperature varchar(8),
  @JobAnalog varchar(8),
  @Age int,
  @EntranceDoor bit OUTPUT,
  @ExitDoor bit OUTPUT,
  @SwitchIO bit OUTPUT,
  @CombineA bit OUTPUT,
  @CombineB bit OUTPUT,
  @MultFactor money OUTPUT,
  @AddFactor money OUTPUT,
  @QueryOnly bit

As

Declare @Exists bit
Set @Exists = (Case When EXISTS (Select * From Sensors WITH (NOLOCK) Where SerialNumber = @SerialNumber and ServerId = @ServerId) Then 1 Else 0 End)

If @Exists = 0
Begin
  Declare @FacilityId int
  Select @FacilityId = ParentFacility
  From Servers (NOLOCK)
  Where ServerId = @ServerId

  Insert Into Sensors (ServerId,  FacilityId,  RowIndex,  SensorName,  SerialNumber,  SensorType,  MultFactor, AddFactor, Active, JobValueA,  JobValueB,  JobState,  JobHumidity,  JobTemperature,  JobAnalog,  CombineValueA, CombineValueB, Age)
  Values              (@ServerId, @FacilityId, @RowIndex, @SensorName, @SerialNumber, @SensorType, 0,          0,         1,      @JobValueA, @JobValueB, @JobState, @JobHumidity, @JobTemperature, @JobAnalog, @CombineA,     @CombineB, @Age)
End
Else 
Begin

  If @QueryOnly = 0
  Begin
    Update Sensors
    Set SensorName = @SensorName, 
        MultFactor = @MultFactor, 
        AddFactor = @AddFactor, 
        EntranceDoor = @EntranceDoor, 
        ExitDoor = @ExitDoor, 
        SwitchIO = @SwitchIO,
        CombineValueA = @CombineA,
        CombineValueB = @CombineB,
        Active = 1,
        JobValueA = @JobValueA,
        JobValueB = @JobValueB,
        JobState = @JobState,
        JobHumidity = @JobHumidity,
        JobTemperature = @JobTemperature,
        JobAnalog = @JobAnalog,
        Age = @Age

    Where SerialNumber = @SerialNumber and ServerId = @ServerId
  End Else
  Begin
    Update Sensors
    Set Active = 1, Age = @Age
    Where SerialNumber = @SerialNumber and ServerId = @ServerId
  End

End


Declare @Result int
Select 
@Result = IsNull(SensorId, 0), 
@EntranceDoor = IsNull(EntranceDoor, 0), 
@ExitDoor = IsNull(ExitDoor, 0), 
@SwitchIO = IsNull(SwitchIO, 0),
@CombineA = IsNull(CombineValueA, 0),
@CombineB = IsNull(CombineValueB, 0)
From Sensors (NOLOCK)
Where SerialNumber = @SerialNumber and ServerId = @ServerId

Return @Result