Geek Answers Handbook

If and else statements that execute

I have this query in my sql

if (select count(*) from sys.columns where object_id = (select object_id from sys.tables where name = 'CLIENT_STATUS')) = 4 insert into CLIENT_STATUS select 'NA', 'Inactive', 0, 0 --old version else insert into CLIENT_STATUS select 'NA', 'Inactive', 0, 0, 1 --new version

The result of select count(*) from sys.columns where object_id = (select object_id from sys.tables where name = 'CLIENT_STATUS') is 4, but it always seems like it is executing the 5th variant of the else version of the query.

What am I doing wrong with the if statement?

UPDATE:

It seems like both recordings are done, because if I do

 if (select count(*) from sys.columns where object_id = (select object_id from sys.tables where name = 'CLIENT_STATUS')) = 5 insert into CLIENT_STATUS select 'NA', 'Inactive', 0, 0, 1 --new version else insert into CLIENT_STATUS select 'NA', 'Inactive', 0, 0 --old version

I get the same error, but now she says what the first statement does.

UPDATE2: Mikael Eriksson had the correct answer, I changed my code to this to fix it.

 if ((select count(*) from sys.columns where object_id = (select object_id from sys.tables where name = 'CLIENT_STATUS')) = 5) execute ('insert into CLIENT_STATUS select ''NA'', ''Inactive'', 0, 0, 1') --new version else execute ('insert into CLIENT_STATUS select ''NA'', ''Inactive'', 0, 0') --old version
+4
source share
2 answers

You get an error when SQL Server compiles your statements.

In this table

 create table TestTable(ID int)

Try running this statement

 if 1 = 1 insert into TestTable values (1) else insert into TestTable values(1, 2)

Result:

 Msg 213, Level 16, State 1, Line 4 Column name or number of supplied values does not match table definition.

Obviously, the second statement will never be executed, but it will be compiled.

+6
source

I suppose this is related to your bracket, you close the if statement, and then compare this to 5.

You can try this for debugging:

 declare @myCount as int select @myCount = select count(*) from sys.columns where object_id = (select object_id from sys.tables where name = 'CLIENT_STATUS'; print @myCount if (@myCount = 5) insert into CLIENT_STATUS select 'NA', 'Inactive', 0, 0, 1 --new version else insert into CLIENT_STATUS select 'NA', 'Inactive', 0, 0 --old version

This will help you ensure the value you get from the choice.

0
source

More articles:


All Articles