CODEWITHSUNDEEP
sql-servert-sql
Alphatrix
Alphatrix

Reputation: 83

MS sql insert random number set to the parameter using stored procedure

How do i make the parameter code_gen, give and insert a random value.

 @DATE_CREATED datetime = null,
 @STATUS varchar(10) = 'Open',
 @CODE_GEN as ('TN'+ SELECT LEFT(CONVERT(varchar(100), NEWID()),3))+'-'+RIGHT(CONVERT(varchar(100), NEWID()),5)

as
set nocount on
INSERT INTO MEDREC_CODEGEN (Status,DATE_CREATED,CODE_GEN)
values (@STATUS, COALESCE(@DATE_CREATED,GETDATE()), @CODE_GEN)

Upvotes: 3

Views: 642

Answers (3)

EzLo
EzLo

Reputation: 14189

You can't use expressions as default values on a stored procedure declaration. You can, however, use them with the SET statement once inside your code.

This fails:

CREATE PROCEDURE MyProcedure
    @Parameter INT = (100 + 50) -- Incorrect syntax near '('.
AS
BEGIN

    RETURN @Parameter

END

This works:

CREATE PROCEDURE MyProcedure
    @Parameter INT = NULL
AS
BEGIN

    SET @Parameter = 100 + 50

    RETURN @Parameter

END

For your case:

CREATE PROCEDURE YourProcedure
    @DATE_CREATED datetime = null,
    @STATUS varchar(10) = 'Open',
    @CODE_GEN VARCHAR(200) = NULL
as
BEGIN

    set nocount on

    IF @CODE_GEN IS NULL
        SET @CODE_GEN = ('TN'+ SELECT LEFT(CONVERT(varchar(100), NEWID()),3))+'-'+RIGHT(CONVERT(varchar(100), NEWID()),5)

    INSERT INTO MEDREC_CODEGEN (Status,DATE_CREATED,CODE_GEN)
    values (@STATUS, COALESCE(@DATE_CREATED,GETDATE()), @CODE_GEN)

END

Upvotes: 3

I A Khan
I A Khan

Reputation: 8839

Use this may be help 

DECLARE @DATE_CREATED AS DATETIME = NULL

 DECLARE @STATUS AS  varchar(10) = 'Open'

 DECLARE  @CODE_GEN as varchar(10) = NULL
 SET @CODE_GEN = ('TN'+ (SELECT LEFT(CONVERT(varchar(100), NEWID()),3))+'-
                 '+RIGHT(CONVERT(varchar(100), NEWID()),5))

set nocount on
        INSERT INTO MEDREC_CODEGEN (Status,DATE_CREATED,CODE_GEN)
        values (@STATUS, COALESCE(@DATE_CREATED,GETDATE()), @CODE_GEN)

Here what I did

create table #Temp
(
    Status Varchar(50), 
    DATE_CREATED Varchar(50), 
    CODE_GEN Varchar(50)
)
DECLARE @DATE_CREATED AS DATETIME = NULL

 DECLARE @STATUS AS  varchar(10) = 'Open'

 DECLARE  @CODE_GEN as varchar(10) = NULL
 SET @CODE_GEN = ('TN'+ (SELECT LEFT(CONVERT(varchar(100), NEWID()),3))+'-
                 '+RIGHT(CONVERT(varchar(100), NEWID()),5))

set nocount on
        INSERT INTO #Temp (Status,DATE_CREATED,CODE_GEN)
        values (@STATUS, COALESCE(@DATE_CREATED,GETDATE()), @CODE_GEN)


Select * FROM #Temp

Result :

Status  DATE_CREATED         CODE_GEN
Open    Apr  4 2018 12:23PM  TN437-

Upvotes: 0

gbn
gbn

Reputation: 432271

SQL Server accepts only literals as parameter defaults.

default

A default value for a parameter. If a default value is defined for a parameter, the procedure can be executed without specifying a value for that parameter. The default value must be a constant or it can be NULL..

Change it into a SET statement inline, perhaps with an ISNULL if you set the default to NULL.

Upvotes: 1

Related Questions