Execute statement that is stored inside a SQL Server table

Question

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.

To run SQL stored in a table, you need a loop to process multiple rows one at a time, and then sp_executesql to execute the dynamic SQL. — Dale K
– Dale K, Commented Aug 23, 2020 at 20:12
You may also want to have a temp table that has an ORDER - not a random one as you ahve. i.e. add an ID field so you can execute them ordered by ID. Otherwise you may or may not get them in the same order when retrieving. — TomTom
– TomTom, Commented Aug 24, 2020 at 10:33
Why not simply create a view? create view ... as select @@version as version_name, @@language as current_language, ... — user330315
– user330315, Commented Aug 24, 2020 at 10:45

Ebis · Accepted Answer · 2020-08-24 10:30:41Z

2

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

answered Aug 24, 2020 at 10:30

Ebis

3901 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DBSand · Accepted Answer · 2020-08-23 18:00:43Z

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

Collectives™ on Stack Overflow

Execute statement that is stored inside a SQL Server table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related