This page lists some useful scripts, functions and stored procedures in SQL Server
|
Table of Contents
|
SQL Server
This script shows version of SQL Server that is running
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
Scripts
This script declares a table variable and then inserts data into it
USE DatabaseName
GO
DECLARE @MyTable Table
(
fieldname1 datatype
fieldname2 datatype
)
INSERT INTO @MyTable
SELECT one.fieldname1, one.fieldname2 FROM Table1 one
WHERE one.fieldname3 = 'VALUE'
This script executes dynamic SQL
USE DatabaseName
GO
DECLARE @TableName varchar(128)
SELECT @TableName = (SELECT fieldname1 FROM table1 WHERE fieldid = 'VALUE')
EXEC ('SELECT * FROM ' + @TableName)
Control of Flow Statements
IF —— ELSE
IF @inputparameter > 'Value'
BEGIN
PRINT "Invalid parameter"
PRINT "Please enter a value less than 10"
END
ELSE
BEGIN
PRINT "Valid Parameter"
INSERT INTO table1 VALUES (@inputparameter)
END
WHILE
WHILE @parameter < 'VALUE'
BEGIN
PRINT "Parameter is less than 10"
SET @parameter = @parameter + 1
END
WAITFOR
WAITFOR DELAY '01:00' Waits for 1 hour - max = 24 hours
WAITFOR TIME '01:00' Waits until time ie. 1am
ERROR
RAISEERROR
This raises an error message with number, default = 5000
RAISERROR ('Error message', severity, state) WITH LOG / SETERROR / WITH NOWAIT
Severity
1-9: informational only, return specific error code to client
10: informational only, will not return specific error code to client
11-16: terminates procedure and returns specific error code to client
17: SQL server ran out of resources
18-19: severe errors, with log required
20-25: fatal errors, with log required
SETERROR: Sets @@ERROR to error value raised
TRY - CATCH
This raise an error using try and catch
USE DatabaseName
GO
CREATE PROC SpName
@inputparameter1 Datatype
AS
BEGIN
BEGIN TRY
INSERT INTO table1 VALUES (@inputparameter1)
END TRY
BEGIN CATCH
RAISERROR ('Error message', 11, 1)
END CATCH
END
sp_addmessage
This system function adds message to sysmessages table in master database
sp_addmessage
@msgnum = 60000,
@severity = 10,
@msgtext = "Error message"
@with_log = True
RAISERROR (60000,1,1) WITH SETERROR
RETURN @@ERROR
This system function drops message
sp_dropmessage 'MESSAGENUMBER'
System Functions
Identity
This function returns last identity value for table1
IDENT_CURRENT(table1) {SCOPE_IDENTITY(table1)}
This functions returns the increment value for table1
IDENT_INCR(table1)
This function returns the seed value for table1
INDENT_SEED(table1)
This function inserts an identity column into table1
SELECT IDENTITY (datatype, seed, increment) AS FieldID, *
into newtable1 one
from oldtable2 two
where two.fieldname = 'Value'
This function returns a new uniqueidentifier value
NEWID()
@@IDENTITY
This script inserts row in table1 and corresponding row in table2
USE DatabaseName
GO
DECLARE @variableName int
INSERT INTO table1
(fieldname1,fieldname2)
VALUE
('Value1', 'Value2')
SET variableName = @@IDENTITY
INSERT INTO table2
(fieldId, fieldname1, fieldname2)
VALUES
(variableName, 'VALUE1', 'VALUE2')
@@ROWCOUNT
This script counts the number of rows returned from table1
USE DatabaseName
GO
DECLARE @RowCount int
SELECT * FROM table1
SET @RowCount = @@ROWCOUNT
PRINT 'Number of rows in table1 = ' + CAST(@RowCount AS VarChar (5))
@@ERROR
This script checks for error in stored procedure and returns error number
USE DatabaseName
GO
CREATE PROC SpName
@inputparameter1 Datatype
AS
DECLARE @Error int
INSERT INTO table1 VALUES (@inputparameter1)
SELECT @Error = @@ERROR
IF @Error != 0
BEGIN
PRINT "Error Message"
RETURN @Error
END
RETURN
Stored Procedures
Unnested
This stored procedure inputs a row into a table and then selects that row using new identity value
USE DatabaseName
GO
CREATE PROC SpName
@inputparameter1 Datatype,
@inputparameter2 Datatype = 'Value',
@inputparameter3 Datatype = NULL,
@outputparameter Datatype OUTPUT
WITH RECOMPILE Forces stored procedure to recompile everytime it is run
AS
DECLARE @sprocvariable Datatype
/* Comment here */
INSERT INTO table1
VALUES
(@inputparameter1, @inputparameter2, @inputparameter3)
SELECT @outputparameter = @@IDENTITY
USE DatabaseName
GO
DECLARE @myIndent Datatype
EXEC SpName
@inputparameter1 = 'Value',
@inputparameter2 = 'Value',
@outputparameter = @myIndent OUTPUT
WITH RECOMPILE Forces stored procedure to recompile
SELECT @myIndent As IdentityValue
SELECT fieldname1, fieldname2, fieldname3 FROM table1
WHERE fieldid = @myIndent
This stored procedure uses a return value to indicate its success
USE DatabaseName
GO
CREATE PROC SpName
AS
/* Comment here */
RETURN 'Value'
USE DatabaseName
GO
DECLARE @Return int
EXEC @Return = spName
SELECT @Return
Nested
This stored procedure use nested stored procedures
USE DatabaseName
GO
CREATE PROC SpName
AS
DECLARE @Return int
EXEC @Return = spName2
If @Return = 0
INSERT INTO table1 VALUES ('Value1')
ELSE
INSERT INTO table1 VALUES ('Value2')
Recursive
This stored procedure uses recursion to calculate factorials
CREATE PROC spFactorial
@ValueIn int,
@ValueOut int OUTPUT
AS
DECLARE @InWorking int
DECLARE @OutWorking int
IF @ValueIn !=1
BEGIN
SELECT @InWorking = @ValueIn - 1
EXEC spFactorial @InWorking, @OutWorking OUTPUT
SELECT @ValueOut = @ValueIn * @OutWorking
END
ELSE
BEGIN
SELECT @ValueOut = 1
END
RETURN
GO
DECLARE @WorkingOut int
DECLARE @WorkingIn int
SELECT @WorkingIn = 5
EXEC spFactorial @WorkingIn, @WorkingOut OUTPUT
PRINT CAST(@WorkingIn AS varchar) + ' factorial is ' + CAST(@WorkingOut As varchar)
Drop stored procedure
DROP PROC spName
System Stored Procedures
sp_helpdb 'DATABASENAME' : returns information on database and log
sp_help 'OBJECTNAME' : re turns the properties of named object
sp_helptext OBJECTNAME : returns information on named object
sp_helpconstraint TABLENAME : returns information on constraints in table
sp_depends OBJECTNAME : lists all objects that depend on named object
User Defined Functions
Return Scalar Value
This function returns the sum of a field grouped depending on parameter
CREATE FUNCTION schema.FunctionName (@Parameter DateType)
RETURNS DateType
WITH SCHEMABINDING
AS
BEGIN
RETURN (SELECT SUM (fieldname1) FROM table1 WHERE fieldid = @Parameter)
END
GO
SELECT fieldname1, schema.FunctionName (one.fieldid) AS TOTAL FROM table1 one
Return table
This paramatized function returns a table
CREATE FUNCTION schema.FunctionName (@InputParameter DataType)
RETURNS TABLE
AS
RETURN (SELECT one.fieldname1, two.fieldname2 FROM table1 one
INNER JOIN table2 two ON one.fieldid = two.fieldid where one.fieldname1 = @InputParameter)
GO
SELECT * FROM schema.FunctionName ('Value')
This recursive functions return a hierarchy
CREATE FUNCTION schema.FunctionName (@childID AS int)
RETURN @HierarchyTable TABLE
(
ChildID int NON NULL,
ParentID int NULL
)
AS
BEGIN
DECLARE @child AS int
INSERT INTO @HierarchyTable
SELECT childID, ParentID FROM table1 WHERE childID = @childID
SELECT @child = MIN (childID) FROM table1 WHERE ParentID = @ChildID
WHILE @child IS NOT NULL
BEGIN
INSERT INTO @HierarchyTable
SELECT * FROM schema.FunctionName (@child)
SELECT @child = MIN(childID) FROM table1
WHERE ChildID >@child AND parentID = @ChildID
END
RETURN
END
GO
SELECT * FROM schema.FunctionName (n)
Triggers
Insert
This insert trigger checks whether linked table field contains specific value
CREATE TRIGGER TriggerName
ON Table1
WITH ENCRYPTION
FOR INSERT
AS
IF EXISTS
(SELECT 'True' FROM Inserted i
INNER JOIN Table2 two ON two.fieldid = i.fieldid
WHERE two.fieldname = 'Value')
BEGIN
RAISEERROR ('ErrorMessage',1,1)
ROLLBACK TRAN
END
Update
This update trigger checks if updated value is greater than original value
CREATE TRIGGER TriggerName
ON Table1
WITH ENCRYPTION
FOR UPDATE INSTEAD OF - trigger ran before Inserted and Deleted tables created
AS
IF UPDATE (fieldname1) COLUMNS_UPDATED checks multiple columns by using bit mask
BEGIN
IF EXISTS
(SELECT 'True' FROM inserted i
INNER JOIN Deleted d ON d.fieldid = i.fieldid
WHERE d.fieldname1 > i.fieldname1)
BEGIN
INSERT INTO Table1
(fieldname2)
VALUES
('Increase')
END
Delete
This delete trigger updates a summary table when a record is deleted
CREATE TRIGGER TriggerName
ON Table1
WITH ENCRYPTION
FOR DELETE
AS
BEGIN
UPDATE Table2
SET fieldname2 = (SELECT COUNT(*) FROM table1)
WHERE fieldname1 = 'TableName1'
END
This disables or enables trigger
ALTER TABLE Table1
ENABLE|DISABLE TRIGGER TriggerName
This drops trigger
DROP TRIGGER TriggerName
Cursors
Read
DECLARE @cursorvariable1 AS DataType, @cursorvariable2 AS DataType
DECLARE CursorName CURSOR
{GLOBAL|LOCAL}
{FORWARD_ONLY|SCROLL}
{STATIC|KEYSET|DYNAMIC|FAST_FORWARD}
READ_ONLY
{TYPE_WARNING}
FOR SELECT fieldname1, fieldname2 FROM table1
OPEN CursorName
FETCH NEXT FROM CursorName INTO @cursorvariable1, @cursorvariable2
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @cursorvaribable1 + ' ' + @cursorvariable2
FETCH {NEXT|PRIOR|FIRST|LAST|ABOSULTE n|RELATIVE n} FROM CursorName INTO @cursorvariable1, @cursorvariable2
END
CLOSE CursorName
DEALLOCATE CursorName
Write
KeySet cursor uses an clustered index field to retrieve the data
SELECT one.fieldid, one.fieldname1 INTO CursorTable from table1 one
ALTER TABLE CursorTable
ADD CONSTRAINT PKCursor
PRIMARY KEY (fieldid)
SET IDENTITY_INSERT CursorTable ON
DECLARE CursorName CURSOR
{GLOBAL|LOCAL}
{FORWARD_ONLY|SCROLL}
{KEYSET|DYNAMIC}
{SCROLL_LOCKS|OPTOMISTIC}
{TYPE_WARNING}
FOR SELECT fieldid, fieldname1 FROM CursorTable
DECLARE @cursorvariable1 AS DataType, @cursorvariable2 AS DataType
OPEN CursorName
FETCH NEXT FROM CursorName INTO @cursorvariable1, @cursorvariable2
WHILE @@FETCH_STATUS != -1
BEGIN
IF @@FETCH_STATUS = -2
BEGIN
PRINT 'This row has been deleted'
END
ELSE
BEGIN
UPDATE CursorTable SET fieldname1 = 'VALUE' WHERE CURRENT OF CursorName
DELETE CursorTable WHERE CURRENT OF CursorTable
END
FETCH {NEXT|PRIOR|FIRST|LAST|ABOSULTE n|RELATIVE n} FROM CursorName INTO @cursorvariable1, @cursorvariable2
END
CLOSE CursorName
DEALLOCATE CursorName
DROP TABLE CursorTable
Examples
This cursor loops through tables in database
DECLARE @TableName varchar (255)
DECLARE TableCursor CURSOR FOR
SELECT Table_Name FROM Information_Schema.Tables
WHERE Table_Type = 'BASE_TABLE'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
Run SQL command with @TableName as parameter
END
CLOSE TableCursor
DEALLOCATE TableCursor
Full-Text Search
Enable Full-Text Search on Database
USE DatabaseName
EXEC sp_fulltext_database @action={'Enable'|'Disable'}
Create a Full-Text Catalog
USE DatabaseName
EXEC sp_fulltext_catalog @ftcat='CatalogName' @action={'Create'|'Drop'|'Stop'|'Rebuild'}
@path='OptionPathName' //Default=..\MSSQL\FTDATA
Enable Full-Text Search on Table
USE DatabaseName
EXEC sp_fulltext_table @tabname='TableName', @action='Create', @ftcat='CatalogName', @keyname='ClusteredIndexName'
Enable Full-Text Search on Column
USE DatabaseName
EXEC sp_fulltext_column @tabname='TableName', @colname='FieldName', @action='add'
Populate Full-Text Index
USE DatabaseName
EXEC sp_fulltext_table @tabname='TableName', @action={'start_full'|'start_incremental'}
Contains
SELECT one.fieldname1 from table1 one
WHERE CONTAINS (*, 'TEXT')
ContainsTable
SELECT Rank, one.fieldname1
FROM CONTAINSTABLE (Table1, *, 'TEXT') AS ct
INNER JOIN Table1 one ON ct.[KEY] = one.ClusteredIndexField
FreeText
SELECT one.fieldname1 from table1 one
WHERE FREETEXT (*, 'TEXT')
FreeTextTable
SELECT Rank, one.fieldname1
FROM FREETEXTTABLE (Table1, *, 'TEXT') AS ct
INNER JOIN Table1 one ON ct.[KEY] = one.ClusteredIndexField
… Phrase: '"Number of words"'
… Prefix : 'TEXT*'
… Proximity: 'TEXT Near TEXT'
… Weighting: 'ISABOUT(TEXT1 WEIGHT(0.2), TEXT2 WEIGHT(0.4), TEXT3 WEIGHT(0.8)'
… Inflection: 'FORMSOF(INFLECTIONAL, TEXT1, TEXT2)'
… Noise words path: ProgramFiles\Microsoft SQL Server\MSSQL\FTDATA\SQL Server\Config
