执行时sql server存储过程出错： [英] Error in sql server stored procedure while executing:

问题描述

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屋！