Hi All
I have the following stored procedure:
ALTER Procedure dbo.APPL_ServerEnvironmentInsert
(
@ServerEnvironmentName varchar(50),
@ServerEnvironmentDescription varchar(1000),
@UserCreatedId uniqueidentifier,
@ServerEnvironmentId uniqueidentifier OUTPUT
)
WITH RECOMPILE
AS
-- Stores the ServerEnvironmentId.
DECLARE @APPL_ServerEnvironment TABLE (ServerEnvironmentId uniqueidentifier)
-- Insert the data into the table.
INSERT INTO APPL_ServerEnvironment WITH(TABLOCKX)
(
ServerEnvironmentName,
ServerEnvironmentDescription,
DateCreated,
UserCreatedId
)
OUTPUT Inserted.ServerEnvironmentId INTO @APPL_ServerEnvironment
VALUES
(
@ServerEnvironmentName,
@ServerEnvironmentDescription,
GETDATE(),
@UserCreatedId
)
-- If @ServerEnvironmentId was not supplied.
IF (@ServerEnvironmentId IS NULL)
BEGIN
-- Get the ServerEnvironmentId.
SELECT @ServerEnvironmentId = ServerEnvironmentId
FROM @APPL_ServerEnvironment
END
The ServerEnvironmentId column is a primary key with a default set on it, which is (newsequentialid()).
I need this stored procedure to work for 2 scenarios:
- Value supplied for ServerEnvironmentId - WORKS.
- Value not supplied for ServerEnvironmentId - DOES NOT WORK - CANNOT INSERT NULL VALUE. I thought by setting a default on this column this would be fine.
Someone please help to ammend this procedure so that it may work for both scenarios. Solution needs to have minimal changes as all sp's currently following this trend.