SQL Server : Empty variable in another variable

Question

I have 5 variables @var1 varchar(20) and so on…. I have another @varlist which would hold all the variables which are not NULL or ''.

Example: tableA

Variable   Value 
@var1      var1
@var2      var2
@var3      var3
@var4      var4
@var5      var5

SET @var1 = 1 , @var2 = '' , @var3 = 3, @var4 = '' ,@var5 = 8

So I want @varlist to have all variable values where variables are not null

So, @varlist = var1 ,var3, var5

My script (not working)

SET @varlist =  (select value from tableA where variable = @var1 and @var1 IS NOT NULL and @var1 <> ‘’) +’,’ +
(select value from tableA where variable = @var2 and @var2 IS NOT NULL and @var2 <> ‘’) +’,’ +
(select value from tableA where variable = @var3 and @var3 IS NOT NULL and @var3 <> ‘’) +’,’ +
(select value from tableA where variable = @var4 and @var4 IS NOT NULL and @var4 <> ‘’) +’,’ +
(select value from tableA where variable = @var5 and @var5 IS NOT NULL and @var5 <> ‘’)

In SET @var1 = 1 , @var2 = '' , @var3 = 3, @var4 = '' ,@var5 = 8; @var2 = '' for example is not null, it should be @var2 = null, if you meant null here. — user353gre3
– user353gre3, Commented Apr 3, 2014 at 17:35

M.Ali · Accepted Answer · 2014-04-03 18:15:46Z

3

Try something like this ....

DECLARE 
@var1 VARCHAR(1),
@var2 VARCHAR(1),
@var3 VARCHAR(1),
@var4 VARCHAR(1),
@var5 VARCHAR(1)

SELECT @var2 = 2 , @var4= 4, @var5 = 5


SELECT STUFF(ISNULL(',' + @var1, '') +ISNULL(',' + @var2, '') +ISNULL(',' + @var3, '') +
        ISNULL(',' + @var4, '') +ISNULL(',' + @var5, '') , 1,1,'')

After you new request , see below

DECLARE 
@var1 VARCHAR(1),
@var2 VARCHAR(1),
@var3 VARCHAR(1),
@var4 VARCHAR(1),
@var5 VARCHAR(1)

SELECT @var2 = 2 , @var4= 4, @var5 = 5

DECLARE @VarList NVARCHAR(MAX);

SET @VarList  =       CASE WHEN @var1 IS NOT NULL THEN N',@var1' ELSE N'' END
                    + CASE WHEN @var2 IS NOT NULL THEN N',@var2' ELSE N'' END
                    + CASE WHEN @var3 IS NOT NULL THEN N',@var3' ELSE N'' END
                    + CASE WHEN @var4 IS NOT NULL THEN N',@var4' ELSE N'' END
                    + CASE WHEN @var5 IS NOT NULL THEN N',@var5' ELSE N'' END

SET @VarList = STUFF(@VarList,1,1,'')

SELECT @VarList

RESULT: @var2,@var4,@var5

edited Apr 3, 2014 at 18:15

answered Apr 3, 2014 at 17:40

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

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

5 Comments

Bhupinder Singh Over a year ago

So, Instead of getting the actual value of the variable, I want the alias. so I want to see var1 instead of what we set the value of variable which is 2 or whatever

Andriy M Over a year ago

@BhupinderSingh: In your question it says: So I want @varlist to have all variable values where variables are not null. Apparently you need to edit that sentence and clarify there that you want names, not values.

Bhupinder Singh Over a year ago

Yes perhaps. Lets call the column name 'values' as 'Names'. SO I want names. :)

Andriy M Over a year ago

@BhupinderSingh: Please edit your question to make that point clearer.

M.Ali Over a year ago

@BhupinderSingh you should accept an answer if it helps you. I have just seen your profile you havent accepted any of the answers to your previous question. Accepting an answer ensures to other users that a particular solution solved the issue.

user353gre3 · Accepted Answer · 2014-04-03 17:31:11Z

0

See the example below if your want to have all variables that are not null.

declare @var1 as char(1)
declare @var2 as char(1)
declare @var3 as char(1)
declare @var4 as char(1)
declare @var5 as char(1)
declare @varlist as varchar(20)

 SET @var1 = 1
 SET @var2 = null
 SET @var3 = 3
 SET @var4 = null 
 SET @var5 = 8

 select @varlist =  coalesce(@var1, '') + coalesce(', ' + @var2, '') + coalesce(', ' +    @var3, '') +
 coalesce(', ' + @var4, '') + coalesce(', ' +  @var5,' ');

answered Apr 3, 2014 at 17:31

user353gre3

2,7554 gold badges26 silver badges27 bronze badges

3 Comments

M.Ali Over a year ago

What happens when 1st variable is null

Erran Morad Over a year ago

Binaya, please do not delete this answer in case you are think of doing so.

user353gre3 Over a year ago

Borat, why would I think about deleting my post. The forum is about learning and I am glad I learnt a better way from M. Ali and M. Ali already got my up vote.

Collectives™ on Stack Overflow

SQL Server : Empty variable in another variable

2 Answers 2

5 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related