CODEWITHSUNDEEP
sql-serverfunctiont-sql
Brenda Chen
Brenda Chen

Reputation: 1

SQL function: use column 1 and column 2 to give column 3

Looking to write a function where I can call from any two columns in my table, to get the resulting column in the same table. Table: Acme Columns: CustomerID, LastName, FirstName, EmailAdress, MailingAddress, City, State, Zip

Can I get EmailAddress from just FirstName and Last Name? Can I get Zip from CustomerID and City?

What I have so far for EmailAddress:

CREATE FUNCTION fnEmailAddress
(@LastName varchar (255), @FirstName varchar (255))

RETURNS table

RETURN (SELECT EmailAddress
FROM Acme
WHERE FirstName = @FirstName AND LastName = @LastName);

END

So the following would give me [email protected]: EXEC fnEmailAddress ('Brenda'+'Chen')

But it doesn't work :(

Upvotes: 0

Views: 734

Answers (2)

KtX2SkD
KtX2SkD

Reputation: 752

If I understand you correctly, you want a function that can adapt to several possibilities of input combinations, because what columns you will have ready as input might differ in each function call. If so:

  1. Clearly, your function must have as many parameters as column possibilities you have, i.e. for any column that can possibly become a "column 1" or "column 2", add a parameter.
  2. Since you have many parameters, you need to decide which ones are to be used, or if the inputs are irrational to begin with. For example:
    • What to do if you have 4 out of 4 parameters provided? Refuse to work? Or can the function adapt to that?
    • What if parameters 1 and 4 are provided but your function isn't meant to work with that? It's meant to work with 1 + 2 or 3 + 4 for example.

  3. Lastly, choose one of either:

    • Set any unnecessary inputs to NULL, then query once with a WHERE clause that auto-passes any NULL inputs (example: WHERE COL1 = ISNULL(@PAR1, COL1) AND COL2 = ISNULL(@PAR2, COL2), needs more logic for NULLable columns),
    • Or, utilizing IF/ELSE commands to switch between several different queries (different queries is probably maintenance headache, so avoid when possible).

Below is an example based on something I already have, although it uses the IF/ELSE approach. Try to do NULL/ISNULL instead:

CREATE FUNCTION [dbo].[SomeWackoName]
    (@FirstArm tinyint, @SecondArm tinyint, @FirstLeg tinyint, @SecondLeg tinyint)
RETURNS @Results TABLE(
      [Value] varchar(60)
) AS
BEGIN

    -- For input, we need both arms, or both legs. It is acceptable to provide three inputs.
    -- It is unacceptable to provide all four inputs (confusing), or failing to provide any full pair (like one input, or two unpaired inputs).
    DECLARE @Validator tinyint = CASE WHEN @FirstArm + @SecondArm IS NULL THEN 0 ELSE 1 END + CASE WHEN @FirstLeg + @SecondLeg IS NULL THEN 0 ELSE 2 END
    IF @Validator NOT BETWEEN 1 AND 2 RETURN --THROW 50000, 'INCORRECT INPUT WHYYYYYYYYYYYYYYY WHYYYYYYYYYYYYYYYYYY', 0;

    -- Depending on input provided, decide how to behave.
    IF @Validator = 1
        INSERT INTO @Results SELECT 'I GOT THE ARMS. POPULATE WITH LEGS!!'
    ELSE IF @Validator = 2
        INSERT INTO @Results SELECT 'I GOT THE LEGS. POPULATE WITH ARMS!!'

    -- Return results.
    RETURN

END

Upvotes: 1

JayaPrakash
JayaPrakash

Reputation: 199

CREATE FUNCTION fnEmailAddress
(
 @LastName varchar (255)=null, @FirstName varchar (255)=null, 
 @streetNumber varchar(255)=null,@streetname varchar (255)=null
)
RETURNS table
Select case when @firstname+@lastname is not null then EMail Else LastName End Column3
from person 
where                                                                    
(
case when @firstname+@lastname is not null                               
     then case when FirstName=@firstname and LastName=@lastname
               then 1 else 0 End
     when @StreetNumber+@streetname is not null 
     then case when StreetName=@Streetname and StreetNumber=@streetNumber
               then 1 Else 0 End 
End 
)=1

The function should have 4 input parameters, pass first two or the last two parameters only.

CASE in the select statement will get 'Email' or 'LastName' depending upon input parameter null or not.

Similarly, CASE in the where statement will choose which filter should apply depending upon input parameter null or not, and results will be fetched based on that.

Upvotes: 0

Related Questions