May 27, 2015 at 11:12 am
Hello,
I am looking to create a simple IF Else statement in a SPROC but my syantax seems to be wrong. Can someone advise what I am doing wrong?
IF (vchDept='IT')
BEGIN
THEN 1
END
ELSE
IF vchEmail like '%@abc.com' AND vchDept<>1
BEGIN
THEN 2
END
ELSE
IF vchEmail like '%@123.com' AND vchDept<>1
BEGIN
THEN 3
END
ELSE
END
END
END
May 27, 2015 at 11:15 am
Meatloaf (5/27/2015)
Hello,I am looking to create a simple IF Else statement in a SPROC but my syantax seems to be wrong. Can someone advise what I am doing wrong?
IF (vchDept='IT')
BEGIN
THEN 1
END
ELSE
IF vchEmail like '%@abc.com' AND vchDept<>1
BEGIN
THEN 2
END
ELSE
IF vchEmail like '%@123.com' AND vchDept<>1
BEGIN
THEN 3
END
ELSE
END
END
END
And what is the error message you are getting? What is the code snippet above supposed to be accomplishing?
May 27, 2015 at 11:35 am
Maybe you're looking for a CASE expression which is used inside a SELECT statement.
Or maybe you need to define the actions used within the IF statements.
May 27, 2015 at 2:00 pm
Meatloaf (5/27/2015)
Hello,I am looking to create a simple IF Else statement in a SPROC but my syantax seems to be wrong. Can someone advise what I am doing wrong?
IF (vchDept='IT')
BEGIN
THEN 1
END
ELSE
IF vchEmail like '%@abc.com' AND vchDept<>1
BEGIN
THEN 2
END
ELSE
IF vchEmail like '%@123.com' AND vchDept<>1
BEGIN
THEN 3
END
ELSE
END
END
END
The first thing to know is that THEN is not used with IF for SQL Server. Also, while it appears that what you want is a result of either 1, 2, or 3, you don't have any portion of the code that indicates what you'll be doing with that result. Does this choice of values need to be part of a query, or are you perhaps needing to place the value into a variable that gets used later in the SPROC ? I ask, because just taking out the THEN keyword isn't going to solve the problem. IF statements are designed to take actions, and simply stating 3, or THEN 3, doesn't actually do anything, and that's the problem.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 27, 2015 at 3:03 pm
Thank you for all replies.
In hopes to simplify, here is my Pseudo code:
IF (vchDept='IT') THEN iDeptID=1
ELSE
IF (vchEmail like '%@abc.com' AND vchDept<>1) THEN iDeptID=2
ELSE
IF (vchEmail like '%@123.com' AND vchDept<>1) THEN iDeptID=3
ELSE
iDeptID=5
END
END
END
May 27, 2015 at 3:07 pm
Meatloaf (5/27/2015)
Thank you for all replies.In hopes to simplify, here is my Pseudo code:
IF (vchDept='IT') THEN iDeptID=1
ELSE
IF (vchEmail like '%@abc.com' AND vchDept<>1) THEN iDeptID=2
ELSE
IF (vchEmail like '%@123.com' AND vchDept<>1) THEN iDeptID=3
ELSE
iDeptID=5
END
END
END
Nope, still clear as mud on dark night.
Please show us the stored procedure and what you are trying to accomplish. We can't see what you see unless you show it to us.
May 27, 2015 at 3:20 pm
My apologies, and thank you for your patience.
Unfortunately, I do not have a SPROC to share.
What I am trying to do is:
1. Take a data file that contains the text name of a department (e.g. Information Technology)
2. Import file into staging table
3. In staging table, Convert the department name (e.g. Information Technology) in text to the numeric equivelant (e.g. 1) - Step I need help on)
4. Then the data will be ready to process into Production
We have a 3rd party interface containing a drop down of department text values only. However, our database normalizes using numeric values and I am trying to cross reference.
May 27, 2015 at 4:14 pm
It seems that you really need a CASE expression. I'm not sure how would you need to use it, so I'm giving you 3 options.
--Creating some sample data
CREATE TABLE #Staging(
someColumn int,
vchDept varchar(100),
vchEmail varchar(100)
);
INSERT INTO #Staging VALUES
(1, 'IT', 'it@abc.com'),
(2, 'IT', 'it@123.com'),
(3, 'IT', 'it@whocares.com'),
(4, 'HR', 'it@abc.com'),
(5, 'HR', 'it@123.com'),
(6, 'HR', 'it@whocares.com');
--Validate Data
SELECT * FROM #Staging;
GO
--Use a query
SELECT *,
CASE WHEN vchDept='IT' THEN 1
WHEN vchEmail like '%@abc.com' THEN 2
WHEN vchEmail like '%@123.com' THEN 3
ELSE 5 END AS iDeptID
FROM #Staging;
--Update a column
ALTER TABLE #Staging ADD iDeptID int; --Not needed if the column already exists
GO
UPDATE s SET
iDeptID = CASE WHEN vchDept='IT' THEN 1
WHEN vchEmail like '%@abc.com' THEN 2
WHEN vchEmail like '%@123.com' THEN 3
ELSE 5 END
FROM #Staging s;
SELECT * FROM #Staging;
ALTER TABLE #Staging DROP COLUMN iDeptID; --To clean changes
--Use a computed column
ALTER TABLE #Staging
ADD iDeptID AS (CASE WHEN vchDept='IT' THEN 1
WHEN vchEmail like '%@abc.com' THEN 2
WHEN vchEmail like '%@123.com' THEN 3
ELSE 5 END);
SELECT * FROM #Staging;
GO
DROP TABLE #Staging; --Clean my sandbox
May 28, 2015 at 8:06 am
Thank you Luis! This sample is very much appreciated.
And no, the code would not go directly in a Production environment, my intent was to explain how my process theoretically works.
May 28, 2015 at 9:02 am
I hope that you understand that these are 3 examples from which you need to choose one, according to your requirements. Feel free to ask any questions that you have.
Note that I removed the vchDept<>1 condition. This is because CASE conditions are evaluated from left to right and if a condition is true, the following options won't be evaluated.
You can see as well that I included sample data so you can copy, paste and execute the code without effort. When asking for help, it's considered polite to do it (even for theoretical questions) so we can create and test solutions without wasting efforts on creating such sample data.
May 28, 2015 at 10:12 am
Meatloaf (5/27/2015)
Thank you for all replies.In hopes to simplify, here is my Pseudo code:
IF (vchDept='IT') THEN iDeptID=1
ELSE
IF (vchEmail like '%@abc.com' AND vchDept<>1) THEN iDeptID=2
ELSE
IF (vchEmail like '%@123.com' AND vchDept<>1) THEN iDeptID=3
ELSE
iDeptID=5
END
END
END
You have a problem with "vchDept". First you compare it to a char/varchar value, then you compare it to an int.
vchDept='IT' then vchDept<>1
It's not clear how that value is used.
Don Simpson
May 29, 2015 at 8:20 am
[font="Comic Sans MS"]To Mr. Luis Cazares
I am impressed with the extra effort you took in creating the demo table and test data for the original poster struggling even with the basic syntax of a T-SQL IF statement ("THEN" ??? hint: "books on line"...).[/font]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply