Current value of a Sequence as the default parameter for a stored procedure

Question

I have a sql stored procedure which consists of two parameters. And I need to set the current value of a Sequence as the default value of a one parameter. So when I added default value for the parameter it gives me following error.

Msg 102, Level 15, State 1, Procedure sp_GET_TransformationSummary, Line 8
Incorrect syntax near '('.
Msg 156, Level 15, State 1, Procedure sp_GET_TransformationSummary, Line 11
Incorrect syntax near the keyword 'AS'.

And here is my code..

ALTER PROCEDURE [dbo].[sp_GET_TransformationSummary]
@AreaCode           AS NVARCHAR(MAX),
@SyncId             AS INT = (SELECT CONVERT(INT, CURRENT_VALUE) FROM SYS.SEQUENCES WHERE name = 'SQ_COMMON')

AS
BEGIN
SET NOCOUNT ON;

SELECT * FROM TABLE_NAME
WHERE Area= @AreaCode AND SyncId = @SyncId;

END

And Is there any way to do this?

I need to have the CURRENT_VALUE of SQ_COMMON sequence as the default value.

Horia · Accepted Answer · 2015-08-27 06:43:06Z

3

You cannot put a SELECT statement in DEFAULT parameter definition for your stored procedure. The workaround for you is to set NULL as DEFAULT value, and then check if value of @SyncId is null => assign it the current value from your sequence.

ALTER PROCEDURE [dbo].[sp_GET_TransformationSummary]
@AreaCode           AS NVARCHAR(MAX),
@SyncId             AS INT = NULL

AS
BEGIN
SET NOCOUNT ON;

IF @SyncId IS NULL 
    SELECT @SyncId = CONVERT(INT, CURRENT_VALUE) FROM SYS.SEQUENCES WHERE name = 'SQ_COMMON'

SELECT * FROM TABLE_NAME
WHERE Area= @AreaCode AND SyncId = @SyncId;

END

answered Aug 27, 2015 at 6:43

Horia

1,61211 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mohan111 · Accepted Answer · 2015-08-27 06:39:53Z

1

Just make Syncid input parameter NUll and Inside the Store Procedure Declare another variable and assign the statement and make that Declared variable equal to Input Parameter .

 ALTER PROCEDURE [dbo].[sp_GET_TransformationSummary]
    @AreaCode           NVARCHAR(MAX),
    @SyncId             INT  = NULL
    AS
    BEGIN
    SET NOCOUNT ON;
    DECLARE @n_syncID INT
    SET @n_syncID  = 
    SELECT CONVERT(INT, CURRENT_VALUE) FROM SYS.SEQUENCES WHERE name = 'SQ_COMMON'
    SET @n_syncID = @SyncId 

    SELECT * FROM TABLE_NAME
    WHERE Area= @AreaCode AND SyncId = @SyncId;

    END

edited Aug 27, 2015 at 6:39

answered Aug 27, 2015 at 6:37

mohan111

8,8854 gold badges35 silver badges61 bronze badges

3 Comments

tarzanbappa Over a year ago

I tried this way. But I need to have it as a default parameter value as I mentioned in the question

mohan111 Over a year ago

So you are looking to add the value to input parameter.Can you tell me how many values are going to for that select statement Single or Mutliple @tarzanbappa

mohan111 Over a year ago

can you please go through this one stackoverflow.com/questions/14137925/… @tarzanbappa

Collectives™ on Stack Overflow

Current value of a Sequence as the default parameter for a stored procedure

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related