Microsoft SQL Server
संग्रहित प्रक्रियाएं

परिचय

वाक्य - विन्यास

• बनाएँ {प्रक्रिया | PROC} [स्कीमा_नाम।] प्रक्रिया_नाम
• [@parameter [type_schema_name।] डेटाटाइप
• [भिन्न] [= डिफ़ॉल्ट] [OUT | OUTPUT | सिफ़ पढ़िये ]
• , @parameter [type_schema_name।] डेटाटाइप
• [भिन्न] [= डिफ़ॉल्ट] [OUT | OUTPUT | सिफ़ पढ़िये ] ]
• [{एनसीआरपीवाई के साथ | RECOMPILE | खंड के रूप में देखें}]
• [रिपोर्ट के लिए]
• जैसा
• शुरू
• [Declaration_section]
• executable_section
• समाप्त;

एक मूल संग्रहीत प्रक्रिया बनाना और निष्पादित करना

लाइब्रेरी डेटाबेस में Authors तालिका का उपयोग करना

CREATE PROCEDURE GetName
(
    @input_id INT = NULL,      --Input parameter,  id of the person, NULL default
    @name VARCHAR(128) = NULL  --Input parameter, name of the person, NULL default
) 
AS
BEGIN 
    SELECT Name + ' is from ' + Country 
    FROM Authors 
    WHERE Id = @input_id OR Name = @name
END 
GO

आप कुछ अलग सिंटैक्स के साथ एक प्रक्रिया निष्पादित कर सकते हैं। सबसे पहले, आप EXECUTE या EXEC उपयोग कर सकते हैं

EXECUTE GetName @id = 1
EXEC Getname @name = 'Ernest Hemingway'

इसके अतिरिक्त, आप EXEC कमांड को छोड़ सकते हैं। इसके अलावा, आपको यह निर्दिष्ट करने की आवश्यकता नहीं है कि आप किस पैरामीटर से गुजर रहे हैं, क्योंकि आप सभी मापदंडों में पास होते हैं।

GetName NULL, 'Ernest Hemingway'

जब आप इनपुट पैरामीटर को एक अलग क्रम में निर्दिष्ट करना चाहते हैं, तो वे इस प्रक्रिया में कैसे घोषित किए जाते हैं, आप पैरामीटर नाम निर्दिष्ट कर सकते हैं और मान निर्दिष्ट कर सकते हैं। उदाहरण के लिए

 CREATE PROCEDURE dbo.sProcTemp 
 (
    @Param1 INT,
    @Param2 INT
)
AS
BEGIN

    SELECT
        Param1 = @Param1,
        Param2 = @Param2

END

इस प्रक्रिया को निष्पादित करने का सामान्य आदेश @ Param1 के लिए पहले मान और फिर @ Param2 दूसरा है। तो यह कुछ इस तरह दिखेगा

  EXEC dbo.sProcTemp @Param1 = 0,@Param2=1

लेकिन यह भी संभव है कि आप निम्नलिखित का उपयोग कर सकते हैं

  EXEC dbo.sProcTemp @Param2 = 0,@Param1=1

इसमें, आप @ param2 के लिए मूल्य पहले और @ Param1 दूसरे स्थान पर निर्दिष्ट कर रहे हैं। जिसका अर्थ है कि आपको उसी आदेश को रखने की आवश्यकता नहीं है जैसा कि प्रक्रिया में घोषित किया गया है, लेकिन आपकी इच्छानुसार कोई भी आदेश हो सकता है। लेकिन आपको यह निर्दिष्ट करने की आवश्यकता होगी कि आप किस पैरामीटर पर मूल्य निर्धारित कर रहे हैं

किसी भी डेटाबेस से संग्रहीत कार्यविधि

