Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 658
  • Last Modified:

Why can't SQL Server 2008 find a stored procedure that exists?

I have a stored procedure that returns an error message:

     "Msg 2812, Level 16, State 62, Procedure usp_SearchResults, Line 22
      Could not find stored procedure. ''

The procedure exists in the correct database.  As far as I can tell the problem seems to be on the line:

     EXEC @strSQL

If I change it to :

     EXEC (@strSQL)

Then an empty recordset is returned.

Please help.
USE [Data]
GO
/****** Object:  StoredProcedure [dbo].[usp_SearchResults]    Script Date: 08/28/2010 20:16:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: SQLQuery19.sql|7|0|C:\Users\Administrator\AppData\Local\Temp\1\~vsD524.sql
-- =============================================
-- Author:		Rob
-- Create date: 8/24/2010
-- Description:	Returns results of PN search.
-- =============================================
ALTER PROCEDURE [dbo].[usp_SearchResults] 
	-- Add the parameters for the stored procedure here
	@WHEREStatement nvarchar(1000) = NULL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @strSQL nvarchar(1000)
    
	SET @strSQL = 'SELECT Item_NUMBER, DESCRIPTION, ProdLine, DOCUMENT_TYPE 
				   FROM tblItem ' + @WhereStatement 
			
	EXEC @strSQL
END

Open in new window

0
NevSoFly
Asked:
NevSoFly
  • 9
  • 4
  • 2
  • +3
3 Solutions
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Since it is SQL Server 2008, I would recommend using

exec sp_executesql @strSQL
instead of EXEC @strSQL

Make sure that
1. You are connected to the correct database in SSMS
2. Try using CREATE PROCEDURE [dbo].[usp_SearchResults]
instead of ALTER PROCEDURE [dbo].[usp_SearchResults]
0
 
NevSoFlyAuthor Commented:
I did as you suggested but got the below error in return.

     "Msg 217, Level 16, State 1, Procedure usp_SearchResults, Line 21
      Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)."
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Are you sure that your Stored procedure code is exactly the one you posted above..
For SELECT statements you should not receive the error posted above..

And do you have any triggers on the table tblItem
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
NevSoFlyAuthor Commented:
Here is the exact code I have for the SP and the trigger that is on tblItem


SP code:

USE [Data]
GO
/****** Object:  StoredProcedure [dbo].[usp_SearchResults]    Script Date: 08/28/2010 21:32:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Robert Nevins
-- Create date: 8/24/2010
-- Description:	Returns results of PN search.
-- =============================================
ALTER PROCEDURE [dbo].[usp_SearchResults] 
	-- Add the parameters for the stored procedure here
	@WHEREStatement nvarchar(1000) = NULL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @strSQL nvarchar(1000)
    
	SET @strSQL = 'SELECT Item_NUMBER, DESCRIPTION, ProdLine, DOCUMENT_TYPE 
				   FROM tblItem ' + @WhereStatement 
			
	EXEC usp_SearchResults @strSQL
END


trigger on tblItem:

USE [Data]
GO
/****** Object:  Trigger [dbo].[tr_tblItem_Update]    Script Date: 08/29/2010 03:26:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Robert Nevins
-- Create date: 8/24/2010
-- Description:	updates the DateStamp field to the current date whenever a record is added or any data in a record is modified.
-- =============================================
ALTER TRIGGER [dbo].[tr_tblItem_Update] 
   ON  [dbo].[tblItem] 
   AFTER INSERT,UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
    UPDATE tblItem SET DateStamp = getdate() 
    FROM tblItem, inserted i 
    WHERE tblItem.Item_Number = i.Item_Number
   /*AND x.othercolums = i.othercolumns*/
   
   UPDATE tblSynched SET tblsynched.tblItem = GETDATE()
   FROM tblSynched
END

Open in new window

0
 
JensMigCommented:
You recall your own stored procedure indefinately.
Line 30: EXEC usp_SearchResults @strSQL


0
 
NevSoFlyAuthor Commented:
thanks, I fixed that by removing  usp_SearchResults so it now reads 'EXEC @strSQL' but I am back to my original problem.
0
 
NevSoFlyAuthor Commented:
Maybe we should start from the begining.

What I need is a stored procedure that will allow me to have a dynamic WHERE clause.
0
 
marat-ozCommented:
Let's try Debugging
add print after
SET @strSQL = 'SELECT Item_NUMBER, DESCRIPTION, ProdLine, DOCUMENT_TYPE
                           FROM tblItem ' + @WhereStatement
as follows
print @strSQL
What do you get?

I guess you have CONCAT_NULL_YIELDS_NULL  ON

Try to add the following code into your procedure, at the top
SET CONCAT_NULL_YIELDS_NULL OFF

or
set default value to empty string (which I prefer)

@WHEREStatement nvarchar(1000) = ''


0
 
cyberkiwiCommented:
Dynamic SQL is run using

