SQL Script tips for MS SQL Server
1. Waitfor
The WAITFOR statement is specified with one of two clauses:
(1) The DELAY keyword followed by an amount of time to pass before completin
g the WAITFOR statement. The time to wait before completing the WAITFOR stat
ement can be up to 24 hours. For example,
-- Wait for ten secondes before perforing a select statement
WAITFOR DELAY '00:00:10'
Select EmployeeID From Northwind.dbo.Employees
(2) The TIME keyword followed by a time to execute, which specifies completi
on of the WAITFOR statement.
For example,
-- Wait until 10:00 PM to perform a check of the pubs database to make sure
that all pages are correctly allocalted and used.
Use pubs
BEGIN
WAITFOR TIME '22:00'
DBCC CHECKALLOC
END
2. Enable SQL Debugging
-- The SP_SDIDEBUG stored procedure is used by SQL Server for debugging Tran
sact-SQL statements
Use master
Grant Execute on SP_SDIDEBUG to Username
3. Execute a dynamically built string
(1) EXECUTE statement
With the EXECUTE statement, all parameter values must be converted to charac
ter or Unicode and made a part of Transact-SQL string. For example,
DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR(500)
/* Build and execute a string with one parameter value. */
SET @IntVariable = 35
SET @SQLString = N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = ' +
CAST(@IntVariable AS NVARCHAR(10))
EXEC(@SQLString)
/* Build and execute a string with a second parameter value. */
SET @IntVariable = 201
SET @SQLString = N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = ' +
CAST(@IntVariable AS NVARCHAR(10))
EXEC(@SQLString)
(2) SP_ExecuteSQL
Using sp_executesql is recommended over using the EXECUTE statement to execu
te a string. Not only does the support for parameter substitution make sp_ex
ecutesql more versatile than EXECUTE, it also makes sp_executesql more effic
ient because it generates execution plans that are more likely to be reused
by SQL Server.
sp_executesql supports the setting of parameter values separately from the T
ransact-SQL string:
DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
/* Build the SQL string once. */
SET @SQLString =
N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = @level'
/* Specify the parameter format once. */
SET @ParmDefinition = N'@level tinyint'
/* Execute the string with the first parameter value. */
SET @IntVariable = 35
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@level = @IntVariable
/* Execute the same string with the second parameter value. */
SET @IntVariable = 32
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@level = @IntVariable
4. SP_HelpText
Prints the text of a rule, a default, or an unencrypted stored procedure, us
er-defined function, trigger, or view.
-- This example displays the text of the employee_insupd trigger, which is i
n the pubs database
Use Pubs
Exec sp_helptext 'employee_insupd'
from:asp学习网/title:SQL Script tips for MS SQL Server/ time:2007-8-14 17:26:40
本文主题SQL Script