और यह भी आप एक उपसर्ग के साथ एक प्रक्रिया बना सकते हैं sp_ इन procuedres, सभी सिस्टम संग्रहित प्रक्रियाओं की तरह, एसक्यूएल सर्वर के डिफ़ॉल्ट व्यवहार की वजह से डेटाबेस निर्दिष्ट किए बिना क्रियान्वित किया जा सकता। जब आप "sp_" से शुरू होने वाली संग्रहीत प्रक्रिया को निष्पादित करते हैं, तो SQL सर्वर पहले मास्टर डेटाबेस में प्रक्रिया की तलाश करता है। यदि प्रक्रिया मास्टर में नहीं मिलती है, तो यह सक्रिय डेटाबेस में दिखता है। यदि आपके पास एक संग्रहीत कार्यविधि है जिसे आप अपने सभी डेटाबेस से एक्सेस करना चाहते हैं, तो इसे मास्टर में बनाएँ और एक ऐसे नाम का उपयोग करें जिसमें "sp_" उपसर्ग शामिल हो।

Use Master

CREATE PROCEDURE sp_GetName
(
    @input_id INT = NULL,      --Input parameter,  id of the person, NULL default
    @name VARCHAR(128) = NULL  --Input parameter, name of the person, NULL default
) 
AS
BEGIN 
    SELECT Name + ' is from ' + Country 
    FROM Authors 
    WHERE Id = @input_id OR Name = @name
END 
GO

बाहर के मानकों के साथ काम की प्रक्रिया

संग्रहीत कार्यविधियाँ अपनी पैरामीटर सूची में OUTPUT कीवर्ड का उपयोग करके मान लौटा सकती हैं।

एक एकल पैरामीटर के साथ संग्रहीत कार्यविधि बनाना

CREATE PROCEDURE SprocWithOutParams
(
    @InParam VARCHAR(30),
    @OutParam VARCHAR(30) OUTPUT
)
AS
BEGIN
    SELECT @OutParam = @InParam + ' must come out'   
    RETURN
END   
GO

संग्रहीत प्रक्रिया को निष्पादित करना

DECLARE @OutParam VARCHAR(30)    
EXECUTE SprocWithOutParams 'what goes in', @OutParam OUTPUT   
PRINT @OutParam 

कई बाहर मापदंडों के साथ एक संग्रहीत कार्यविधि बनाना

CREATE PROCEDURE SprocWithOutParams2
(
    @InParam VARCHAR(30),
    @OutParam VARCHAR(30) OUTPUT,
    @OutParam2 VARCHAR(30) OUTPUT
)
AS
BEGIN
    SELECT @OutParam = @InParam +' must come out'   
    SELECT @OutParam2 = @InParam +' must come out'
    RETURN
END    
GO

संग्रहीत प्रक्रिया को निष्पादित करना

DECLARE @OutParam VARCHAR(30)    
DECLARE @OutParam2 VARCHAR(30)  
EXECUTE SprocWithOutParams2 'what goes in', @OutParam OUTPUT, @OutParam2 OUTPUT   
PRINT @OutParam 
PRINT @OutParam2

यदि ... के साथ संग्रहीत प्रक्रिया और ऑपरेशन में सम्मिलित करें

उदाहरण तालिका बनाएं Employee :

CREATE TABLE Employee
(
    Id INT,
    EmpName VARCHAR(25),
    EmpGender VARCHAR(6),
    EmpDeptId INT
)

संग्रहीत कार्यविधि बनाता है जो जाँचता है कि संग्रहीत कार्यविधि में दिए गए मान शून्य या गैर रिक्त नहीं हैं और कर्मचारी तालिका में सम्मिलित ऑपरेशन करते हैं।

CREATE PROCEDURE spSetEmployeeDetails
(
    @ID int,
    @Name VARCHAR(25),
    @Gender VARCHAR(6),
    @DeptId INT
)
AS
BEGIN
    IF (
        (@ID IS NOT NULL AND LEN(@ID) !=0) 
        AND (@Name IS NOT NULL AND LEN(@Name) !=0)
        AND (@Gender IS NOT NULL AND LEN(@Gender) !=0)
        AND (@DeptId IS NOT NULL AND LEN(@DeptId) !=0)
    )
    BEGIN
        INSERT INTO Employee
        (
            Id,
            EmpName,
            EmpGender,
            EmpDeptId
        )
        VALUES
        (
            @ID,
            @Name,
            @Gender,
            @DeptId
        )
    END
ELSE
    PRINT 'Incorrect Parameters'
END
GO

संग्रहीत कार्यविधि निष्पादित करें

DECLARE @ID INT,
    @Name VARCHAR(25),
    @Gender VARCHAR(6),
    @DeptId INT