EXEC (@strSQL)

Brackets are NOT optional.
0
 
cyberkiwiCommented:
Now that that is sorted, the other problem is that the default for @WHERESTATEMENT is NULL.
When you add NULL to a string, the entire string becomes null, unless non-standard SET settings are used.

Try this

------

ALTER PROCEDURE [dbo].[usp_SearchResults]
      -- Add the parameters for the stored procedure here
      @WHEREStatement nvarchar(1000) = NULL
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      DECLARE @strSQL nvarchar(1000)
   
      SET @strSQL = 'SELECT Item_NUMBER, DESCRIPTION, ProdLine, DOCUMENT_TYPE
                           FROM tblItem ' + IsNull(@WhereStatement , '')
                  
      EXEC (@strSQL)
END
0
 
wolfman007Commented:
I think the problem could be that you have called your database [Data], which is listed as a future key word in the following website.

Reserved Keywords (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms189822.aspx

You should try and avoid using key words when naming your databases of tables.

Temporarily rename your database to see if it fixes the problem.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Few comments for the code attached in comment: 33552509

Replace EXEC usp_SearchResults @strSQL
with EXEC sp_executesql @strSQL

And I don't see any practical usage of the trigger from the application point of view. Its adding addtional overhead to your table.
1. You can achieve this operation
    UPDATE tblItem SET DateStamp = getdate()
    FROM tblItem, inserted i
    WHERE tblItem.Item_Number = i.Item_Number
by using a default constraint with value as getdate() for Datestamp column

2. May I know how many records this UPDATE would be modifying

   UPDATE tblSynched SET tblsynched.tblItem = GETDATE()
   FROM tblSynched

second one can impact your query performance and as I mentioned above, the actual reason for failure may be some other.
And kindly confirm whether tblItem is a table or view.
0
 
NevSoFlyAuthor Commented:
marat-0z,

This is the result:
   SELECT Item_NUMBER, DESCRIPTION, ProdLine, DOCUMENT_TYPE
   FROM tblItem WHERE item_Number=100000-1

This gives me the same error.

When I set @WHEREStatement = "" it tells me that aliases defined as "" or [] are not allowed.
When I tried 'SET CONCAT_NULL_YIELDS_NULL OFF' I got"
   'Msg 2812, Level 16, State 62, Procedure usp_SearchResults, Line 21
Could not find stored procedure 'SELECT Item_NUMBER, DESCRIPTION, ProdLine, DOCUMENT_TYPE
                   FROM tblItem WHERE item_number = 100000-1'.'
0
 
NevSoFlyAuthor Commented:
cyberkiwi,

I tried the EXEC (@strSQL) before and it didn't work.  I don't know what was different this time but I got a different error 'can't make conversion from nvarchar to int' (or something simular).  I changed the code from WHERE item_number = 100000-1 to WHERE item_number = '100000-1' and it worked.

Also combining it with the ISNULL change got me the desired results incase I didn't have a WHERE clause.

Thank you.
0
 
NevSoFlyAuthor Commented:
wolfman007,

that didn't solve my problem but your right, I should do it thanks.
0
 
NevSoFlyAuthor Commented:
rrjegan17,

I tested your suggestion yet but to answer some of your questions.

1.  The trigger has nothing to do with this query.  I was asked if there was a trigger on the table so I supplied
     the code.  The trigger was ment only to keep track of when the table was updated and when a specific
     record on that table was updated or inserted.

     Will using a default constraint with GETDATE() as the value work for updating a record as well as
     inserting a new record?

2.  It only updates one record and tblItem is a table.  
0
 
marat-ozCommented:
Hi NevSoFly,
I created your SP and tested it, it runs fine, returning records when it supose to.
Please analyse and run below code; let me know of results
ALTER PROCEDURE [dbo].[usp_SearchResults]
      -- Add the parameters for the stored procedure here
      @WHEREStatement nvarchar(1000) = ''
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      DECLARE @strSQL nvarchar(1000)
   
      SET @strSQL = 'SELECT Item_NUMBER, DESCRIPTION, ProdLine, DOCUMENT_TYPE
                           FROM tblItem ' + @WhereStatement
                  
      EXEC (@strSQL)
END

GO

exec [dbo].[usp_SearchResults] 'WHERE item_Number=''100000-1'''
exec [dbo].[usp_SearchResults] 'WHERE item_Number=''Hello'''
exec [dbo].[usp_SearchResults] ''
exec [dbo].[usp_SearchResults]
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> Will using a default constraint with GETDATE() as the value work for updating a record as well as
     inserting a new record?

I assume, updates can happen in the table only via application or through some stored procedures. In that case, you can include this column in UPDATE clause to avoid the need of a trigger.
0
 
NevSoFlyAuthor Commented:
Thank you all for your assistance.  I think what really confused me on this problem was that the error message didn't seem to match the actual error.  Agian thank you all.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 4
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now