Create variables based on condition in SQL?

Question

Can you create variables based on condition in SQL server?

for example I will pass a string like '2016,2017'

My query will create 2 variables because it has 2 years

now if I pass 10 years in the string e.g '2010,2011,...'

My query must create 10 variables too...

Is this even possible?

EDIT:

PSEUDOCODE

declare @string as nvarchar(max) = '2014,2015,2016'
declare @count as int = (select LEN(REPLACE(@string, ',', '**')) - LEN(@string) )


while @count <> 0
begin

--create variable statement here
Declare @var + @count as nvarchar(max)

set @string = (select substring(@string,0,CHARINDEX(',', @string)))

set @var +@count = @string

set @count = count - 1


end


select @var1, @var2

OUTPUT

2014 2015

EDIT 2

Reason:

I want to have a SP that will output inquired data of sales, since I already asked here on how could I get the data, I was now tasked to make it dynamic, specially the years since we have a 15 year retention. The inquired data might be from 1 to 15 years

My first idea is passing the years as a string

exec SP_test '2014,2015,2016'

which made me think of creating dynamic variables so I can substitute the years on my query, along with creating strings required by the query to produce the appropriate result

Complete psuedo code

declare @string as nvarchar(max) = '2014,2015,2016'
declare @count as int = (select LEN(REPLACE(@string, ',', '**')) - LEN(@string) )


while @count <> 0
begin

--create variable statement here
Declare @var + @count as nvarchar(max)

set @string = (select substring(@string,0,CHARINDEX(',', @string)))

set @var +@count = @string

set @count = count - 1


end

--up to this point lets say I have created @var1 and @var2

 SELECT Item,Price,Quantity,PriceYear,QuantityYear INTO TempFinal
    FROM (
    SELECT Item,Price,Quantity, 'Price+@var1' as PriceYear,'Quantity+@var1' as QuantityYear FROM @Table+@var1
    UNION ALL                                      
    SELECT Item,Price,Quantity, 'Price@var2' as PriceYear,'Quantity@var2' as QuantityYear FROM @Table+@var2
    ) MyTables

