sql server 基础知识

存储过程中的相关

临时表是在TempDB中创建的表。临时表的名称都以“#”开头。

临时表的范围为创建临时表的连接。因为，临时表不能在两个连接之间共享，一旦连接关闭，临时表就会被丢弃。

如果临时表被创建于存储过程之中，则临时表的范围在存储过程之中，或者被该存储过程调用的任何存储过程之中。如果需要在连接之间共享临时表，则需要使用全局的临时表。

全局的临时表以“##”符号开头，它将一直存在于数据库中，直到SQL Server重新启动。一旦这类临时表创建之后，所有的用户都可以访问到。

在临时表上不能明确地指明权限。 临时表提供了存储中间结果的能力。有时候，临时表还能通过将一个复杂的查询分解成两个查询而获得性能的改善。

这可以通过首先将第一个查询的结果存在临时表中，然后在第二个查询中使用临时表来实现。当一个大表中的某个子集在一个在座过程中使用多次时，建议使用临时表。

在这种情况下，在临时表中保持数据的子集，以在随后的连接中使用，这样能大大改善性能。还可以在临时表中创建索引。

 

SET ANSI_NULLS ON----------------标准要求在对空值进行等于 (=) 或不等于 (<>) 比较时取值为 FALSE。

当 SET ANSI_NULLS 为 ON 时，即使 column_name 中包含空值，使用 WHERE column_name = NULL 的 SELECT 语句仍返回零行。

即使 column_name 中包含非空值，使用 WHERE column_name <> NULL 的 SELECT 语句仍会返回零行。

 

isnull的用法

select isnull(studentid,100) from student_info

　　解释：这个实例的意思就是查出student_info表中字段为studentid的值，如果字段studentid值为空就在字段studentid中添加100，或者你可以动态的向这个空字段添加值。

 

SET QUOTED_IDENTIFIER ON---------当 SET QUOTED_IDENTIFIER 为 ON 时，标识符可以由双引号分隔，而文字必须由单引号分隔。

 

 

 

 

存储过程在Management Studio中的位置

 

如何查看存储过程的内容

下面的黑体字是存储过程的名字

EXEC sp_helptext N'cl_srd_SearchRedemptionOrderDetail';  

 

使用SSDT调试存储过程

https://msdn.microsoft.com/en-us/library/hh272701(v=vs.103).aspx

工具的下载地址https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt

 

select * from vie_cti_23 这个可能是表，也可能是视图

 

如何执行存储过程

使用sql prompt

直接输入 exec，然后输入存储过程名字的时候，如果sql prompt找到了，直接enter键，sql prompt会自动补全

使用sql server自带的功能

先找到存储过程，然后右键，Execute Stored Procedure，在弹出的界面输入参数

之后sql server会自动生成以下的语句

USE [d_lisa_Program_dev01_v4300]
GO

DECLARE    @return_value int,
        @ScheduleID int

EXEC    @return_value = [dbo].[ps_fud_QueryTask]
        @CurrentTime = NULL,
        @ScheduleID = @ScheduleID OUTPUT

SELECT    @ScheduleID as N'@ScheduleID'

SELECT    'Return Value' = @return_value

GO

 

查看存储过程的依赖项

可以查看谁依赖存储过程，也可以查看存储过程依赖哪些对象

 

 

Functions

 @@Error

https://docs.microsoft.com/en-us/sql/t-sql/functions/error-transact-sql

Returns the error number for the last Transact-SQL statement executed.

Remarks

Returns 0 if the previous Transact-SQL statement encountered no errors.

Returns an error number if the previous statement encountered an error. If the error was one of the errors in the sys.messages catalog view, then @@ERROR contains the value from the sys.messages.message_id column for that error. You can view the text associated with an @@ERROR error number in sys.messages.

Because @@ERROR is cleared and reset on each statement executed, check it immediately following the statement being verified, or save it to a local variable that can be checked later.

Use the TRY...CATCH construct to handle errors. The TRY...CATCH construct also supports additional system functions (ERROR_LINE, ERROR_MESSAGE, ERROR_PROCEDURE, ERROR_SEVERITY, and ERROR_STATE) that return more error information than @@ERROR. TRY...CATCH also supports an ERROR_NUMBER function that is not limited to returning the error number in the statement immediately after the statement that generated an error. For more information, see TRY...CATCH (Transact-SQL).  

 

 SCOPE_IDENTITY

 https://docs.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql

 Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, if two statements are in the same stored procedure, function, or batch, they are in the same scope.

Remarks

  SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT (Transact-SQL).

SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.

Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY return different values at the end of an INSERT statement on T1. @@IDENTITY returns the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() returns the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function returns the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.

Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented.  

 

IDENT_CURRENT (Transact-SQL)

https://docs.microsoft.com/en-us/sql/t-sql/functions/ident-current-transact-sql 

Returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.

IDENT_CURRENT( 'table_name' )  

 

 

 OBJECT_ID

https://docs.microsoft.com/en-us/sql/t-sql/functions/object-id-transact-sql

 Returns the database object identification number of a schema-scoped object.

 Note: Objects that are not schema-scoped, such as DDL triggers, cannot be queried by using OBJECT_ID. For objects that are not found in the sys.objects catalog view, obtain the object identification numbers by querying the appropriate catalog view. For example, to return the object identification number of a DDL trigger, use SELECT OBJECT_ID FROM sys.triggers WHERE name = 'DatabaseTriggerLog``'.  

判断临时表是否存在，如果存在，就drop掉

  IF OBJECT_ID('tempdb..#temp') IS NOT NULL
            DROP TABLE #temp;

 

 

 

 REPLACE

https://docs.microsoft.com/en-us/sql/t-sql/functions/replace-transact-sql

Replaces all occurrences of a specified string value with another string value.

REPLACE ( string_expression , string_pattern , string_replacement )  

 

@@TRANCOUNT (Transact-SQL)

 https://docs.microsoft.com/en-us/sql/t-sql/functions/trancount-transact-sql

Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.

 

System stored procedures

sp_getapplock

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-getapplock-transact-sql

Places a lock on an application resource.

sp_getapplock [ @Resource = ] 'resource_name' ,  
     [ @LockMode = ] 'lock_mode'   
     [ , [ @LockOwner = ] 'lock_owner' ]   
     [ , [ @LockTimeout = ] 'value' ]  
     [ , [ @DbPrincipal = ] 'database_principal' ]  
[ ; ]  

DECLARE @res INT
    EXEC @res = sp_getapplock                 
                @Resource = 'CustomTableCheckLock',
                @LockMode = 'Exclusive',
                @LockOwner = 'Transaction',
                @LockTimeout = 10000,
                @DbPrincipal = 'public'
    IF @res NOT IN (0, 1)
    BEGIN       
        Return -1
    END 

 

Return Code Values

>= 0 (success), or < 0 (failure)

Value	Result
0	The lock was successfully granted synchronously.
1	The lock was granted successfully after waiting for other incompatible locks to be released.
-1	The lock request timed out.
-2	The lock request was canceled.
-3	The lock request was chosen as a deadlock victim.
-999	Indicates a parameter validation or other call error.