
CREATE PROCEDURE dbo.MapValue @DestDB VARCHAR(255), @TableName VARCHAR(255), @KeyFieldName VARCHAR(255), @MapFieldName VARCHAR(255), @Output INTEGER OUTPUT, @NoMatchReturnFirst BIT = 1 AS
SET NOCOUNT ON
/*
  Created by Jacob Pedersen 20070206

  Description: Maps a resource from source database  in destination database. See example below

  Params:
  @DestDB		: Destination database. Format "[databasename]."
  @TableName		: Same name in destination and source database
  @KeyFieldName	: The value returned in @Output
  @MapFieldName	: Data which is mapped, for example a roomtype name, a segment name, a pointofsale name etc
  @Output		: Key value of the row found in destination database, also used for Input of KeyValue in source database
  @NoMatchReturnFirst	: If no row was found should we return NULL or the first row in table if one is available

  Example:

  DECLARE @Output INT
  SET @Output = 8 -- Room 101 key value in source
  EXEC dbo.MapValue '2picasso_3_36', 'dbo.Rooms', 'RoomNumber', 'Number', @Output OUTPUT
  PRINT @Output -- returns 6 which is the key value of room 101 in destination
 
*/
IF NOT (@Output IS NULL)
BEGIN
  DECLARE @Str NVARCHAR(255), @Params NVARCHAR(255), @Value VARCHAR(255)

  SET @Str = N'SELECT @Value='+@MapFieldName+' FROM '+@TableName+' WITH (NOLOCK) WHERE '+@KeyFieldName+'='+CONVERT(VARCHAR, @OutPut)
  SET @Params = N'@Value VARCHAR(255) OUTPUT'
  EXEC sp_executesql @Str, @Params, @Value OUTPUT

  SET @Output = NULL -- DO NOT DELETE. If no rows are found it will keep the old input value
  SET @Str = N'SELECT @OutPut='+@KeyFieldName+' FROM '+@DestDB+@TableName+'  WITH (NOLOCK) WHERE '+@MapFieldName+'='''+@Value+''''
  SET @Params = N'@OUTPUT INTEGER OUTPUT'
  EXEC sp_executesql @Str, @Params, @Output OUTPUT

  IF (@Output IS NULL) AND (@NoMatchReturnFirst = 1)
  BEGIN
    SET @Str = N'SELECT TOP 1 @OutPut='+@KeyFieldName+' FROM '+@DestDB+@TableName+' WITH (NOLOCK) ORDER BY '+@KeyFieldName
    SET @Params = N'@OUTPUT INTEGER OUTPUT'
    EXEC sp_executesql @Str, @Params, @Output OUTPUT -- NB! @Params is reused!
  END
END

