将SQL查询转换为函数

Question

我有一个SQL查询，主要是在给定的数据库中返回字符串出现位置。

我想将其转换为SQL函数，但是我没有足够的经验（可以理解为：没有任何经验）去实现这个操作，因此遇到了些麻烦。

    /* 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

我尝试将这个语句转换成一个“create function”语句，但总是有语法错误。这是我处理到的最远的程度，执行此SQL时会给我返回“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

我不确定自己到底做错了什么...

- schizoid04

---

我认为你不能在函数内使用动态SQL。 - shawnt00

你也不能使用 set nocount on。 - shawnt00

我认为你唯一缺少的部分就是在结尾处加上一个 end。 - shawnt00

是的，我之前已经对那些进行了评论。但是关于函数语法，你只需要 end 就可以了。至少你离正确很近了，这是一个小安慰。 - shawnt00

要运行它，您需要输入 exec dbo.FindString 'test'; ，并在最终的 end 后单独一行输入 GO ，以确保将其与随后添加的任何命令分开。 - shawnt00

Answer 1

第一步语法上只需要在脚本的最后一行添加一个 end 与第一个 begin 配对。不幸的是在这一点上，您会收到有关在函数内使用 set nocount on 和 exec 的投诉。第一个问题很容易解决，但第二个问题就有些棘手了。

由于您不能在函数内使用动态 SQL，因此您的下一个最佳选择是存储过程，在这里只需要进行轻微的手术即可。第一步是将定义更改为存储过程，从以下行开始，替换所有内容直到初始 as 并包括整个 returns @results 部分。

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

最后一步是将return修改为select * from @Results。

在使用此代码片段作为长脚本的一部分时，请确保在之前和之后单独的行上加上go。 create procedure必须出现在它自己的批处理中。 使用exec dbo.FindString 'test'来尝试它。

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

创建此存储过程的SQL语句：

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

- shawnt00