CODEWITHSUNDEEP
sql-serverfor-loop
JohnN
JohnN

Reputation: 1010

If else statement inside a for loop for a batch file

This for loop pulls a max int value from a SQL table, adds 1, then sets it equal to a variable called "Num".

How can I modify this for loop so that if there is no value found (or null entry) in the SQL column called "Number", it will set the variable Num == 1?

for /f "delims= " %%a in 
    ('sqlcmd -S SERVER -d DATABASE-Q "SET NOCOUNT ON; 
    select max(Number)+1 from TABLE"') do set Num=%%a

Upvotes: 0

Views: 45

Answers (2)

benjamin moskovits
benjamin moskovits

Reputation: 5468

Select max(isnull (number,0))+1 from table

Edited

Upvotes: 0

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

You can do this from SQL statement. What you're looking for is COALESCE.

From manual

Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

Syntax for your case would be

select coalesce(max(Number)+1, 1) FROM TABLE

Upvotes: 2

Related Questions