Stored Procedure to Return Multiple Values based on different simultaneous Input conditions

Question

I created a stored procedure that gets a person's name, matches the name with values in a table and return the correct format via output variable. If the name is not in the table/database the stored procedure simply output the original entry. This part works fine.

Now I want to do both first and last names together in the same stored procedure; example:

1. If first name has a match, output matched - output variable
2. If Last name has a match send out a match - output variable
3. If both names match send out the match for both BUT via different output variable.

I am having a hard time because I am not sure how to process both names, and get both or one, or the other:

I could do separate Procedures but that would not be wise.

Table Name Is: (StringFormat)

Stored Procedure:

CREATE PROCEDURE [dbo].[uspFormat]

@FName VARCHAR(30),
@FNameMatchedValue VARCHAR(30) OUTPUT

AS 
BEGIN

SET NOCOUNT ON

DECLARE @ValueMatched VARCHAR(30)

SELECT @ValueMatched =  StringValue
FROM StringFormat WHERE MatchedValue = @FName;

IF @ValueMatched IS NOT NULL

BEGIN
SET
@FNameMatchedValue = @ValueMatched
END

RETURN 0

END

Thanks for looking:

UPDATE: Please Review My Solution:

Okay after posting I went back and modified the code which I had done wrong before: It is working but returning unique values even when one name is a match. However I am not sure this is the best way to do it:

Another Change: I simply used two viriables to get the matched rather then one:

@FirstNameMatched and @LastNameMatched

Code after modification:

CREATE PROCEDURE [dbo].[uspFormat]

@FName VARCHAR(30),
@LName VARCHAR(30),
@FNameMatchedValue VARCHAR(30) OUTPUT,
@LNameMatchedValue VARCHAR(30) OUTPUT

AS 
BEGIN

SET NOCOUNT ON

DECLARE @FirstNameMatched VARCHAR(30)
DECLARE @LastNameMatched VARCHAR(30)

SELECT @FirstNameMatched =  StringValue
FROM StringFormat WHERE MatchedValue = @FName;

IF @FirstNameMatched IS NOT NULL

BEGIN
SET
@FNameMatchedValue = @FirstNameMatched
END

ELSE
BEGIN
SET @FNameMatchedValue = @FName
END

BEGIN 
SELECT @LastNameMatched =  StringValue
FROM StringFormat WHERE MatchedValue = @LName;

IF @LastNameMatched IS NOT NULL

BEGIN
SET
@LNameMatchedValue = @LastNameMatched
END

ELSE
BEGIN
SET @LNameMatchedValue = @LName
END
END

RETURN 0

END

Answer 1

Try:

CREATE PROCEDURE [dbo].[uspFormat]

@FName VARCHAR(30) OUTPUT,
@LName VARCHAR(30) OUTPUT,
@FNameBothMatched VARCHAR(30) OUTPUT,
@LNameBothMatched VARCHAR(30) OUTPUT

AS 
BEGIN

SET NOCOUNT ON

DECLARE @FnameMatched VARCHAR(30)
DECLARE @LnameMatched VARCHAR(30)

SELECT TOP 1 @FnameMatched =  StringValue
FROM StringFormat WHERE MatchedValue = @FName;

SELECT TOP 1 @LnameMatched =  StringValue
FROM StringFormat WHERE MatchedValue = @LName;

IF (@FnameMatched IS NOT NULL) and (@LnameMatched IS NOT NULL)

   BEGIN
     SET @FNameBothMatched = @FnameMatched
     SET @LNameBothMatched = @LnameMatched
   END
ELSE
   BEGIN
     IF (@FnameMatched IS NOT NULL)
        BEGIN
           SET @Fname = @FnameMatched
        END
     IF (@LnameMatched IS NOT NULL)
        BEGIN
           SET @Lname = @LnameMatched
        END
   END


RETURN 0

END

---

Admittedly I did not test this but what I am trying to do here following your own requirements are this:

1. if both Lastname and Firstname has matched then it would go both to @FNameBothMached and @LNameBothMatched
2. if only Firstname has matched then it would change the passed @Fname otherwise @Fname passed value will remain as it is
3. if only Lastname has matched then it would change the passed @Lname otherwise @Lname passed value will remain as it is

---

Scenarios:

Scenario 1: Fistname has match but Lastname has no match

Passed Value

Firstname : Macdonald (has match)

Lastname : Obrian (has no match)

Result Scenario 1

Firstname : Mac Donald

Lastname : Obrian

Scenario 2: Fistname has no match but Lastname has match

Passed Value

Firstname : McDonald (has no match)

Lastname : Obrien (has match)

Result Scenario 2

Firstname : McDonald

Lastname : O'brien

Scenario 3: Fistname has no match and Lastname has no match as well

Passed Value

Firstname : McDonald (has no match)

Lastname : Obrian (has no match)

Result Scenario 3

Firstname : McDonald

Lastname : Obrian

Scenario 4: Both Fistname and Lastname has match

Passed Value

Firstname : Macdonald (has match)

Lastname : Obrien (has match)

Result Scenario 4

Firstname : MacDonald

Lastname : O'brien

Answer 2

Yes there is a better way.

CREATE PROCEDURE [dbo].[uspFormat]
@FName VARCHAR(30),
@LName VARCHAR(30)
AS 
BEGIN

SET NOCOUNT ON

select
(select StringValue FROM StringFormat WHERE MatchedValue = @FName) as FNameMatchedValue,

(select StringValue FROM StringFormat WHERE MatchedValue = @LName) as LNameMatchedValue

RETURN 0

END

Above way return a DataTable to you. but if You want the output using output type variable then there also a better solution exists

CREATE PROCEDURE [dbo].[uspFormat]
@FName VARCHAR(30),
@LName VARCHAR(30),
@FNameMatchedValue VARCHAR(30) OUTPUT,
@LNameMatchedValue VARCHAR(30) OUTPUT
AS 
BEGIN

SET NOCOUNT ON


select @FNameMatchedValue=StringValue FROM StringFormat WHERE MatchedValue = @FName

select @LNameMatchedValue=StringValue FROM StringFormat WHERE MatchedValue = @LName

RETURN 0

END