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, Jul 13 '16 at 12:51
Output is different comparing to first query - it should be the same — Mati, Jul 13 '16 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: http://stackoverflow.com/questions/2944511/sql-server-in-clause-with-a-declared-variable — alan, Jul 13 '16 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 https://msdn.microsoft.com/en-us/library/ms188001.aspx. — Maverick, Jul 13 '16 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, Jul 13 '16 at 13:03

HoneyBadger · Accepted Answer · 2016-07-13T13:17:16.373

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 '16 at 13:17

answered Jul 13 '16 at 12:53

HoneyBadger

14,750
3
34
48

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 Jul 13 '16 at 12:55
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 – Mati Jul 13 '16 at 13:09
@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 – HoneyBadger Jul 13 '16 at 13:11
I already had group by before i comment this, so that's not the problem. – Mati Jul 13 '16 at 13:14
1

@Pro100, Updated answer – HoneyBadger Jul 13 '16 at 13:17
Yep, i forgot about that earlier, just gave you that "rep" – Mati Jul 13 '16 at 14:19

score 1 · Answer 2 · answered Jul 13 '16 at 12:59

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]

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. http://sqlperformance.com/2012/07/t-sql-queries/split-strings — Sean Lange, Jul 13 '16 at 13:50
Thank you Sean Lange for performance notes. This is one of solution, but, as you said, there are many other option. — Lucarob, Jul 13 '16 at 14:25

score 1 · Answer 3 · answered Jul 13 '16 at 13:29

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 '16 at 13:29

Rahul Hendawe

902
1
14
39

Variable in query doesn't work

3 Answers3