4

For sake of backwards compatibility I need to have a stored proc run on both sql server 2005 AND sql server 2008. However due to some commands being deprecated in sql server 2008 my script compiles on 2005 but fails on 2008. I am looking for the c++, c# equivalent of #define, #ifdef so I can compile the same script on 2005 & 2008. What is the standard practice on this ? Attached screenshot explains in detail. thank you

enter image description here

Gullu
  • 3,477
  • 7
  • 43
  • 70

2 Answers2

5

Use dynamic SQL:

if (@returnVal = 1)
    exec ('backup log myDB with truncate_only');
Gullu
  • 3,477
  • 7
  • 43
  • 70
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • vow. Didn't know it was that simple. Is this the standard practice in the t-sql world for conditional compilation. thanks – Gullu Jul 07 '11 at 14:32
  • @Gullu: It's rather unusual for a single SP to support two versions of SQL Server. The standard practice would be to deploy a separate SP for each SQL Server version. – Andomar Jul 07 '11 at 17:16
0

Basically, there is none, and the example above that states it is "dynamic SQL" is not dynamic SQL. Dynamic SQL is when you construct the SQL expression itself, "dynamically". The prior example is a static command. Merely having a constant string for SQL doesn't make it "dynamic" -- that's the whole "dynamic" thing -- the SQL itself can change based on other conditions.

From: https://www.sqlshack.com/dynamic-sql-in-sql-server/

"Dynamic SQL is the SQL statement that is constructed and executed at runtime based on input parameters passed."

Richard Berman
  • 1