CODEWITHSUNDEEP
sql-serverstored-procedures
Wes Palmer
Wes Palmer

Reputation: 880

GEt Individual Columns From System Stored Procedure

I am trying to run a totally dynamic update statement to get Number of Cpu's, Number of cores, Memory and version of a the server it is ran on. The portion I have for the version number runs a stored procedure in the master DB and I need to pull out just the Character_Value column do I can get version and build number to push into the table.

UPDATE DBA_STATS.dbo.HOSTS

SET CPUNUM = (SELECT (cpu_count / hyperthread_ratio) FROM master.sys.dm_os_sys_info),
CORENUM = (SELECT CASE            
        WHEN hyperthread_ratio = cpu_count                
            THEN cpu_count            
        WHEN (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio])*cpu_count) = 0
            THEN cpu_count
        ELSE (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio])*cpu_count)            
    END FROM sys.dm_os_sys_info),
MEMORY = (SELECT physical_memory_in_bytes/1073741824 FROM sys.dm_os_sys_info),
DOMAIN = (SELECT DEFAULT_DOMAIN()),
VERSION = (exec master.dbo.xp_msver 'WindowsVersion')

WHERE NAME = @@ServerName

I have an error obviously because the stored procedure xp_msver is giving me 4 columns. All I want to pull out is Character_Value column. Does anyone have an idea of how to accomlish this?

Upvotes: 0

Views: 108

Answers (1)

Julien Vavasseur
Julien Vavasseur

Reputation: 3952

You can do it this way:

declare @xp_msver table ([Index] int, name sysname, internal_value int, character_value nvarchar(50));
declare @version nvarchar(50);

Insert Into @xp_msver Exec  master.dbo.xp_msver 'WindowsVersion';

Select top(1) @version = character_value from @xp_msver Where name = 'WindowsVersion';

Upvotes: 1

Related Questions