This is just an example, but the query should build the strings. The example has 2 years so the query created two select statements (that's another problem, but I think I might have a solution for that) Then the unique fields with the years involved.

  SELECT item, SUM([Price+@var1])[Price+@var1],SUM([Quantity+@var1])[Quantity+@var1],SUM([Price+@var2])[Price+@var2],SUM([Quantity+@var2])[Quantity+@var2]
    FROM (
    SELECT item,Price,Quantity,PriceYear,QuantityYear
    FROM TempFinal) up
    PIVOT (SUM(Quantity) FOR QuantityYear IN ([Quantity+@var2],[Quantity+@var2]) AS pvt
    PIVOT (SUM(Price) FOR PriceYear IN ([Price+@var1],[Price+@var2]) AS pvt2
    GROUP by item
    ORDER BY item

   --do take note that @var1 contains `2014` and @var2 contains `2015`

Got it. You will do further actions with that variables? or you just need the output as 2014 2015? — mkRabbani, May 15 '19 at 06:33
What are you trying to do? Split a string? There are techniques to do that several duplicate questions. Since SQL deals with sets, you'll get back a *set* of items — Panagiotis Kanavos, May 15 '19 at 06:39
@PanagiotisKanavos sadly its not as simple as splitting a string... will edit my question, but I do believe I cannot do it in SQL, I just thought Its possible and others might have a idea on how to do it. — Mr.J, May 15 '19 at 06:43
This looks like a case of [the XY Problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). You have a problem X (how to split a string) and assume that Y is the solution (dynamic variable declaration). When Y fails, you ask about Y not X. What is the *actual* question? — Panagiotis Kanavos, May 15 '19 at 06:44
@Mr.J what are you trying to do? Splitting is a solved problem, one way or another. Your desired result shows the split results *pivoted*. That's solved too, either through PIVOT or a solution using `GROUP BY`. — Panagiotis Kanavos, May 15 '19 at 06:46
@Mr.J if your question is "how to dynamically pivot data", there's a solution for that too, although it's another XY case - why return the data as *columns* instead of rows? Arbitrary columns can't be queried and *transposing* is a lot easier to do on the client. In fact, in HTML all you'd need is to add the proper CSS class to convert a vertical list into a horizontal list with no other code changes — Panagiotis Kanavos, May 15 '19 at 06:48
@PanagiotisKanavos since I think its not possible here in SQL, I am doing the task that I desire in the front end, then pass it as a parameter for my TSQL, then execute it using `sp_executesql` — Mr.J, May 15 '19 at 06:50
@Mr.J since *what* is not possible in SQL? What is the question and why do you thing you need multiple variables at all? If you want to pass a list of values to another stored procedure you need *rows*, not variables and a table valued parameter. You can call a stored procedure that accepts a TVP from the client in the first place, so there's no need to create comma-separated lists and split them at all — Panagiotis Kanavos, May 15 '19 at 06:52
@Mr.J it's impossible to answer this question, because there's no question yet. I started answering about splitting, then the comments made me start writing about pivoting. Right now it seems none of these are needed and the appropriate answer is an example of calling a stored procedure with a TVP from a client. [That's explained in the docs](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters) and there are numerous duplicate questions too, [like this one](https://stackoverflow.com/questions/10409576/pass-table-valued-parameter-using-ado-net) — Panagiotis Kanavos, May 15 '19 at 06:55
@PanagiotisKanavos then I will edit my question, thank you for the time, I will put all the details in my question, please wait for a while. — Mr.J, May 15 '19 at 07:11
@PanagiotisKanavos I have done the changes... may you have a look at it? Thank you so much for the time. — Mr.J, May 15 '19 at 08:09
@Mr.J don't use a CSV as a parameter. Use a table-valued parameter. The full query though shows some serious design problems, like separate tables per year. These are a *very* bad idea. For one, they don't improve performance at all. Second, SQL Server already supports [transparent partitioning](https://learn.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes?view=sql-server-2017) but you wouldn't need it unless you had multiple gigabytes of data per table. Partitioning is a data management feature, it doesn't help with performance — Panagiotis Kanavos, May 15 '19 at 08:36
@Mr.J finally, it's *very* common practice [to use a Calendar table](https://www.sqlshack.com/designing-a-calendar-table/) in reporting databases to make aggregations per year, quarter etc trivial. Instead of calculating years and quarters, you join with the calendar table and just group by its Year,Month or Quarter fields. — Panagiotis Kanavos, May 15 '19 at 08:40
@PanagiotisKanavos Thank you so much for the inputs specially the idea about a calendar table. I will apply these on the project, thank you! — Mr.J, May 15 '19 at 23:38

Bart Hofland · Answer 1 · 2019-05-15T06:34:45.320

As far as I know, it is not possible to dynamically generate variables this way, unless you create your SQL logic completely dynamically and execute it with EXECUTE or sp_executesql.

You might consider to use a temporary table or a table variable instead. That table will serve as a kind of an array then.

declare @string as nvarchar(max) = '2010,2011,2012,2013,2014,2015,2016';

declare @vars as table ([index] int identity, [value] nvarchar(max));
declare @charindex as int = CHARINDEX(',', @string);
declare @value as nvarchar(max);

while @charindex > 0
begin
    set @value = substring(@string, 0, @charindex);
    set @string = substring(@string, @charindex + 1, LEN(@string));

    insert into @vars ([value]) values (@value);

    set @charindex = CHARINDEX(',', @string);
end;

select [index], [value] from @vars order by [index];

Sorry about tweaking your code a little. I wanted to post a working example (giving your expected output).

Extra note:

If you want to include the last value as well, you might have to add more complex logic in your code... or you just might append an extra comma to the @string value...

Create variables based on condition in SQL?

1 Answers1