October 26, 2021 at 2:32 pm
I have a table that contains many rows, but only one column, called "Script". It is an NVarchar data type. In fact, I have multiple tables set up exactly the same, except it varies a bit in NVarchar(50-Max) from one table to the next. In the individual rows of the table, are SQL statements. An example below:
Script
Row 1: Select * From SomeTable Where SomeCriteria = 1
Row 2: UNION ALL Select * From SomeSimilarTable Where SomeCriteria = 1
Row 3: UNION ALL Select * From SomeSimilarTable2 Where SomeCriteria = 1
What I want to do, is have a SQL query that takes the "strings" in the table above, and run them as a SQL statement...
Like this...
Select * From SomeTable Where SomeCriteria = 1 UNION ALL Select * From SomeSimilarTable Where SomeCriteria = 1 UNION ALL Select * From SomeSimilarTable2 Where SomeCriteria = 1
And then, I want to see those results...
Column1, Column2, Column3
DataPoint1, DataPoint12, DataPoint13
DataPoint21, DataPoint22, DataPoint23
(Not sure how to "format as a table" in here, so sorry if the above "markdown" block is confusing. It's a result set.)
Please note that the number of columns (and rows) in the "final" dataset can vary, as well as the datatype(s) of the data that gets "called" from these "scripts".
October 26, 2021 at 2:38 pm
Man, that's asking for trouble, but...
You just need to use sp_executesql. Query the table to get the string. Load that into a variable. Follow the link to see how to use it to execute a query. As to formatting the output, you can only really do what the query says. Let the formatting get taken care of on the client side of things.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 26, 2021 at 3:14 pm
I agree with Grant's first statement - that is asking for trouble and can be a scary practice... and if you read his signature, he is The Scary DBA!
Why it is asking for trouble - lets say that someone inserts into the table "DROP DATABASE" or "SHUTDOWN WITH NOWAIT" or some other dangerous stuff... could do inserts, updates, deletes, disable backups, create new logins, etc... sp_executesql is a dangerous tool but can be helpful when used properly.
I would advise against the approach you are going for and instead make it a 2 step process and instead of using sp_executesql, use PRINT and have someone run that output manually after verifying the input. Or, alternately, if that is being fed back to a custom coded application, have the application take the script, present it to the end user to verify, and then execute only after they click "run" or similar.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 26, 2021 at 4:26 pm
Thank you both for your feedback. I totally understand, accept and am prepared for the consequences of such a practice. That being said, I am using a number of other scripts in sequence to generate these tables in a dynamic way to include the same targeted information from the same table, but from a plethora of neigh-identical (customer-specific) DBs. Currently, I just "Select * From ScriptTable" and copy/paste the results into another query window, which I then execute that... about a dozen times.
To be clear. The only statements in these tables are "Select * [...]" and "Union All Select * [...]", no "insert into" "update" or anything like that.
Again, I know that this is NOT a best practice by any means. It's just a stop-gap solution for now. It'll get better, once the criteria for what the "uppers" are wanting is more succinctly defined, etc.
Thanks again. I'll take a look at "sp_execsql" and see if I can wrap my head around getting that function to work.
October 26, 2021 at 5:37 pm
Getting sp_executesql to work is pretty easy.
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = '<insert dynamic SQL here>'
EXEC sp_executesql @sql
The only thing that makes your query a bit more tricky is that your dynamic SQL comes from a table. Do you have some ordering column in the table such as an IDENTITY column or a datetime? If not, then this may not go the way you expect either. Without a column to order by, you may end up with data that is not ordered how you expect and you may get syntax errors if the first row returned is a UNION and not a SELECT. But for that, you would just need to change the query to something like:
DECLARE @sql NVARCHAR(MAX) = ''
SELECT @sql = @sql + ' ' + Script
FROM TableA
ORDER BY ID ASC
PRINT @sql
--EXEC sp_executesql @sql
Here I put the PRINT @sql before the commented out execute as I like to verify that the @sql value is what I expect before I actually run it. To run it, uncomment the last line.
I would make sure to do a dry run on a test system first just to make sure it works, but the above should work if you change the table name from TableA to whatever you need. Possibly change the column name Script to something else too.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 26, 2021 at 5:54 pm
Thanks for the additional explanation on sp_executesql. That's essentially the same info that I found. I ultimately went with a variation that also includes "STRING_AGG" and "CAST". So far, this seems to be working well:
DECLARE @Test NVARCHAR(MAX)
SET @Test = (Select STRING_AGG(cast(Script AS NVarchar(Max)), ' ') AS Test From DatabaseName.dbo.SomeTable)
EXECUTE sp_executesql @Test
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply