In SQL Server, how do you create a default constraint for a column using the result or return value from a Stored procedure

constraintdefault valuestored-procedures

In SQL Server, can you create a default constraint for a column using the result or return value from a Stored procedure, and if so how?

So I am thinking of something along the lines of this, but with valid SQL.

ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_MyTable_MyColumn] DEFAULT (EXEC [dbo].[GetNextNumber]) FOR [MyColumn] GO

My reason for this is I have a table which has the sole responsibility of providing the next number to use, and a table which the default value must use this next number every time a new record is entered.

Best Answer

You can't use a SP in a default, but you can use a function. But presumably your SP code has to increment the value in the NextNumber table, so that won't work for you.

Your best bet for doing this within SQL is probably to have an INSERT trigger on MyTable which calls the SP and sets MyColumn.

However you'll have to carefully consider the behaviour of your SP code when multiple concurrent inserts are involved.
For example, you'll probably need to select the current value from the NextNumber table using an UPDLOCK hint to prevent another user reading and using the same value. (Note that the UPDLOCK hint will only prevent reads in this way if it's within a transaction [which triggers run within in by default], and if other selects on the NextNumber table use UPDLOCK as well.)

Related Solutions

Sql-server – Stored Procedure Create Table from Variable with Variable Constraint

Your logic seems fine, you are getting a syntax error because you aren't delimiting your strings correctly.

[PeriodClosedUID] [varchar](3) NULL,)' <-- problem

Also it's very helpful to be liberal with carriage returns and indenting, so problems like this aren't hidden 4 screens over and only accessible via horizontal scrollbar.

CREATE PROCEDURE dbo.procFinancialPeriodTable -- always use schema prefix
    @TABLENAME SYSNAME
AS
BEGIN -- use body wrappers around whole body
  SET NOCOUNT ON; -- put this at the beginning - no point in setting it at the end

  DECLARE @SQL NVARCHAR(MAX); -- dynamic SQL should always be Unicode

  SELECT @SQL = 'CREATE TABLE dbo.' + QUOTENAME(@TABLENAME) + '('
    + '[ID] [int] IDENTITY(1,1) NOT NULL,[FinPeriodNr] [int] NOT NULL,
       [FinCurrentPeriod] [bit] NULL,[FinStart] [date] NULL,
       [FinEnd] [date] NULL,[FinStatusOpen] [bit] NOT NULL,
       [PeriodClosedTS] [smalldatetime] NULL,[PeriodClosedUID] [varchar](3) NULL,
       CONSTRAINT [PK_FinancialPeriod_' + @TABLENAME + '_1] PRIMARY KEY CLUSTERED(
         [FinPeriodNr]));'; -- all those options you specified were verbose defaults

  EXEC sp_executesql @sql; -- instead of EXEC(@sql)
END
GO

Some links:

SQL Server – How to Create a Table Within a Stored Procedure

My answer here is based on this one statement:

Error: I don't have permission to create a table.

You can use EXECUTE AS in your stored procedure to give it permissions that the person calling it doesn't have. This should be used with caution as permissions are granted with good reason. For example if you create a user called TableCreator and grant it the ability to create tables in the temp schema.

GRANT CREATE TABLE TO TableCreator 
GRANT ALTER ON schema::temp TO TableCreator

Then create your stored procedure with the following:

CREATE PROCEDURE myProc
WITH EXECUTE AS 'TableCreator'
AS 
...

Now any time that stored procedure is run it will run as the user TableCreator which will have the ability to create, alter and drop tables within the schema temp.

Best Answer

Related Solutions

Sql-server – Stored Procedure Create Table from Variable with Variable Constraint

SQL Server – How to Create a Table Within a Stored Procedure

Related Question