悠悠博客 提供视频转换器,3gp格式转换器免费下载

This posting will show you some script tips about 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'