SQL Server Creating User Defined Functions

Here is the basic way to create a User defined Function in SQL Server:

CREATE FUNCTION GETTRAININGSTATUS
(
@USERID VARCHAR(25),
@TRAININGID INT
)
RETURNS INT
AS
BEGIN
DECLARE @COUNT INT
DECLARE @RETURNVALUE INT
SET @COUNT = (SELECT COUNT(*) FROM TBLTRAININGS
WHERE USERID = @USERID AND TRAININGID = @TRAININGID)
IF(@COUNT = '')
SET @RETURNVALUE = 0
ELSE IF (@COUNT = 1)
SET @RETURNVALUE = 1
ELSE IF (@COUNT = 0)
SET @RETURNVALUE = 1

RETURN @RETURNVALUE
END

The function will fetch equivalent value for the element in the table. The same can be achieved with the help of cursors, by I wanted to try it with functions hence the code.

To use this function I write the following SP,

CREATE PROCEDURE SP_TRAININGSTATUS
@USERID varchar(50)
AS

SELECT TRAININGID, TRAINING, 
dbo.GETTRAININGSTATUS(@USERID, @TRAININGID) AS SKILLSTATUS
FROM TBLTRAININGS
GO

SQL Server: Searching all columns in tables in a database for a keyword?

Just found an interesting tip on this site about How to search all tables in SQL server for a particular keyword.

You can read the article on the following link:

How to Search Columns for a Keyword

Here is the code listed on the site:

CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results
END