Why the code moving to Else block?

Question

Post reply

Why the code moving to Else block?

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
February 29, 2016 at 4:44 pm

#305213

Hello
Trying to run the specific code in each SQL Version. But not sure why it is being run in specific SQL Version?
DECLARE @Val nVARCHAR(100)
SET @Val = CONVERT(varchar(50),(select SERVERPROPERTY('productversion')))
select @val
IF (@val like '10.%')
BEGIN
--SELECT @@servername 'ServerName', SERVERPROPERTY('productversion') productversion, SERVERPROPERTY ('productlevel') productlevel,
--SERVERPROPERTY ('edition') edition
SELECT cpu_count AS [Logical CPU Count],
hyperthread_ratio AS Hyperthread_Ratio,
cpu_count/hyperthread_ratio AS Physical_CPU_Count,
--physical_memory_kb/1000/1000 physical_memory_GB,
physical_memory_in_bytes/1000/1000/1000 AS Physical_Memory_in_GB,
sqlserver_start_time,
affinity_type_desc -- (affinity_type_desc is only in 2008 R2)
FROM sys.dm_os_sys_info
END
ELSE IF (@val like '11.%')
BEGIN
SELECT cpu_count AS [Logical CPU Count],
hyperthread_ratio AS Hyperthread_Ratio,
cpu_count/hyperthread_ratio AS Physical_CPU_Count,
physical_memory_kb/1000/1000 physical_memory_GB,
--physical_memory_in_bytes/1000/1000/1000 AS Physical_Memory_in_GB,
sqlserver_start_time,
affinity_type_desc -- (affinity_type_desc is only in 2008 R2)
FROM sys.dm_os_sys_info
Thanks.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

i believe you'll get an error if the column doesn't' exist, right? physical_memory_in_bytes vs physical_memory_kb

i've done it with an exec command instead;

IF (SELECT CONVERT(MONEY,LEFT(CONVERT(VARCHAR,SERVERPROPERTY('productversion')),CHARINDEX('.',CONVERT(VARCHAR,SERVERPROPERTY('productversion')))))) >=11

BEGIN

SET @cmd = 'UPDATE MyTarget

SET actual_MemoryMB = physical_memory_kb

FROM #MaxServerMemory MyTarget

CROSS JOIN sys.dm_os_sys_info';

EXEC(@cmd)

END

ELSE

--2005 thru 2008R2 has column physical_memory_in_bytes

BEGIN

SET @cmd = 'UPDATE MyTarget

SET actual_MemoryMB = physical_memory_in_bytes /1024

FROM #MaxServerMemory MyTarget

CROSS JOIN sys.dm_os_sys_info

';

EXEC(@cmd)

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

It's not moving to the ELSE block. The error is a parse-time error, happens during parsing before any part of the code, including the IF has executed.

You can see that behaviour easily with

IF (1=0)

SELECT ColumnThatDoesNotExist from sys.objects

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass