CODEWITHSUNDEEP
sqlsql-serverfunction
schizoid04
schizoid04

Reputation: 934

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...

Upvotes: 3

Views: 920

Answers (1)

shawnt00
shawnt00

Reputation: 17943

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

The SQL to create this stored procedure: 

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

AS
BEGIN

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

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


END

Upvotes: 2

Related Questions