If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Using single talking marks (') with Dynamic SQL?

Hi Everyone,

I am new to Dynamic SQL and have become quite stuck regarding how to included aliases which have spaces in my query. A related issue, which I believe also revolves around the use of single talking marks (') within Dynamic SQL is how to include argument values and variables in my queries.

From my limited research it 'feels like' what I need to do is the opposite of ESCAPE sequencing...

My issue is better explained through some code samples.

Each of the samples below assume the use of a variable within the overall query, e.g.: DECLARE @SQL nvarchar(max)

Here is a sample from a working query (excuse the brevity)

Now if I put a space into the Alias name as shown below the query will not run...

Similarly I am wondering how to go about assigning variables, within for example a WHERE clause?

Only uncomment --EXEC(@SQL)
and comment out PRINT @SQL
once you are 100% sure the query works (like you have copy and pasted it into SSMS and it gives you the results you expect). This way you will notice all errors in the generated SQL script.
In the example I gave, there is a space missing between "FROM table1"' and "WHERE col1". Only with a PRINT it becomes obvious.

And avoid dynamic SQL whenever possible.

Last edited by Wim; 10-30-14 at 04:04.

With kind regards . . . . . SQL Server 2000/2005/2012
Wim
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Finally whilst I can already see that Dynamic SQL is adding a whole new level of complexity to my queries, ultimately I am working in SAP Business One, and don't have any real alternatives for very select tasks, such as the one that I have just completed.

There are several reasons.
Performance-wise, the code is compiled on each run, as opposed to a stored procedure that saves an execution plan.
Support wise, it is difficult to read, understand, and troubleshoot.
Karma-wise, you probably don't want all of the developers who inherit this code to keep cursing you because of reason 2.

First and foremost, avoid dynamic SQL to prevent SQL Injection. This is the #1 method for hacking web sites and has been the cause or a contributing factor to most of the data breaches in the last few years (see Home Depot for several great examples). If you build dynmic SQL in your own code, it isn't nearly as dangerous as building the dynamic SQL from user input but it is still very dangerous!

Once you get past the regulatory obligations, then consider query plan problems... Dynamic SQL work well (sometimes fabulously) in small doses (up to a few thousand queries), but once you consider the overhead cost of using Dynamic SQL on a large scale it can be frightening!

There are ways to make judicious use of Dynamic SQL safe and reasonably cost effective, but it can also be an Achilles heel in your code base that will cause havoc when you least expect it!

-PatP

In theory, theory and practice are identical. In practice, theory and practice are unrelated.