SQL Script tips for MS SQL Server
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 completing the WAITFOR statement. The time to wait before completing the WAITFOR statement 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 completion 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 Transact-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 character 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 execute a string. Not only does the support for parameter substitution make sp_executesql more versatile than EXECUTE, it also makes sp_executesql more efficient 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 Transact-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, user-defined function, trigger, or view.
-- This example displays the text of the employee_insupd trigger, which is in the pubs database
Use Pubs
Exec sp_helptext 'employee_insupd'