Summary of stored procedures

1. Stored procedures

  • Querying stored Procedures

    select * from sys.objects where name = 'GetUser' -- Stored procedure name
Copy the code

  • Create and modify stored procedures

    create / alter proc GetUser  Create the stored procedure GetUser
@Id int Parameters -
as 
set nocount on;  -- Does not return a count to improve application performance
begin - start
    select * from [dbo].[User] where Id=@Id  -- Execute SQL statements
-- return @id if a value is required
end;End -
Copy the code

  • delete

    drop proc GetUser - delete GetUser
Copy the code

  • perform

    EXEC GetUser 1; - 1 is the parameter
Copy the code

2. The Function (Function)

Only table variables can be used

  • Query function

    select * from sys.objects where name = 'f_splitlianxi' -- Function name
Copy the code

  • To create change

    create / ALTER  FUNCTION [dbo].[f_splitlianxi]       -----id is the number of fields, and val is the value of the field
(
@str NVARCHAR(max),-- The string to split
@fengefu NVARCHAR(20)-- The symbol for partitioning
)RETURNS @table TABLE(id INT,val NVARCHAR(max))
AS
BEGIN 
    DECLARE @index INT.@startsplit INT.@id INT --@index subscript of the location of the separator @startsplit The location at which each split begins
    SELECT @index=CHARINDEX(@fengefu.@str),@startsplit=1.@id=1
    WHILE @index>0
    BEGIN
        IF @id>1  The code in this method body is executed after the first loop
        BEGIN 
            SELECT @startsplit=@index+LEN(@fengefu) -- The start of the split is equal to the previous character subscript position + the length of the character itself
            SELECT @index=CHARINDEX(@fengefu.@str.@startsplit)
            
        END 
        IF @index>0   -- The first loop intercept position starts at @startsplit=1
            BEGIN 
                INSERT INTO @table VALUES (@id.SUBSTRING(@str.@startsplit.@index-@startsplit)) -- The length to intercept at the beginning of the string to intercept
            END 
        ELSE 
        BEGIN   -- The last loop will truncate the rest of the string because the @index=0 split character cannot be matched
            INSERT INTO @table VALUES (@id.SUBSTRING(@str.@startsplit,LEN(@str)-@startsplit+1))
        END 
        SELECT @id=@id+1
    END
    RETURN 
END
Copy the code

  • delete

    drop function function_name; -- Function name
Copy the code

  • perform

    select * from dbo.f_splitlianxi('123; 234 '.'; ')
Copy the code

    The results are as follows

    id val
    1 123
    2 234

The difference between the two

The differences between a stored procedure and a function are: 1. A stored procedure is a precompiled collection of SQL statements and optional control flow statements, while a function is a subroutine composed of one or more SQL statements. 2. Stored procedures can execute a series of SQL statements in a single stored procedure, while custom functions have many limitations. 3. Different ways of execution.