Hey there! Let's get your stored procedure working correctly to generate that random CODE_GEN value and insert it into your MEDREC_CODEGEN table. Your current code has a syntax error in how you're defining the @CODE_GEN parameter—SQL Server doesn't allow complex concatenation expressions directly as a parameter default value. Let's walk through the fixed solution step by step.

Key Issues in Your Original Code #

• You can't define a parameter like @CODE_GEN as ('TN'+ SELECT LEFT(CONVERT(varchar(100), NEWID()),3))+'-'+RIGHT(CONVERT(varchar(100), NEWID()),5)—this isn't valid T-SQL syntax for parameter defaults.
• Calling NEWID() twice will generate two different random values, so the left and right parts of your CODE_GEN would come from separate GUIDs (probably not what you want).

Fixed Solution: Generate Random CODE_GEN Internally #

This version creates a reusable random value from a single NEWID() call, handles optional parameters properly, and follows T-SQL best practices:

CREATE PROCEDURE InsertMedRecCodeGen
    @DATE_CREATED datetime = NULL,
    @STATUS varchar(10) = 'Open',
    @CODE_GEN varchar(20) = NULL -- Optional: Allow manual override of the code
AS
BEGIN
    SET NOCOUNT ON; -- Suppress "rows affected" messages, best practice for stored procs

    -- Auto-generate CODE_GEN if no value was passed in
    IF @CODE_GEN IS NULL
    BEGIN
        -- Capture a single GUID to use for both parts of the code
        DECLARE @RandomGuid varchar(100) = CONVERT(varchar(100), NEWID());
        SET @CODE_GEN = 'TN' + LEFT(@RandomGuid, 3) + '-' + RIGHT(@RandomGuid, 5);
    END

    -- Perform the insert
    INSERT INTO MEDREC_CODEGEN (Status, DATE_CREATED, CODE_GEN)
    VALUES (@STATUS, COALESCE(@DATE_CREATED, GETDATE()), @CODE_GEN);
END

Alternative: Generate Directly in the Insert (Simpler, but with a Catch) #

If you don't need to allow manual overrides of CODE_GEN, you can skip the parameter entirely and generate the value directly in the INSERT statement. Just note that this uses two separate NEWID() calls, so the left and right segments will come from different GUIDs:

CREATE PROCEDURE InsertMedRecCodeGen
    @DATE_CREATED datetime = NULL,
    @STATUS varchar(10) = 'Open'
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO MEDREC_CODEGEN (Status, DATE_CREATED, CODE_GEN)
    VALUES (
        @STATUS,
        COALESCE(@DATE_CREATED, GETDATE()),
        'TN' + LEFT(CONVERT(varchar(100), NEWID()), 3) + '-' + RIGHT(CONVERT(varchar(100), NEWID()), 5)
    );
END

How to Use the Procedure #

You can call it in a few ways:

• Let it auto-generate everything: EXEC InsertMedRecCodeGen;
• Specify a custom status: EXEC InsertMedRecCodeGen @STATUS = 'Closed';
• Override the CODE_GEN value manually: EXEC InsertMedRecCodeGen @CODE_GEN = 'TN-ABC-12345';

内容的提问来源于stack exchange，提问作者Alphatrix