T-SQL Querying a table using a variable

Question

I have been tasked with updating a stored procedure and I'm trying to figure out the most efficient way of performing the select. Here is the current code, vaultID and clientID are input parameters:

SELECT 
   @siteID = Site.siteID, 
   @siteGroupID = Site.siteGroupID,
   @customerID = Customer.customerID
FROM 
   Customer
INNER JOIN 
   Site ON Customer.siteID = Site.siteID
WHERE 
   Site.phoneNumberIVR = @vaultID 
   AND Site.production = 1
   AND Customer.clientID = @clientID

The update I'm working on has to do with the @clientID variable. If a record has a specific @siteGroupID, the clientID needs to be passed in as it was received. If the record has a different type of @siteGroupID, the @clientID variable needs to be appended with a specific prefix. That prefix is also going to be stored on the site table.

I realize I can make a call to the site table initially to get the prefix, and then modify the @clientID variable, but I'm trying to figure out if there is a way to do this with just one call. I've been trying different case statements but I'm not sure this is even feasible.

valverij · Accepted Answer · 2014-01-13 17:49:58Z

1

If I understand your issue correctly, then you should be able to throw a CASE in your SELECT with your condition to do the appending:

SELECT 
    @siteID = Site.siteID, 
    @siteGroupID = Site.siteGroupID,
    @customerID = Customer.customerID,
    @clientID = 
        CASE
            WHEN Site.siteGroupID = 1234 THEN Site.Prefix + @clientID
            ELSE @clientID
        END
FROM 
    Customer
    inner join Site on Customer.siteID = Site.siteID
WHERE 
    Site.phoneNumberIVR = @vaultID 
    and Site.production = 1
    and Customer.clientID = @clientID

Of course, depending on the datatypes of @clientID and Site.Prefix, you might have to do something other than a simple + to do the appending. For example if both were integer datatypes, you can append then with some CONVERT calls:

@clientID = CONVERT(integer, CONVERT(varchar, Site.Prefix) + CONVERT(varchar, @clientID))

edited Jan 13, 2014 at 17:49

answered Jan 13, 2014 at 17:44

valverij

4,9711 gold badge25 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TrevorGoodchild Over a year ago

Thank you!!!! I was actually fairly close but had an equals sign in there that was blowing it up. Thanks for the quick response

Collectives™ on Stack Overflow

T-SQL Querying a table using a variable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related