================= Paging ==========================
Copy Code code as follows:
/* Pagination Lookup Data * *
CREATE PROCEDURE [dbo]. [GetRecordSet]
@strSql varchar (8000),--query SQL, such as SELECT * from [user]
@PageIndex int,--query as page number
@PageSize int--Display records per 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, @scrollopt =1, @ccopt =1, @rowcount = @rowCount output-Total number of records
Select @PageCount =ceiling (1.0* @rowCount/@pagesize)--Get total pages
, @currentPage = (@PageIndex-1) * @PageSize +1
Select @RowCount, @PageCount
exec sp_cursorfetch @p1, @currentPage, @PageSize
EXEC sp_cursorclose @p1
SET NOCOUNT OFF
Go
========================= User Registration ============================
/*
User registration, is also added to the bar
*/
Create proc [dbo]. [Useradd]
(
@loginID nvarchar (50),--Login account
@password nvarchar (50),--Password
@email nvarchar (200)--Email
)
As
DECLARE @userID INT--User ID
--Login account has been registered
If exists (select loginID from tablename where loginID = @loginID)
Begin
return-1;
End
--The mailbox 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. To add descriptive information to a field in a 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 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 '-modifying datasheet name
EXEC sp_rename ' table_name. [Old_column_name] ', ' new_column_name ', ' column '-Modify field name
–4. Gets the contents of the stored procedure by a given stored procedure name
EXEC sp_helptext Sp_name
/* The following is a system stored procedure or SQL statement about security control, detailed syntax to read books Online related content * *
– Create a new SQL Server login that enables users to connect to SQL Server that uses SQL Server authentication.
EXEC sp_addlogin @loginame = ', @passwd = ', @defdb = ', @deflanguage = null, @sid = NULL, @encryptopt = NULL
– Enable Windows NT User or group accounts to connect to SQL Server using Windows authentication.
EXEC sp_grantlogin @loginame = '
– Remove the SQL Server login to prevent access to SQL Server using the login name.
EXEC sp_droplogin @loginame = '
– Prevent Windows NT users or groups from connecting to SQL Server.
EXEC sp_denylogin @loginame = '
– Deletes a login entry for a Windows NT user or group created with sp_grantlogin or sp_denylogin from SQL Server.
EXEC sp_revokelogin @loginame = '
– Change the default database for logins.
EXEC sp_defaultdb @loginame = ', @defdb = '
– Change the default language for logins.
EXEC sp_defaultlanguage @loginame = ', @language = '
– Add or change the SQL Server login password.
EXEC sp_password @old = ', @new = ', @loginame = '
– Add new members of the server role.
EXEC sp_addsrvrolemember @loginame = ', @rolename = '
– Add a member of the server role.
EXEC sp_dropsrvrolemember @loginame = ', @rolename = '
– Add a security account to the current database for SQL Server logins or Windows NT users or groups, and enable them to be granted permission to perform activities in the database (granted the default "public" Database role).
EXEC sp_grantdbaccess @loginame = ', @name_in_db = NULL
– or
EXEC sp_adduser @loginame = ', @name_in_db = NULL, @grpname = '
– Remove the 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 = '
– Deletes a database role in the current database.
EXEC sp_droprole @rolename = '
– Adds a new member of the database role to the current database.
EXEC sp_addrolemember @rolename = ', @membername = '
– Deletes a member of a database role in the current database.
EXEC sp_droprolemember @rolename = ', @membername = '
– Permissions are assigned to objects such as database roles, tables, stored procedures, and so on
–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 procedures for database restores ============
Copy Code code as follows:
SQL Code
Create proc killspid (@dbname varchar (20))
As
Begin
declare @sql nvarchar (500)
DECLARE @spid int
Set @sql = ' Declare getspid cursor for
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
Role: Kill the active process in the incoming database for exclusive operations such as a backup restore
=================== Arabic numeral to Capital Chinese =============
Example: Input 12345, the program gives: one million thousand thousands of restaurants to collect Wu
Example: Enter 10023040, the program gives: one thousand Wan another to pick up
One of the solutions (tested in SqlServer2000):
Copy Code code 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 (m), @no VARCHAR (a), @unit VARCHAR (16)
SELECT @str = ', @no = ' Another three Woolu qi Ba nine ', @unit = ' Collect bai thousand million '
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, ' pick up ', ' another ')
SET @str =replace (@str, ' Bai ', ' another ')
SET @str =replace (@str, ' another ', ' another ')
SET @str =replace (@str, ' pick up ', ' another ')
SET @str =replace (@str, ' Another million ', ' million ')
While @i>0
BEGIN
SET @str =replace (@str, ' another ', ' another ')
SET @i=charindex (' other ', @str)
End
SET @str =replace (@str, ' Another million ', ' million ')
SET @str =replace (@str, ' billions ', ' billion ')
IF Right (@str, 1) = ' another '
SET @str =left (@str, LEN (@str)-1)
Return @str
End
Go
--Test: There are 0 and no 0 cases
SELECT Dbo.fun_cgnum (900000000), Dbo.fun_cgnum (903002051), Dbo.fun_cgnum (903002050)
PS: Interested friends can continue to consider the decimal point and add units (Yuan/PT/min) situation
