Stored Procedure Invalid - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Procedure Invalid

Author

Topic

jp2code
Posting Yak Master

175 Posts

Posted - 2010-02-26 : 17:43:13

I wrote a stored procedure to retrieve an employee's badge number from the full name. (it looks through the string for spaces, and parses them as needed)

The stored procedure seems to handle everything I throw at it, so I started placing it in some of my larger stored procedures that were spending a good portion of their time doing this in a crude fashion that was taking too long (see code segment at the bottom).

So far, I have placed it in 2 of my stored procedures. One works fine and the turnaround time is greatly reduced (from 4 seconds per part number to under half a second). The seconds stored procedure saved on the server, but will not run:

Msg 208, Level 16, State 1, Procedure X, Line X
Invalid object name 'dbo.sp_GetBadge'.

Could someone clue me in to what causes this to work on one procedure but not another?

sp_GetBadge is defined as follows:

PROCEDURE [dbo].[sp_GetBadge](@FullName nVarChar(255)) AS
BEGIN
	SET NOCOUNT ON;
	declare @FirstName nVarChar(255), @LastName nVarChar(255)
	declare @space nVarChar(1), @lastChar nVarChar(1)
	declare @index int
	set @space=' ' -- constant - do not change
	set @FirstName='' -- initialize just in case there are no spaces!
	set @LastName=LTrim(RTrim(@FullName))
	set @index=CharIndex(@space, @LastName)
	if (0 < @index) Begin
	  set @FirstName=SubString(@LastName, 1, @index)
	  set @index=Len(@LastName)-1
	  set @lastChar=SubString(@LastName, @index, 1) -- starts at the end...
	  While (@lastChar!=@space) Begin
		set @index=@index-1 -- ...and walks back one char at a time
		set @lastChar=SubString(@LastName, @index, 1)
	  End
	  set @LastName=SubString(@LastName, @index+1, Len(@LastName))
	End
	If (@LastName!='') Begin
		SELECT TOP 1 NUM
		FROM EmployeeInfo
		WHERE (FIRSTNAME=@FirstName) AND (LASTNAME=@LastName)
		ORDER BY [COUNT] DESC
	End Else Begin
		SELECT @LastName As 'NUM'
	End
END

I hate that it is so long! Our SQL Table for EmployeeInfo is imported daily from an older MS Access table that HR uses. It has many errors, and we are trying hard to get away from it, but it has taken years.

The stored procedure that sp_GetBadge fails on is as follows:

SELECT
	CASE WHEN (Len(dbo.sp_GetBadge(TR.OP_ID))=6)
	THEN	EI.[FIRSTNAME]+' '+EI.[LASTNAME]+' ('+dbo.sp_GetBadge(TR.OP_ID)+')'
/*  --- Replaced Section ---
SELECT
	CASE WHEN ((SELECT Count(FIRSTNAME) FROM EmployeeInfo
		WHERE (Replace(RTrim(TR.OP_ID), '  ', ' ')=(RTrim(FirstName)+' '+RTrim(LastName)))
		)>0) THEN (
		SELECT TOP 1 FIRSTNAME+' '+LASTNAME+' ('+NUM+')' AS 'Operator' FROM EmployeeInfo
		WHERE (Replace(RTrim(TR.OP_ID), '  ', ' ')=(RTrim(FirstName)+' '+RTrim(LastName)))
		ORDER BY [COUNT] DESC
	)
*/
	ELSE TR.[OP_ID] END AS 'Operator',
		TR.[Serial_Number], TR.[Date_Time]
FROM	Test_Results TR LEFT JOIN EmployeeInfo EI ON dbo.sp_GetBadge(TR.OP_ID)=EI.[NUM]

The commented section is what sp_GetBadge is meant to replace.

Does anyone have a clue?

All fields are stored as nVarChar(255).

Thank you,
Joe

Avoid Sears Home Improvement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-26 : 23:42:42

are both the procedures in same db?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jp2code
Posting Yak Master

175 Posts

Posted - 2010-03-01 : 09:25:22

Yes, both procedures are in the same database.

Avoid Sears Home Improvement

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources