?
Solved

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

Posted on 2010-08-28
19
Medium Priority
?
648 Views
Last Modified: 2012-05-10
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
Comment
Question by:NevSoFly
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 4
  • 2
  • +3
19 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33551633
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
 

Author Comment

by:NevSoFly
ID: 33551694
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33551891
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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

Author Comment

by:NevSoFly
ID: 33552509
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
 
LVL 1

Expert Comment

by:JensMig
ID: 33553924
You recall your own stored procedure indefinately.
Line 30: EXEC usp_SearchResults @strSQL


0
 

Author Comment

by:NevSoFly
ID: 33554096
thanks, I fixed that by removing  usp_SearchResults so it now reads 'EXEC @strSQL' but I am back to my original problem.
0
 

Author Comment

by:NevSoFly
ID: 33555314
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
 
LVL 2

Expert Comment

by:marat-oz
ID: 33556409
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
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 1600 total points
ID: 33556762
Dynamic SQL is run using

EXEC (@strSQL)

Brackets are NOT optional.
0
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 1600 total points
ID: 33556776
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
 
LVL 14

Expert Comment

by:wolfman007
ID: 33556796
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33558088
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
 

Author Comment

by:NevSoFly
ID: 33559131
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
 

Author Comment

by:NevSoFly
ID: 33559174
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
 

Author Comment

by:NevSoFly
ID: 33559189
wolfman007,

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

Author Comment

by:NevSoFly
ID: 33559351
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
 
LVL 2

Assisted Solution

by:marat-oz
marat-oz earned 400 total points
ID: 33563326
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33564625
>> 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
 

Author Closing Comment

by:NevSoFly
ID: 33566029
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question