0

In my sql query I want to use sequence inside CASE statement. The problem is, that I can't use NEXT VALUE FOR inside CASE. I was trying to define user function, but restrictions about using exec and modifying data in UDF made my solution useless...

Here is my test code:

    create sequence temp_test start with 1
select 
'value1',
'value2',
case when(1=1)
then next value for temp_test
else (select current_value from sys.sequences where name='temp_test')
end
drop sequence temp_test

All I need is increasing variable if condition is true and return it. Any idea?

The exact error is

NEXT VALUE FOR function cannot be used within CASE, CHOOSE, COALESCE, IIF, ISNULL and NULLIF.

Julian Kowalczuk
  • 145
  • 1
  • 2
  • 10
  • please add some sample table data and the expected result - as well in formatted text. Also show us your current query attempt – TheGameiswar Mar 22 '17 at 09:23
  • that's not important while resolving this issue, so I will modify code to show clearly why not – Julian Kowalczuk Mar 22 '17 at 12:11
  • When I run `create sequence temp_test starts with 1;` I get `Incorrect syntax near 'starts'.`. Please fix that bit of your code. I have also added the error message (please do that in future) What are you trying to do? – Nick.Mc Mar 22 '17 at 12:21
  • Besides that, `else temp_test` is invalid. Are you trying to get the current value? – Nick.Mc Mar 22 '17 at 12:24
  • fixed. anyway please focus on issue aim (how to get next value inside case statement inside select statement) – Julian Kowalczuk Mar 22 '17 at 13:21
  • Clearly it's not possible. If you explain what you're trying to do we may find a workaround. – Nick.Mc Mar 22 '17 at 13:23

2 Answers2

0

Instead of directly inserting to target table, create a temporary table first and set id as null initially where sequence needs to be set. After that update the temp table whose id rows are null. After that insert to the target table from temp table.

Select case when @something = 1 then cast(null as int)
       else ID ends id,
       value
into #temp
from temp_test;

update #temp
set id = next value for [your_seq]
where id is null;
Jose Tuttu
  • 418
  • 5
  • 15
-1

Maybe you mean something like this:

declare @value INT

SELECT @value = CAST(current_value AS INT) 
FROM sys.sequences WHERE name = 'temp_test';

IF (1=1)
    select  @value = next value for temp_test;


select 
'value1',
'value2',
@value

It's not clear why you need this to be all in a single select but it seems to me that violates some rules where your select may or may not make a difference to the database at run time.

You might be able to use an identity to do what you want or you might be able to write this a different way but you won't explain what you're really trying to do (i.e. load a new key into a table?) so I can't help further

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
  • nope, here you set value out of select and case statement – Julian Kowalczuk Mar 22 '17 at 13:19
  • Please explain what you're actually trying to do. – Nick.Mc Mar 22 '17 at 13:21
  • Variable starting with 1. I read data from db by select statement and inside this if condition is true, increase variable and return it with another fields – Julian Kowalczuk Mar 22 '17 at 13:24
  • Why can't it be done in two statements like above? Are you allowed to have gaps (I.e. Always increment sequence but only return if needed) – Nick.Mc Mar 22 '17 at 13:29
  • There is a bigger picture here that isn't being explained. Will 'identity' help? – Nick.Mc Mar 22 '17 at 13:29
  • as you see in example, increase only if condition is true, return value ALWAYES for select statement. The select in original version get data from table and condition depens on fields values. – Julian Kowalczuk Mar 22 '17 at 15:39
  • I'm 99% sure you can't do that in an inline SELECT, you have to do it procedurally. I'll make an edit to my answer. You still haven't explained why it needs to be an inline select. Where is the data going? into another table? into a report? – Nick.Mc Mar 22 '17 at 22:38
  • The problem is you're using the wrong design pattern. If I have a problem in C# I know it's usually because i'm doing it the wrong way not because the language is bad. I'm sure what you are trying to do is easily achievable if you could explain why you need to conditionally get a sequence. I'm sure what you are trying to do is not that out of the ordinary. Are you trying to generate new id's on the fly? just in case those records are inserted afterwards? What is the _real_ logic to decide whether you want a new sequence or not? – Nick.Mc Mar 23 '17 at 09:38
  • I'm converting queries from postresql to mssql. I know code written by authors of program which we refactor is poor quality and they shouldn't use sequence here, because it's not neccessary here. But they use and all I have to do it implement it in mssql. On the fly I want to increase variable (it doesn't have to be sequence) if condition is true and alwayes include it inside select statement to return it with other fields. That's it. – Julian Kowalczuk Mar 23 '17 at 12:06
  • The answer I posted is in a select statement. Do you get an error when you try to implement it? – Nick.Mc Mar 23 '17 at 12:08
  • Does the number have to be globally unique? (i.e. no collisions). Does it need increment by 1? Does it need to be a number? If it needs to have no collisions, (multiple parallel calls all get their unique id), then your options are `sequence`, `identity` or `GUID`. – Nick.Mc Mar 23 '17 at 12:55
  • yeah, that has to be unique. increment just by 1. i know that sequence is option, identity and guid for sure not. I'm looking for any hack how to do it, because restrictions of mssql do not allow to do it clearly... You ask so many questions, but we do not focus on the aim. I want to get data from table with additional field dependent on condition in brackets returning the same variable, but increase that before return if condition is true. for me as programmer that's all I should know about issue. I'm going to find help here among specs and I hope I will – Julian Kowalczuk Mar 24 '17 at 07:54
  • It can't be done the way you want so I am trying to understand what you really want to do so I can propose a workaround. That's why I'm asking so many questions. I already proposed one solution early on but you haven't explained why it is not satisfactory. Do you get an error? – Nick.Mc Mar 24 '17 at 12:23
  • I have already answered. You do it in if statement, which cannot be used inside select. So not. – Julian Kowalczuk Mar 27 '17 at 07:09
  • Is it not an option because you have loads of complex code to migrate and you need to have a simple template solution? Because the _output_ of the solution I posed is _indistinguishable_ to any other standalone select that does it all inline. There must be some kind of syntax reason that you don't want to do it this way. So I'll say again: you need to describe your constraints in more detail so that we can come up with a workaround. The main problem is that a _select_ on its own, _by design_ does not have all the locking necessary to ensure a unique number. – Nick.Mc Mar 27 '17 at 07:17
  • I'm not saying my answer is right or suitable for you. It's just a starting point for us to understand each other. If my answer is unsuitable because 'it uses if', then I can't suggest anything else. If you say my answer is unsuitable because your existing code is based on views, that gives me a clue to providing a suitable solution. If you implement my code and get an error, that also gives me a clue. – Nick.Mc Mar 27 '17 at 07:18