The highlights of Common Stored procedures are commonly used in mssql. you can choose to use them as needed.
The highlights of Common Stored procedures are commonly used in mssql. you can choose to use them as needed.
========================= Page ================================ ====
The code is as follows:
/* Query data by page */
Create procedure [dbo]. [GetRecordSet]
@ StrSql varchar (8000), -- query SQL, such as select * from [user]
@ PageIndex int, -- query the current page number
@ PageSize int -- records displayed on each page
AS
Set nocount on
Declare @ p1 int
Declare @ currentPage int
Set @ currentPage = 0
Declare @ RowCount int
Set @ RowCount = 0
Declare @ PageCount int
Set @ PageCount = 0
Exec sp_cursoropen @ p1 output, @ strSql, @ scroalopt = 1, @ ccopt = 1, @ rowcount = @ rowCount output -- get the total number of records
Select @ PageCount = ceiling (1.0 * @ rowCount/@ pagesize) -- get the total number of pages
, @ CurrentPage = (@ PageIndex-1) * @ PageSize + 1
Select @ RowCount, @ PageCount
Exec sp_cursorfetch @ p1, 16, @ currentPage, @ PageSize
Exec sp_cursorclose @ p1
Set nocount off
GO
====================================== User registration ================== ====================
/*
User registration.
*/
Create proc [dbo]. [UserAdd]
(
@ LoginID nvarchar (50), -- logon account
@ Password nvarchar (50), -- password
@ Email nvarchar (200) -- email address
)
As
Declare @ userID int -- user ID
-- The logon account has been registered
If exists (select loginID from tableName where loginID = @ loginID)
Begin
Return-1;
End
-- The email address has been registered.
Else if exists (select email from tableName where email = @ email)
Begin
Return-2;
End
-- Registration successful
Else
Begin
Select @ userID = isnull (max (userID), 100000) + 1 from tableName
Insert into tableName
(UserID, loginID, [password], userName, linkNum, address, email, createTime, status)
Values
(@ UserID, @ loginID, @ password, '', @ email, getdate (), 1)
Return @ userID
End
====================================== SQL server system stored procedures ============== ============
-1. add description information to fields in the table.
Create table T2 (id int, name char (20 ))
GO
EXEC sp_addextendedproperty 'Ms _ description', 'employee ID', 'user', dbo, 'table', T2, 'column', ID
EXEC sp_updateextendedproperty 'Ms _ description', 'This is a test', 'user', dbo, 'table', T2, 'column', id
-2. modify the database name
EXEC sp_renamedb 'old _ db_name ', 'New _ db_name'
-3. modify the data table name and field name
EXEC sp_rename 'old _ table_name ', 'New _ table_name'-modify the data table name
EXEC sp_rename 'Table _ name. [old_column_name] ', 'New _ column_name', 'column '-modify the field name
-4. get the stored procedure content by specifying the stored procedure name.
Exec sp_helptext sp_name
/* The following are system stored procedures or SQL statements related to security control. For more information about the syntax, see books online */
-Create a new SQL Server logon so that you can connect to the SQL Server that uses SQL Server authentication.
EXEC sp_addlogin @ loginame = '', @ passwd ='', @ defdb = '', @ deflanguage = NULL, @ sid = NULL, @ encryptopt = NULL
-Enables Windows NT users or group accounts to connect to SQL Server using Windows authentication.
EXEC sp_grantlogin @ loginame =''
-Delete the SQL Server login to prevent the use of this login name to access SQL Server.
EXEC sp_droplogin @ loginame =''
-Prevent Windows NT users or groups from connecting to SQL Server.
EXEC sp_denylogin @ loginame =''
-Delete the logon entries of Windows NT users or groups created with sp_grantlogin or sp_denylogin from SQL Server.
EXEC sp_revokelogin @ loginame =''
-Change the default database for logon.
EXEC sp_defaultdb @ loginame = '', @ defdb =''
-Change the default logon language.
EXEC sp_defaultlanguage @ loginame = '', @ language =''
-Add or change the SQL Server logon password.
EXEC sp_password @ old = '', @ new ='', @ loginame =''
-Add a new server role member.
EXEC sp_addsrvrolemember @ loginame = '', @ rolename =''
-Add a server role member.
EXEC sp_dropsrvrolemember @ loginame = '', @ rolename =''
-Add a security account to the current database for SQL Server logon or Windows NT users or groups, and grant it the permission to execute activities in the database (Grant the default "public" database role ).
EXEC sp_grantdbaccess @ loginame = '', @ name_in_db = NULL
-Or
EXEC sp_adduser @ loginame = '', @ name_in_db = NULL, @ maid =''
-Delete a security account from the current database.
EXEC sp_revokedbaccess @ name_in_db =''
-Or
EXEC sp_dropuser @ name_in_db =''
-Create a new database role in the current database.
EXEC sp_addrole @ rolename = '', @ ownername =''
-Delete a database role from the current database.
EXEC sp_droprole @ rolename =''
-Add a new member of the database role to the current database.
EXEC sp_addrolemember @ rolename = '', @ membername =''
-Delete a member of the database role from the current database.
EXEC sp_droprolemember @ rolename = '', @ membername =''
-Grant permissions to database roles, tables, stored procedures, and other objects
-1. authorized access
GRANT
-2. Access Denied
DENY
-3. cancel authorization or reject
REVOKE
-4. Sample (pubs ):
Grant select on authors TO Limperator
Deny select on authors TO Limperator
Revoke select on authors TO Limperator
============================== Stored procedure for database restoration ======================
The code is as follows:
SQL code
Create proc killspid (@ dbname varchar (20 ))
As
Begin
Declare @ SQL nvarchar (500)
Declare @ spid int
Set @ SQL = 'Clare getspid cursor
Select spid
From sysprocesses
Where dbid = db_id (''' + @ dbname + ''')'
Exec (@ SQL)
Open getspid
Fetch next from getspid
Into @ spid
While @ fetch_status <>-1
Begin
Exec ('Kill '+ @ spid)
Fetch next from getspid
Into @ spid
End
Close getspid
Deallocate getspid
End
GO
Purpose: Kill active processes passed into the database for exclusive backup and restoration operations.
============================ Convert Arabic numerals to uppercase Chinese ==================
For example, enter 12345, and the program will show you:
For example, enter 10023040, and the program will provide the following information:
One solution (tested in SqlServer2000 ):
The code is as follows:
SQL code
Create function fun_cgnum
(@ Num INT)
Returns varchar (100)
AS
BEGIN
DECLARE @ temp INT, @ res INT, @ I TINYINT
DECLARE @ str VARCHAR (100), @ no VARCHAR (20), @ unit VARCHAR (16)
SELECT @ str = '', @ no = 'Another employee, Wu Luhan, Wu Luyun, @ unit = 'yaohan wanzhibaibaibaibaibaiyi'
SET @ temp = @ num
SELECT @ I = 0, @ res = @ temp % 10, @ temp = @ temp/10
WHILE @ temp> 0
BEGIN
IF @ I = 0
SET @ str = SUBSTRING (@ no, @ res + 1, 1)
ELSE
SET @ str = SUBSTRING (@ no, @ res + 1,1) + SUBSTRING (@ unit, @ I, 1) + @ str
SELECT @ res = @ temp % 10, @ temp = @ temp/10
SET @ I = @ I + 1
END
SET @ str = SUBSTRING (@ no, @ res + 1,1) + SUBSTRING (@ unit, @ I, 1) + @ str
SET @ str = REPLACE (@ str, 'Taobao', 'Taobao ')
SET @ str = REPLACE (@ str, 'Taobao', 'Taobao ')
SET @ str = REPLACE (@ str, 'Taobao', 'Taobao ')
SET @ str = REPLACE (@ str, 'Taobao', 'Taobao ')
SET @ str = REPLACE (@ str, 'wan Wan ', 'wan ')
WHILE @ I> 0
BEGIN
SET @ str = REPLACE (@ str, 'others', 'Others ')
SET @ I = CHARINDEX ('others', @ str)
END
SET @ str = REPLACE (@ str, 'wan Wan ', 'wan ')
SET @ str = REPLACE (@ str, 'Billionaire, '')
If right (@ str, 1) = 'others'
SET @ str = LEFT (@ str, LEN (@ str)-1)
RETURN @ str
END
GO
-- Test: zero or zero
SELECT dbo. fun_cgnum (900000000), dbo. fun_cgnum (903002051), dbo. fun_cgnum (903002050)
PS: If you are interested, you can continue to consider the number of decimal points and the unit of adding (yuan/angle/points ).
