GEt Individual Columns From System Stored Procedure

Question

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?

[Insert results of a Stored Procedure into a Temporary Table](http://stackoverflow.com/q/653714/772086) — Mike, Nov 05 '15 at 15:22

score 1 · Accepted Answer · answered Nov 05 '15 at 15:19

1

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';

answered Nov 05 '15 at 15:19

Julien Vavasseur

3,854
1
20
29

Do you need to drop the variable table after you are done with it. I am wanting to keep this as clean as possible. – Wes Palmer Nov 05 '15 at 16:04
No. It is a variable. It will be removed with other variables. Don't worry :) You only have to worry if it gets too big because it is created in tempdb and drop when the variable is removed. – Julien Vavasseur Nov 05 '15 at 16:05

GEt Individual Columns From System Stored Procedure

1 Answers1