Page pagination:
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:
Copy Code code as follows:
/*
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. Add descriptive information to the fields in the table
Copy Code code as follows:
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
Copy Code code as follows:
EXEC sp_renamedb ' old_db_name ', ' new_db_name '
3. Modify the data table name and field name
Copy Code code as follows:
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. Get stored procedure contents given the stored procedure name
Copy Code code as follows:
The following is about database control
* The following is a system stored procedure or SQL statement about security control, detailed syntax to refer to "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 restore
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 activities in the incoming database for exclusive operations such as backup restores
=================== 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
