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 @d1 int = 1,
@v varchar(2);
DECLARE @table1 TABLE
(
id int,
col2 varchar(2)
);
DECLARE @table2 TABLE
(
id int,
col2 varchar(2)
);
INSERT INTO @table1
VALUES (1,'aa'),(2,'bb'),(3,'d*')
INSERT INTO @table2
VALUES (1,'xx'),(2,'yy'),(3,'zz')
SELECT @v = col2 FROM @table1 WHERE id = @d1;
IF @v LIKE '%*%'
SELECT @v = col2 FROM @table2 WHERE id = @d1
SELECT @v
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 =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 @d1 int = 3,
@v varchar(2);
DECLARE @table1 TABLE
(
id int,
col2 varchar(2)
);
DECLARE @table2 TABLE
(
id int,
col2 varchar(2)
);
INSERT INTO @table1
VALUES (1,'aa'),(2,'bb'),(3,'d*')
INSERT INTO @table2
VALUES (1,'xx'),(2,'yy'),(3,'zz')
SELECT @v =
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
-- 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