CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012dynamic-sql
amazen
amazen

Reputation: 11

Execute statement that is stored inside a SQL Server table

As example I will create a temp table:

CREATE TABLE #TembTable  
(
      Command VARCHAR(max)  
)

Insert the values toin the table

INSERT INTO #TembTable (Command)
VALUES ('SELECT @@VERSION AS Version_Name ;'),
       ('SELECT @@LANGUAGE AS Current_Language;'),
       ('SELECT @@LANGID AS Language_ID ;'),
       ('SELECT @@SERVERNAME AS Server_Name;'),
       ('SELECT @@SERVICENAME AS Service_Name;')

If I select all values

SELECT * FROM #TembTable

Result will show:

SELECT @@VERSION AS Version_Name ;
SELECT @@LANGUAGE AS Current_Language;
SELECT @@LANGID AS Language_ID ;
SELECT @@SERVERNAME AS Server_Name;
SELECT @@SERVICENAME AS Service_Name;

What I am trying to do is to execute each command automatically.

Thanks.

Upvotes: 1

Views: 515

Answers (2)

Ebis
Ebis

Reputation: 380

You must declare a variable

DECLARE @Statements NVARCHAR(MAX);

Fill this variable with the commands from #Temptable

SET @Statements = ( SELECT STRING_AGG (CONVERT(NVARCHAR(MAX),Command), NCHAR(13)) 
                    FROM   #TembTable)
;

and execute it:

EXECUTE (@Statements)
;

Upvotes: 2

DBSand
DBSand

Reputation: 750

A select query is a database object that shows information in Datasheet view. A query does not store data, it displays data that is stored in tables. A query can show data from one or more tables, from other queries, or from a combination of the two. (https://support.microsoft.com/en-us/office/create-a-simple-select-query-de8b1c8d-14e9-4b25-8e22-70888d54de59#:~:text=A%20select%20query%20is%20a,a%20combination%20of%20the%20two)

If your intention is to get the values for the statements then you can incorporate a stored procedure to do the above.

CREATE PROCEDURE Getdetails
AS
BEGIN
    SELECT @@VERSION AS Version_Name ;
    SELECT @@LANGUAGE AS Current_Language;
    SELECT @@LANGID AS Language_ID ;
    SELECT @@SERVERNAME AS Server_Name;
    SELECT @@SERVICENAME AS Service_Name;
END;

to get o/p: The below will retrieve all details that you needed

exec Getdetails

Upvotes: 0

Related Questions