Dynamic SQL
-
Has anyone ever seen a DB stored procedure where the queries weren't written using SQL, but rather using string concatenation to build the queries, and then submitting the queries with the EXEC command? I can't think of a more horrifically error-prone way of coding in T-SQL. But I see this from a prominent maker of Warehouse Management Systems. The first time I ever saw that, I was taken aback because I couldn't understand why anyone would do that. But then I saw that it was done so that the queries could be written to target different databases, tables, and columns depending upon the values of local variables. So even something as weird and wonderful as that has a use. I wonder if any other RDMS has a more elegant solution? EDIT: I realize that the queries could still be written using regular T-SQL, but in a more structured way. But I see that this strange practice waas done for the sake of simpler, straight-line code.
The difficult we do right away... ...the impossible takes slightly longer.
-
Has anyone ever seen a DB stored procedure where the queries weren't written using SQL, but rather using string concatenation to build the queries, and then submitting the queries with the EXEC command? I can't think of a more horrifically error-prone way of coding in T-SQL. But I see this from a prominent maker of Warehouse Management Systems. The first time I ever saw that, I was taken aback because I couldn't understand why anyone would do that. But then I saw that it was done so that the queries could be written to target different databases, tables, and columns depending upon the values of local variables. So even something as weird and wonderful as that has a use. I wonder if any other RDMS has a more elegant solution? EDIT: I realize that the queries could still be written using regular T-SQL, but in a more structured way. But I see that this strange practice waas done for the sake of simpler, straight-line code.
The difficult we do right away... ...the impossible takes slightly longer.
Richard Andrew x64 wrote:
So even something as weird and wonderful as that has a use.
i think you are being far too generous & kind. It's a terrible idea for numerous reasons. I know little about DBs & DBMS but there are a couple that seem glaringly wrong to me: 1) one of the big ideas of a SP is that it is precompiled. This build-string-query then exec would insure that wouldn't be true. Think about that. A SP is precompiled & knows the "execution path" but in this case that would never be true, so it makes entire no sense that this "dynamic" thing would be a SP. 2) built in query analyzer of SQL Server would not know what the execution would be & this probably causes performance issues & the inability to know if it is slow or not since the query is built on the fly I'm going to assume that some dev with little experience got this "genius" idea for how to create dynamic queries and no one ever looked at it because "it works". SQL Server is an amazing feat of true Engineering and will fix things for you so the dev is probably getting really lucky. Plus hardware is probably handling this. And probably if the thing really got serious traffic it would bog down to nothing. Just another Lucky Dev -- they're 92% of all Devs anyways. "That's not coding, that's typing." :rolleyes:
-
Has anyone ever seen a DB stored procedure where the queries weren't written using SQL, but rather using string concatenation to build the queries, and then submitting the queries with the EXEC command? I can't think of a more horrifically error-prone way of coding in T-SQL. But I see this from a prominent maker of Warehouse Management Systems. The first time I ever saw that, I was taken aback because I couldn't understand why anyone would do that. But then I saw that it was done so that the queries could be written to target different databases, tables, and columns depending upon the values of local variables. So even something as weird and wonderful as that has a use. I wonder if any other RDMS has a more elegant solution? EDIT: I realize that the queries could still be written using regular T-SQL, but in a more structured way. But I see that this strange practice waas done for the sake of simpler, straight-line code.
The difficult we do right away... ...the impossible takes slightly longer.
I have done that very rarely, such as when the name of a table isn't known. For instance, writing a procedure which will di a TRUNCATE TABLE but fallback to DELETE if that fails. Definitely not as a normal course of action. Further, I have not trusted the input, but rather checked it against the
sys.objectsor similar table to be sure it is a reasonable value before proceeding. -
Has anyone ever seen a DB stored procedure where the queries weren't written using SQL, but rather using string concatenation to build the queries, and then submitting the queries with the EXEC command? I can't think of a more horrifically error-prone way of coding in T-SQL. But I see this from a prominent maker of Warehouse Management Systems. The first time I ever saw that, I was taken aback because I couldn't understand why anyone would do that. But then I saw that it was done so that the queries could be written to target different databases, tables, and columns depending upon the values of local variables. So even something as weird and wonderful as that has a use. I wonder if any other RDMS has a more elegant solution? EDIT: I realize that the queries could still be written using regular T-SQL, but in a more structured way. But I see that this strange practice waas done for the sake of simpler, straight-line code.
The difficult we do right away... ...the impossible takes slightly longer.
-
Has anyone ever seen a DB stored procedure where the queries weren't written using SQL, but rather using string concatenation to build the queries, and then submitting the queries with the EXEC command? I can't think of a more horrifically error-prone way of coding in T-SQL. But I see this from a prominent maker of Warehouse Management Systems. The first time I ever saw that, I was taken aback because I couldn't understand why anyone would do that. But then I saw that it was done so that the queries could be written to target different databases, tables, and columns depending upon the values of local variables. So even something as weird and wonderful as that has a use. I wonder if any other RDMS has a more elegant solution? EDIT: I realize that the queries could still be written using regular T-SQL, but in a more structured way. But I see that this strange practice waas done for the sake of simpler, straight-line code.
The difficult we do right away... ...the impossible takes slightly longer.
Leaving aside the performance issues noted by @raddevus, this is a terrible security risk. While the legitimate code uses this e.g. to query the client table, what is stopping malicious code from querying the credit card table? (Yes, I know that credit card Nos. should not be stored like that, but many databases do in order to provide a rolling subscription to their site.)
Freedom is the freedom to say that two plus two make four. If that is granted, all else follows. -- 6079 Smith W.
-
Has anyone ever seen a DB stored procedure where the queries weren't written using SQL, but rather using string concatenation to build the queries, and then submitting the queries with the EXEC command? I can't think of a more horrifically error-prone way of coding in T-SQL. But I see this from a prominent maker of Warehouse Management Systems. The first time I ever saw that, I was taken aback because I couldn't understand why anyone would do that. But then I saw that it was done so that the queries could be written to target different databases, tables, and columns depending upon the values of local variables. So even something as weird and wonderful as that has a use. I wonder if any other RDMS has a more elegant solution? EDIT: I realize that the queries could still be written using regular T-SQL, but in a more structured way. But I see that this strange practice waas done for the sake of simpler, straight-line code.
The difficult we do right away... ...the impossible takes slightly longer.
Richard Andrew x64 wrote:
submitting the queries with the EXEC command?
X| For SQL Server, they should at least be using sp_executesql[^], and passing the parameters as parameters rather than concatenating them into the string. In some rare situations, it may be worth doing this - for example, if your procedure has a lot of optional filters, building a query that only specifies the ones being used will allow the DBMS to select the most appropriate execution plan for the query. If you put them all in the same query - eg:
(@x Is Null Or T.X = @x)- then the execution plan will be selected based on the first set of filters provided, which may be sub-optimal for a different set of filters. But passing the string toEXECrather thansp_executesqlmeans they're introducing a SQL Injection[^] vulnerability into the code, which far outweighs any performance benefits.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Richard Andrew x64 wrote:
So even something as weird and wonderful as that has a use.
i think you are being far too generous & kind. It's a terrible idea for numerous reasons. I know little about DBs & DBMS but there are a couple that seem glaringly wrong to me: 1) one of the big ideas of a SP is that it is precompiled. This build-string-query then exec would insure that wouldn't be true. Think about that. A SP is precompiled & knows the "execution path" but in this case that would never be true, so it makes entire no sense that this "dynamic" thing would be a SP. 2) built in query analyzer of SQL Server would not know what the execution would be & this probably causes performance issues & the inability to know if it is slow or not since the query is built on the fly I'm going to assume that some dev with little experience got this "genius" idea for how to create dynamic queries and no one ever looked at it because "it works". SQL Server is an amazing feat of true Engineering and will fix things for you so the dev is probably getting really lucky. Plus hardware is probably handling this. And probably if the thing really got serious traffic it would bog down to nothing. Just another Lucky Dev -- they're 92% of all Devs anyways. "That's not coding, that's typing." :rolleyes:
raddevus wrote:
built in query analyzer of SQL Server would not know what the execution would be & this probably causes performance issues
Actually, the opposite is more likely to be true. If you have a single query with lots of conditional filtering based on the parameters - eg:
(@x Is Null Or T.X = @x)- you'll get one execution plan based on the first set of parameters used, which can be sub-optimal for a different set of parameters. Having a different query for each set of applied filters can allow the query optimiser to select the "best" execution plan to satisfy that set of filters. You may end up with some query execution plan cache bloat, and very complicated queries might take slightly longer for the first compilation. But you may still end up with better performance. :)
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Has anyone ever seen a DB stored procedure where the queries weren't written using SQL, but rather using string concatenation to build the queries, and then submitting the queries with the EXEC command? I can't think of a more horrifically error-prone way of coding in T-SQL. But I see this from a prominent maker of Warehouse Management Systems. The first time I ever saw that, I was taken aback because I couldn't understand why anyone would do that. But then I saw that it was done so that the queries could be written to target different databases, tables, and columns depending upon the values of local variables. So even something as weird and wonderful as that has a use. I wonder if any other RDMS has a more elegant solution? EDIT: I realize that the queries could still be written using regular T-SQL, but in a more structured way. But I see that this strange practice waas done for the sake of simpler, straight-line code.
The difficult we do right away... ...the impossible takes slightly longer.
This type of crap used to be all the rage in the late 90s/early 00s. I'm pleased to say I haven't seen abominations like this in the last 20 years or so.
-
They are used where I work. They are usually performing a reporting task. Building it dynamically allows changes to the actual select clause, as well as sorts, groups etc. They are a nightmare to debug...
-
Has anyone ever seen a DB stored procedure where the queries weren't written using SQL, but rather using string concatenation to build the queries, and then submitting the queries with the EXEC command? I can't think of a more horrifically error-prone way of coding in T-SQL. But I see this from a prominent maker of Warehouse Management Systems. The first time I ever saw that, I was taken aback because I couldn't understand why anyone would do that. But then I saw that it was done so that the queries could be written to target different databases, tables, and columns depending upon the values of local variables. So even something as weird and wonderful as that has a use. I wonder if any other RDMS has a more elegant solution? EDIT: I realize that the queries could still be written using regular T-SQL, but in a more structured way. But I see that this strange practice waas done for the sake of simpler, straight-line code.
The difficult we do right away... ...the impossible takes slightly longer.
-
Has anyone ever seen a DB stored procedure where the queries weren't written using SQL, but rather using string concatenation to build the queries, and then submitting the queries with the EXEC command? I can't think of a more horrifically error-prone way of coding in T-SQL. But I see this from a prominent maker of Warehouse Management Systems. The first time I ever saw that, I was taken aback because I couldn't understand why anyone would do that. But then I saw that it was done so that the queries could be written to target different databases, tables, and columns depending upon the values of local variables. So even something as weird and wonderful as that has a use. I wonder if any other RDMS has a more elegant solution? EDIT: I realize that the queries could still be written using regular T-SQL, but in a more structured way. But I see that this strange practice waas done for the sake of simpler, straight-line code.
The difficult we do right away... ...the impossible takes slightly longer.
Yeah, seen it too. In fact, it was the default for a project I worked on. The idea was that you could filter on something like ten to twenty fields and depending on which fields were set, the string concatenation added fields to the WHERE-clause. The alternative was something like
WHERE (X = @X OR @X IS NULL) AND (Y = @Y OR @Y IS NULL) AND (Z = @Z OR @Z IS NULL) -- Etc.Nowadays I'd use LINQ to build such a query.Best, Sander Azure DevOps Succinctly (free eBook) Azure Serverless Succinctly (free eBook) Migrating Apps to the Cloud with Azure arrgh.js - Bringing LINQ to JavaScript
