Doug Robinson Doug Robinson - 3 months ago 19
SQL Question

Turn SQL Query Into Function

I have a SQL query that, for the most part, will return the location of a string, wherever it occurs in a given database.

I'd like to turn this into a function in SQL, but I don't have enough experience (read: Any experience) with functions and am having trouble doing so.

/* Set @SearchStr to a string you are looking for and all text columns of a DB
will be searched to find that string */

DECLARE @sql nvarchar(4000)
DECLARE @SearchStr nvarchar(100)
SET @SearchStr = 'TYPE What you are looking for here'

DECLARE @Results TABLE (
TableName nvarchar(256)
, 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
SET @sql = 'SELECT TableName = ''' + @TableName + ''', ''' + @TableName + '.' + @ColumnName
+ ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' + 'WHERE ' + @ColumnName
+ ' LIKE ' + @SearchStr2
--PRINT @sql
INSERT INTO @Results
EXECUTE (@sql
)
END
END
END

SELECT * FROM @Results


I've attempted to turn this into a 'create function' statement, but I always get a syntax error somewhere.

This is the farthest i've gotten with this, and my execution of this SQL will give me the error message "Invalid syntax near 'Return'" :

create function dbo.FindString( @SearchStr Varchar(256) )

RETURNS
@Results TABLE (
TableName nvarchar(256)
, ColumnName nvarchar(370)
, ColumnValue nvarchar(3630)
)
AS
BEGIN

DECLARE @sql nvarchar(4000)
--DECLARE @SearchStr nvarchar(100)
SET @SearchStr = ''


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
SET @sql = 'SELECT TableName = ''' + @TableName + ''', ''' + @TableName + '.' + @ColumnName
+ ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' + 'WHERE ' + @ColumnName
+ ' LIKE ' + @SearchStr2
--PRINT @sql
INSERT INTO @Results
EXECUTE (@sql
)
END
END

END

Return


I'm not sure exactly what I'm doing wrong...

Answer

The first step syntactically is just to add an end as the final line of the script to pair up with the first begin. Unfortunately at that point you'll get complaints about using set nocount on and exec inside a function. The first of those is no sweat but the second one is a problem.

Since you can't use dynamic sql inside functions your next best option is a stored procedure and only minor surgery is required here. Step one is to change the definition to a procedure, starting off with the line below replacing everything up to the initial as and including the entire returns @results section.

create procedure dbo.FindString @SearchStr Varchar(256) as

The last tweak is to change return to a select * from @Results.

Make sure to add go on separate lines before and after when using this as part of a longer script. create procedure must appear as it's own separate batch. Use exec dbo.FindString 'test' to try it out.

https://msdn.microsoft.com/en-us/library/ms187926.aspx