EXECUTE spSetEmployeeDetails
    @ID = 1,
    @Name = 'Subin Nepal',
    @Gender = 'Male',
    @DeptId = 182666 

संग्रहीत कार्यविधि में गतिशील एसक्यूएल

डायनामिक SQL हमें रन टाइम पर SQL स्टेटमेंट जेनरेट और रन करने में सक्षम बनाता है। जब हमारे SQL स्टेटमेंट में अलग-अलग कंपाइल समय में बदलाव हो सकते हैं, तो डायनेमिक SQL की आवश्यकता होती है।

गतिशील एसक्यूएल का सरल उदाहरण:

CREATE PROC sp_dynamicSQL
@table_name      NVARCHAR(20),
@col_name        NVARCHAR(20), 
@col_value       NVARCHAR(20) 
AS
BEGIN
DECLARE  @Query  NVARCHAR(max)
SET      @Query = 'SELECT * FROM ' + @table_name
SET      @Query = @Query + ' WHERE ' + @col_name + ' = ' + ''''+@col_value+''''
EXEC     (@Query)
END

उपरोक्त sql क्वेरी में, हम देख सकते हैं कि हम रन टाइम के समय @table_name, @col_name, and @col_value में मानों को परिभाषित करके उपरोक्त क्वेरी का उपयोग कर सकते हैं। क्वेरी रनटाइम पर उत्पन्न होती है और निष्पादित होती है। यह तकनीक है जिसमें हम एक चर में पूरी स्क्रिप्ट को स्ट्रिंग के रूप में बना सकते हैं और इसे निष्पादित कर सकते हैं। हम गतिशील एसक्यूएल और कॉन्सेप्टन अवधारणा का उपयोग करके अधिक जटिल प्रश्न बना सकते हैं। यह अवधारणा बहुत शक्तिशाली है जब आप एक स्क्रिप्ट बनाना चाहते हैं जिसका उपयोग कई शर्तों के तहत किया जा सकता है।

संग्रहीत प्रक्रिया को निष्पादित करना

DECLARE @table_name     NVARCHAR(20) = 'ITCompanyInNepal',
        @col_name       NVARCHAR(20) = 'Headquarter',
        @col_value      NVARCHAR(20) = 'USA'
    
EXEC    sp_dynamicSQL   @table_name,    
                        @col_name,
                        @col_value

तालिका मैंने उपयोग की है

उत्पादन

सिंपल लूपिंग

पहले कुछ डेटा को #systables नाम दिया #systables और एक वृद्धि पंक्ति को एड करते हैं ताकि हम एक बार में एक ही क्वेरी कर सकें।

select
    o.name,
    row_number() over (order by o.name) as rn
into
    #systables
from
    sys.objects as o
where
    o.type = 'S'

अगला हम लूपिंग को नियंत्रित करने के लिए कुछ चर घोषित करते हैं और इस उदाहरण में तालिका नाम को संग्रहीत करते हैं

declare
    @rn int = 1,
    @maxRn int = (
                    select
                        max(rn)
                    from
                        #systables as s
                    )
declare    @tablename sys name

अब हम एक सरल समय का उपयोग करके लूप कर सकते हैं। हम select कथन में @rn वृद्धि @rn हैं, लेकिन यह ex set @rn = @rn + 1 लिए एक अलग कथन हो सकता है, यह आपकी आवश्यकताओं पर निर्भर करेगा। हम यह भी के मूल्य का उपयोग @rn से पहले ही से एक रिकॉर्ड का चयन करने के वृद्धि की जाती है #systables । अंत में हम टेबल का नाम प्रिंट करते हैं।

while @rn <= @maxRn
    begin

        select
            @tablename = name,
            @rn = @rn + 1
        from
            #systables as s
        where
            s.rn = @rn

        print @tablename
    end

सिंपल लूपिंग

CREATE PROCEDURE SprocWithSimpleLoop
(
    @SayThis VARCHAR(30),
    @ThisManyTimes INT
)
AS
BEGIN
    WHILE @ThisManyTimes > 0
    BEGIN
        PRINT @SayThis;
        SET @ThisManyTimes = @ThisManyTimes - 1;
    END
    
    RETURN;
END    
GO