执行时sql server存储过程出错: [英] Error in sql server stored procedure while executing:
本文介绍了执行时sql server存储过程出错:的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
USE [ProductReview]
GO
/****** Object: StoredProcedure [dbo].[FindStringInTable] Script Date: 02/06/2014 14:38:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[FindStringInTable] @stringToFind VARCHAR(100), @schema sysname, @table sysname
AS
DECLARE @sqlCommand VARCHAR(8000)
DECLARE @where VARCHAR(8000)
DECLARE @columnName sysname
DECLARE @cursor VARCHAR(8000)
BEGIN TRY
SET @sqlCommand = 'SELECT * FROM [' + @schema + '].[' + @table + '] WHERE'
SET @where = ''
SET @cursor = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME
FROM ' + DB_NAME() + '.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ''' + @schema + '''
AND TABLE_NAME = ''' + @table + '''
AND DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'')'
EXEC (@cursor)
OPEN col_cursor
FETCH NEXT FROM col_cursor INTO @columnName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @where <> ''
SET @where = @where + ' OR'
SET @where = @where + ' [' + @columnName + '] LIKE ''' + @stringToFind + ''''
FETCH NEXT FROM col_cursor INTO @columnName
END
CLOSE col_cursor
DEALLOCATE col_cursor
SET @sqlCommand = @sqlCommand + @where
--PRINT @sqlCommand
EXEC (@sqlCommand)
END TRY
BEGIN CATCH
PRINT 'There was an error. Check to make sure object exists.'
IF CURSOR_STATUS('variable', 'col_cursor') <> -3
BEGIN
CLOSE col_cursor
DEALLOCATE col_cursor
END
END CATCH
----------------------------------- --------------------------------
当我执行时
:
EXECUTE [ProductReview]。[dbo]。[FindStringInTable]'Iph%','ProductReview','Products'
---------------- -------------------------------------------------- -
错误:出错了。检查以确保对象存在。
-------------------------------------------------------------------
when i execute:
EXECUTE [ProductReview].[dbo].[FindStringInTable] 'Iph%','ProductReview','Products'
-------------------------------------------------------------------
Error:There was an error. Check to make sure object exists.
推荐答案
没有人可以告诉你错误是什么,因为你正在吞咽它,并在它的位置提供无用的错误信息。
至少,让你的CATCH执行此操作:
Nobody can tell from that what the error is because you are swallowing it, and providing a "useless" error message in it's place.
As a minimum, make your CATCH do this:
BEGIN CATCH
PRINT 'There was an error: ' + ERROR_MESSAGE()
然后你至少会得到一个问题的线索!
And then you will at least be given a clue as to what the problem might be!
这篇关于执行时sql server存储过程出错:的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文