Creating function on SQL

Question

I am repeating the same query over and Over.Is there a way i can create a function in the following query and pass as parameter to make it more readable.

USE [ES]
      GO    
DECLARE @current_value AS BigInt;
DECLARE @s nvarchar(1000);

SELECT @current_value = (SELECT sequence_id FROM SEQUENCES where  seq='seq1')
   if (@current_value > '0')
   SET @s = N'
ALTER SEQUENCE seq1 RESTART WITH ' + CAST(@current_value AS nvarchar(10));
    EXEC (@s);

SELECT @current_value = (SELECT sequence_id FROM SEQUENCES where seq='seq2')
    if (@current_value > '0')
    SET @s = N'
ALTER SEQUENCE seq2 RESTART WITH ' + CAST(@current_value AS nvarchar(10));
    EXEC (@s);

SELECT @current_value = (SELECT sequence_id FROM SEQUENCES where seq='seq3')
   if (@current_value > '0')
    SET @s = N'
ALTER SEQUENCE seq3 RESTART WITH ' + CAST(@current_value AS nvarchar(10));
    EXEC (@s);
    GO

Arnaud Peralta · Accepted Answer · 2019-03-08 21:25:18Z

1

Your query contains some DDL clauses, who can't fit in a user defined function. You should a stored procedure for your query.

CREATE PROC myProc @current_value BIGINT, @s NVARCHAR(1000)
AS    
SELECT .... 
GO;

So after this, you query will be :

EXECUTE dbo.myProc @current_value = ... , @s = .....

answered Mar 8, 2019 at 21:25

Arnaud Peralta

1,3051 gold badge17 silver badges20 bronze badges

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

Comments

Andy · Accepted Answer · 2019-03-08 21:39:48Z

Something like a table variable could work instead of a function (note: I changed your @s to @sql so I could use my own @s):

USE [ES]
      GO    
DECLARE @current_value AS BigInt;
DECLARE @sql nvarchar(1000);
DECLARE @seq TABLE (seq NVARCHAR(4), idx INT)
INSERT INTO @seq (seq, idx)
VALUES ('seq1',1),('seq2',2),('seq3',3),('seq4',4)
DECLARE @i INT = 0
DECLARE @s NVARCHAR(4)

WHILE @i <= (SELECT COUNT(*) FROM @seq)
BEGIN
    SET @i = @i + 1
    SET @s = (SELECT seq from @seq WHERE idx = @i)
    SELECT @current_value = (SELECT sequence_id FROM SEQUENCES where seq=@s)

    if (@current_value > '0')
        SET @sql = N'
            ALTER SEQUENCE '+@s+' RESTART WITH ' + CAST(@current_value AS nvarchar(10));
    EXEC (@sql);
END

Collectives™ on Stack Overflow

Creating function on SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related