Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2014
    Posts
    35

    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)

    Click image for larger version. 

Name:	2em08ep.jpg 
Views:	7 
Size:	30.8 KB 
ID:	15944

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

    Click image for larger version. 

Name:	x0wkti.jpg 
Views:	7 
Size:	31.9 KB 
ID:	15945

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

    Click image for larger version. 

Name:	jax2jn.jpg 
Views:	7 
Size:	24.4 KB 
ID:	15946

    Any help with the above broad questions will be greatly appreciated.

    Kind Regards,

    Davo

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    For the second SQL, replace
    as 'bin col'
    by
    as [bin col].

    For the third SQL, PRINT your statement instead of executing it.
    I guess you use something like:

    Code:
    DECLARE @SQL VARCHAR(500) 
    DECLARE @date	DATE
    
    SET @SQL = 'SELECT col1, COL2 
    			FROM table1'
    
    SELECT @date = max(date1) from table2
    
    SET @SQL = @SQL +
    			'WHERE col1 <=' + @date
    
    --EXEC(@SQL)
    PRINT @SQL
    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 05: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

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    To emphasize what Wim said: avoid dynamic SQL if possible!

    Pay attention to the rest of what Wim said, but he didn't emphasize that point nearly enough to suit me!

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

  4. #4
    Join Date
    Mar 2014
    Posts
    35
    Hi Wim,

    Great suggestions, thank you.

    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.



    Kind Regards,

    David

  5. #5
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Why avoid dynamic SQL?

    I am trying to learn more about it.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •