Default Values Question

microsoft.public.sqlserver.programming

13 Nov 2006 3:07 PM

Mark

I'm fairly new to SQL Server 2005 so this is probably an easy question
for someone. All I want to be able to do is create a stored procedure
that can insert default values. I have a testing table as defined
below:

----------------------------------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[Test](
    [Key] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Test_Key] DEFAULT
(newsequentialid()),
    [SomeData] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
    [Key] ASC
)
----------------------------------------------------------------------------------------------------------------------------

I have a stored procedure as follows:

-------------------------------------------------------------------
ALTER PROCEDURE [dbo].[sp_Test_Insert]
@Key uniqueidentifier = default,
@SomeData varchar (50)

AS

-- INSERT a new row in the table
INSERT INTO [dbo].[Test]([Key],[SomeData] ) VALUES (@Key, @SomeData)
------------------------------------------------------------------

Calling the stored procedure as such produces an error:

EXEC    @return_value = [dbo].[sp_Test_Insert]
        @SomeData = N'Test Data Here!'

Error: Cannot insert the value NULL into column 'Key', table
'Test.dbo.Test'; column does not allow nulls. INSERT fails.

Why is does this not work?

I really don't want to have to test for each variable that has Default
Values (e.g. DateCreated, DateLastModified, etc) and create separate
Insert Statements leaving out the specific field name in the values
clause

(e.g. INSERT INTO [dbo].[Test]([SomeData] ) VALUES (@SomeData)) .

This is way too much work and combinations so hopefully somebody will
be able to tell me what a quick solution is?

Any ideas????

Thanks
Mark