CODEWITHSUNDEEP
sql-servert-sql
user2410266
user2410266

Reputation: 551

Creating function on SQL

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

Upvotes: 0

Views: 95

Answers (2)

Andy
Andy

Reputation: 3170

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

Upvotes: 0

Arnaud Peralta
Arnaud Peralta

Reputation: 1305

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 = .....

Upvotes: 1

Related Questions