Common functions and operations in SQL Server stored procedures

1. Case statement

• Used to select statements　　　　

SELECTProductNumber, Category=       CaseProductLine when 'R'  Then 'Road'          when 'M'  Then 'Mountain'          when 'T'  Then 'Touring'          when 'S'  Then 'Other sale Items'         ELSE 'Not for sale'      END, Name fromproduction.productORDER  byProductNumber;

View Code

• For update operations

UPDATEHumanResources.EmployeeSETVacationHours=     (  Case          when((vacationhours- 10.00)< 0) ThenVacationHours+  +         ELSE(vacationhours+ 20.00)       END) OUTPUT Deleted.businessentityid, Deleted.vacationhours asBeforevalue, Inserted.vacationhours asAftervalueWHERESalariedflag= 0

View Code

• The user assigns a value to the variable

    SET @ContactType =          Case             --Check for Employee             when EXISTS(SELECT *  fromHumanResources.Employee aseWHEREE.businessentityid= @BusinessEntityID)                  Then 'Employee'            --Check for Vendor             when EXISTS(SELECT *  fromPerson.businessentitycontact asBecWHEREBec. BusinessEntityID= @BusinessEntityID)                  Then 'Vendor'            --Check for Store             when EXISTS(SELECT *  fromPurchasing.vendor asvWHEREV.businessentityid= @BusinessEntityID)                  Then 'Store Contact'            --Check for individual consumer             when EXISTS(SELECT *  fromSales.Customer asCWHEREC.personid= @BusinessEntityID)                  Then 'Consumer'        END;

View Code

• Project examples

--depending on the type, determine the assignment operation construct the Operation log table description    Set @tempopdes= Case @servertype  when '0'  Then 'Assigning Customers'                                     when '1'  Then 'Assigning accounts'                                     when '2'  Then 'Assigning accounts'                                     when '4'  Then 'Assigning Customers'                                     when '5'  Then 'Assigning Customers'                                     when '3'  Then 'Assigning Customers'                                     when '7'  Then 'Assigning Customers'                                    Else "'                                End

View Code

2. Cast and CONVERT functions

Expression)])(expression])

• CAST

The expression type is converted mainly when the string is spliced, and it is commonly used to convert an int or other numeric type to a varchar type.

Cast Example:

SELECT DISTINCT CAST  as Char (ten  as Name, S.unitpricefromJOINon=  p.) ProductIDwherelike'long-sleeve Logo Jersey, M';

View Code

• CONVERT

Convert is more, finer, and often used as a time conversion operation than cast can convert

convert Example:

CONVERT (nvarchar(GETDATE126)

View Code

Time format reference SQL Server online documentation

3. Reverse inversion function

Reverse function action expression must be a character type or be implicitly convertible to a character type

Declare @ids varchar ($) Set @ids = ' China, 2 you 2,34,56 ' Select @ids Select  REVERSE (@ids)

View Code

4. charindex function

(,expression2,)

Find the location information for expression1 in expression2 from [start_location] (not set or negative, starting at 0), and return the value of the position immediately after finding it. Start position count is 1

DECLARE @document varchar( -)SELECT @document = 'Bicycle reflectors is vital safety' +                   'Bicycle components of your bicycle.'SELECT CHARINDEX('Bicycle',@document)GO

View Code

5. Other functions
Min min value
Max Maximum Value
Len Gets the string length

6. Global variable @ @ROWCOUNT

Used to get information about the number of rows affected by the most recent operation.

7. Exec

• Execute stitching statements　　

EXECUTE ('+@schemaname+'. ' + @tablename + ' REBUILD; ');

View Code

• Call a stored procedure

Variable @sql must be known as nvarchar type in the exec sp_executesql @sql

Set @sql = ' Select @a=sum (Mi_receivermoney) from t_money_receive where  mr_id in ('+@mr_ids  +')'        exec@sql, N'@a Decimal (18,2) output',@receivetotalmoney output

View Code

　　　

Resources:

SQL Server online Documentation

Common functions and operations in SQL Server stored procedures