我有一个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
我不确定自己到底做错了什么...
set nocount on
。 - shawnt00end
。 - shawnt00end
就可以了。至少你离正确很近了,这是一个小安慰。 - shawnt00exec dbo.FindString 'test';
,并在最终的end
后单独一行输入GO
,以确保将其与随后添加的任何命令分开。 - shawnt00