Creating Stored Procs based on SQL Server Version

[eyinkr]

We have a product that we were experiencing high recompile rates do to
temp tables, we have re-written them to use table variables to reduce
recompile rate. The problem I am having is that we have some
customers still on SQL 7 where the table variables were not valid. I
would like to have one installation script that would create the
stored procedure with table variables on SQL Server 2000 and without
on SQL Server 7, I have been unsuccessful do to the limitation that
CREATE PROCEDURE statement cannot be in a script with any other
statements.

Was wondering if anyone else has run into this and found a way around
it.

[Erland Sommarskog]

Re: Creating Stored Procs based on SQL Server Version

eyinkr (eyinkr@hotmail .com) writes:[color=blue]
> We have a product that we were experiencing high recompile rates do to
> temp tables, we have re-written them to use table variables to reduce
> recompile rate. The problem I am having is that we have some
> customers still on SQL 7 where the table variables were not valid. I
> would like to have one installation script that would create the
> stored procedure with table variables on SQL Server 2000 and without
> on SQL Server 7, I have been unsuccessful do to the limitation that
> CREATE PROCEDURE statement cannot be in a script with any other
> statements.
>
> Was wondering if anyone else has run into this and found a way around
> it.[/color]

There is no built-in to SQL Server for handle this. However, you can
use a preprocessor. If you have Visual C++, the C preprocessor is
probably the most natural use. It goes without saying that there is
some work to package the preprocessor with your load process.

We have faced the same problem in our shop, although in our case it
was SQL 6.5 and SQL 2000 we needed to support. We didn't go for the
C preprocessor, but rather I wrote our own that was fully integrated
with the rest of our toolset for building and installing database
object. This preprocessor, named Preppis, is available as freeware
together with the rest of the toolset on http://www.abaris.se/abaperls/.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp