How to put an SQL command in a variable

Question

How to put an SQL command in a variable

Asked 9 years, 10 months ago

Viewed 1,278 times

0

I have a following SQL command and I need to put it inside a variable in SQL. The code is this.

 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 several ways but I could not do the same.

This script is for an application that I am developing that at the moment I need to get the percentage of CPU processing through this script in SQL.

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

2

You want to create a variable in SQL or a variable in your script? In what language is the script? And which database (to know which SQL variation is using)?

– Maniero

2014/12/17 at 16:52
Which database, friend? Click [Edit] and enter the tags.

– gmsantos

2014/12/17 at 16:52
I want to create a variable in the script. Transact-SQL. SQL Server 2012.

– Thiago Beltrame

2014/12/17 at 16:57
Variable in sql ? c is doing a stored Procedure or Trigger ? if it is not making sense variables in sql. sql is a language of queries, not a programming language. It is intended to extract data from the database. You elaborate the query, then in your program (php, c++, java, etc) you put it in a string variable and send it to the database and then the database returns the results and you capture the results in some variable. It makes no sense therefore to exist variable in the query. Unless you are referring to parameters like "and data = :myData" so you can use the same query for several dates.

– Nelson Teixeira

2014/12/17 at 17:47
I’m doing a stored trial

– Thiago Beltrame

2014/12/17 at 18:15
But it wasn’t a script? Now it’s become Sproc?

– Maniero

2014/12/17 at 19:03

Show 1 more comment

1 answer

Browser other questions tagged sql sql-server variables sql-server-2012

You are not signed in. Login or sign up in order to post.

by Levi Macedo • 57 points · Answer 1 · 2015-01-08T17:09:44+00:00

Thus:

    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