Variable in query doesn't work

Question

I'm facing some problem with a SQL query, I'm creating a variable :

 declare @material varchar(500)
 declare @typ varchar(200)
 declare @strsql varchar(max)
 set @typ = 'papier'

 select @material = (SELECT + rtrim(ltrim([grupa])) + ','
                     FROM [test].[dbo].[GT] 
                     WHERE (typ = @typ) FOR XML PATH(''))
 set @material =  left(@material, len(@material)-1)
 set @material  = replace(@material, ',' ,''',''')
 set @material = '''' + @material + ''''

 select @material

The output from variable is :

'test','test2'

And here is a little part of my main code :

 SELECT
     Number,
     isnull(sum((case 
                    when [Group] in ('test','test2') 
                       then isnull(cast([Quantity] as int), 0) 
                 end)), 0) as other
 FROM
     [dbo].[test-table]

which works correct but when I'm trying to do this like that :

 SELECT
     Number,
     isnull(sum((case 
                    when [Group] in (@material) 
                       then isnull(cast([Quantity] as int), 0) 
                  end)), 0) as other
 FROM
     [dbo].[test-table]

Output is wrong (different). Can anyone tell me why? It's kinda this same.

Output is wrong, can anyone tell me, why? What is the output? — Rahul Tripathi
– Rahul Tripathi, Commented Jul 13, 2016 at 12:51
Output is different comparing to first query - it should be the same — Mati
– Mati, Commented Jul 13, 2016 at 12:51
You're attempting to place a comma separated list of VARCHARs into a variable. You can't do this in SQL without dynamic SQL. See this original post: stackoverflow.com/questions/2944511/… — alan
– alan, Commented Jul 13, 2016 at 12:54
You have to declare the SqlParameter NVARCHAR(MAX) and later on you have to use EXEC sp_exeutesql SQL, SqlParameter. You the link to get more inside msdn.microsoft.com/en-us/library/ms188001.aspx. — Maverick
– Maverick, Commented Jul 13, 2016 at 12:58
you could drop the search values into a temporary table or a table variable - then do select ... where [group] in (select field form #tempTable) — Cato
– Cato, Commented Jul 13, 2016 at 13:03

HoneyBadger · Accepted Answer · 2016-07-13 13:17:16Z

1

It's kind of the same, but it's not the same. What you are looking to get is: in ('test','test2'), what you get is: in (''test','test2'').

You will have to build the rest of your query dynamically, something like:

DECLARE @SQL NVARCHAR(MAX) = 'SELECT
 Number
 ,isnull(sum((case when [Group] in ('+ @material + ') 
 then isnull(cast([Quantity] as int),0) end)),0) as other
 from [dbo].[test-table]'

EXEC sys.sp_executesql @SQL

If you say the output is wrong, it's best to show the actual output and why it is wrong. Saying "Output is different comparing to first query - it should be the same" is not enough, in most cases.

edit:

If you don't want to use dynamic sql (which makes sense), you could do something like this (which is actually much more readable than dynamic):

SELECT      Number
,           ISNULL(SUM(data),0) as other
FROM        (
                SELECT  Number
                ,       CASE WHEN [Group] IN (SELECT grupa FROM [dbo].[GT] WHERE typ = @typ) 
                                THEN ISNULL(CAST([Quantity] AS INT), 0) 
                         END data
                FROM    [dbo].[test-table] 
            ) TT
GROUP BY    Number

I am making a lot of assumptions about your goal, your schema and your data here though. Without more info, this is probably the best I can do...(There may be some performance tweaking to be done, but this is the essence)

edited Jul 13, 2016 at 13:17

answered Jul 13, 2016 at 12:53

HoneyBadger

15.2k3 gold badges36 silver badges50 bronze badges

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

6 Comments

Mati Over a year ago

I'v already test it earlier and when im doing it like you it works but im having tons of subquerys so i kinda cant declare so many variables (i mean i can but its pointless), is there any chance to correct mine code.For example if output from variable will be test','test2 instead of 'test','test2' then it will work correctly?

Mati Over a year ago

When im doing this the way you add in your edit it shows me error : Cannot perform an aggregate function on an expression containing an aggregate or a subquery. Cause of this SUM

HoneyBadger Over a year ago

@Pro100 Please see further edit. Obviously, a group by has to be defined. I added one, but because of you summation with case, maybe more attributes have to be added to the group by. On second thought, it's probably a different problem.... I'll get back on that

Mati Over a year ago

I already had group by before i comment this, so that's not the problem.

HoneyBadger Over a year ago

@Pro100, Updated answer

|

Lucarob · Accepted Answer · 2016-07-13 12:59:29Z

1

Try to use a function for split the string:

CREATE FUNCTION [dbo].[FN_SplitString](@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (Items nvarchar(4000))
AS
    BEGIN
    IF @String IS NULL RETURN
    IF LTRIM(RTRIM(@String)) = '' RETURN
    DECLARE @INDEX INT
    DECLARE @SLICE nvarchar(4000)
    SELECT @INDEX = 1
    WHILE @INDEX !=0
        BEGIN   
            SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)

        IF @INDEX !=0
            SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
        ELSE
            SELECT @SLICE = @STRING
        INSERT INTO @Results(Items) VALUES(@SLICE)
        SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
        IF LEN(@STRING) = 0 BREAK
END
RETURN

Then use it in this way:

SELECT
 Number
 ,isnull(sum((case when [Group] in (SELECT  Items, FROM FN_SplitString(@material,',')) 
 then isnull(cast([Quantity] as int),0) end)),0) as other
 from [dbo].[test-table]

answered Jul 13, 2016 at 12:59

Lucarob

664 bronze badges

2 Comments

Sean Lange Over a year ago

I would highly suggest NOT using this splitter. There is no need for looping here at all. Here are many other options which will outperform the nibbler style looping splitter. sqlperformance.com/2012/07/t-sql-queries/split-strings

Lucarob Over a year ago

Thank you Sean Lange for performance notes. This is one of solution, but, as you said, there are many other option.

Rahul Hendawe · Accepted Answer · 2016-07-13 13:29:48Z

1

In SQL Server, if you pass a comma separated list as a variable in IN Clause in T-SQL, it would not give error but you will not even get expected result either.

There are two solutions to handle this:

Using Dynamic query
Using Split function

answered Jul 13, 2016 at 13:29

Rahul Hendawe

9221 gold badge15 silver badges41 bronze badges

Collectives™ on Stack Overflow

Variable in query doesn't work

3 Answers 3

6 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related