SQL Server Stored Procs include file statement

Question

I don't think it is possible to do so what I would but I ask anyway.

I've found that I include the same variables in the top of every Stored Proc I make. These variables are used for logging and error handling. They don't change between stored procs, there meaning if fixed but primary use is to help readability and have a consistent style.

-- Declare code that resolve to possible Error
DECLARE @CONFLICT_CODE AS INT 
SET @CONFLICT_CODE= -99

-- Check for Conflict
IF Found > 0
BEGIN
    SELECT  @Error = @CONFLICT_CODE
END

I would be great to define these in a file that I could include into the stored proc.

I'm developing on SQL Server 2008 deploying to SQL Server 2005

Answer 1

You can't do this in TSQL. Another way, there is no include or macro feature

However, you could create a UDF thus:

CREATE FUNCTION dbo.CONFLICT_CODE()
RETURNS int
AS
BEGIN
    RETURN -99
END

This would replace this in every proc

-- Declare code that resolve to possible Error
DECLARE @CONFLICT_CODE AS INT 
SET @CONFLICT_CODE= -99

and you'd use this

SELECT  @Error = dbo.CONFLICT_CODE()

Answer 2

No, there isn't an include capability, but you may be able to minimize the effort to add your standard code.

You can add your code to a file, open the file when you need the standard code, and copy and paste the code.

You can also create a template file. In SQL Server Management Studio, you can create templates and load the template when you want to create a new stored procedure. Upon loading a template, your standard code would appear in the query tool. It becomes the starting point for coding your new stored procedure.