How to put a SQL command in a variable

0

I have a following command in SQL and I need to put it inside a variable in SQL. That's the code.

 WITH DB_CPU_Stats
        AS
        (SELECT SUM(total_worker_time) AS [CPU_Time_Ms]
        FROM sys.dm_exec_query_stats 
        CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] 
                  FROM sys.dm_exec_plan_attributes(plan_handle)
                  WHERE attribute = N'dbid') AS F_DB
        GROUP BY DatabaseID)
        SELECT CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]
        FROM DB_CPU_Stats
        ORDER BY [CPU Percent] DESC;

What would be an easy and practical way to do this. I have tried in many ways but I have not been able to do the same.

This script is for an application that I'm developing that currently I need to get the percentage of CPU processing through this script in SQL.

EDIT: Language: Transact-SQL, Database: SQL Server 2012

    
asked by anonymous 17.12.2014 / 17:49

1 answer

1

So:

    DECLARE @COMANDOSQL VARCHAR(MAX)

    SET @COMANDOSQL = 'WITH DB_CPU_Stats
            AS
            (SELECT SUM(total_worker_time) AS [CPU_Time_Ms]
            FROM sys.dm_exec_query_stats 
            CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] 
                      FROM sys.dm_exec_plan_attributes(plan_handle)
                      WHERE attribute = N''dbid'') AS F_DB
            GROUP BY DatabaseID)
            SELECT CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]
            FROM DB_CPU_Stats
            ORDER BY [CPU Percent] DESC;'

--Imprime conteúdo do comando
PRINT @COMANDOSQL
    
08.01.2015 / 18:09