July 10, 2012 at 3:16 pm
How would I populate a variable based on a IF clause? For example,
I am looking at two tables. One table is the primary and if the primary has '*' this character I want to look into the secondary table and populate my variable.
July 10, 2012 at 3:22 pm
Not sure I really understand your question... something like this?
DECLARE @Var VARCHAR(10)
SET @Var = 'abc'
IF (SELECT 1) = 1
BEGIN
SET @Var = (SELECT 'def')
END
PRINT @Var
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 10, 2012 at 3:29 pm
something like this?
DECLARE @Primary TABLE
( iD INT IDENTITY(1,1)
,Charac CHAR(1)
)
DECLARE @Secondary TABLE
( iD INT IDENTITY(1,1)
,Charac CHAR(1)
,Name VARCHAR(100)
)
INSERT INTO @Primary
SELECT '*'
UNION ALL SELECT '/'
UNION ALL SELECT '&'
UNION ALL SELECT '#'
INSERT INTO @Secondary
SELECT '*' , 'Asterix'
UNION ALL SELECT '/' , 'Slash'
UNION ALL SELECT '&' , 'Ampersand'
UNION ALL SELECT '#' , 'Hash'
DECLARE @NameVariable VARCHAR(100)
,@CharToFind CHAR(1)
SET @CharToFind = '#'
SELECT @NameVariable = sec.Name
FROM @Secondary sec
WHERE sec.Charac = @CharToFind
AND EXISTS ( SELECT 1
FROM @Primary pr
WHERE pr.Charac = @CharToFind
)
SELECT ISNULL (@NameVariable ,'Not available') [Value]
If not, then please set-up the sample like i had given, along with the expected result, CLEARLY!
July 10, 2012 at 4:54 pm
I think this is what you are looking for:
DECLARE @d1int = 1,
@v-2 varchar(2);
DECLARE @table1 TABLE
(
idint,
col2varchar(2)
);
DECLARE @table2 TABLE
(
idint,
col2varchar(2)
);
INSERT INTO @table1
VALUES (1,'aa'),(2,'bb'),(3,'d*')
INSERT INTO @table2
VALUES (1,'xx'),(2,'yy'),(3,'zz')
SELECT @v-2 = col2 FROM @table1 WHERE id = @d1;
IF @v-2 LIKE '%*%'
SELECT @v-2 = col2 FROM @table2 WHERE id = @d1
SELECT @v-2
Test this out with @d=3
-- Itzik Ben-Gan 2001
July 10, 2012 at 5:02 pm
Thank you! This looks like what I am looking for. I will test it out in the morning and let you know!
July 11, 2012 at 8:20 am
Alan - Thank you very much. This did the trick.
July 11, 2012 at 8:35 am
Sony Francis @EY (7/11/2012)
We can simplify the above query toselect @v-2 =case when t1.col2 like '%*%' then t2.col2 else t1.col2 end
from @table1 t1, @Table2 t2
where t1.id=@d1 and t2.id=@d1
Yes, this is better (I was being lazy). Thanks.
DECLARE @d1int = 3,
@v-2 varchar(2);
DECLARE @table1 TABLE
(
idint,
col2varchar(2)
);
DECLARE @table2 TABLE
(
idint,
col2varchar(2)
);
INSERT INTO @table1
VALUES (1,'aa'),(2,'bb'),(3,'d*')
INSERT INTO @table2
VALUES (1,'xx'),(2,'yy'),(3,'zz')
SELECT @v-2 =
CASE
WHEN t1.col2 like '%*%' THEN t2.col2
ELSE t1.col2
END
FROM @table1 t1, @Table2 t2
WHERE t1.id=@d1 and t2.id=@d1
SELECT @v-2
-- Itzik Ben-Gan 2001
July 11, 2012 at 8:36 am
SQLSeTTeR (7/11/2012)
Alan - Thank you very much. This did the trick.
No problem.:cool:
-- Itzik Ben-Gan 2001
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy