missing SP when I query for it's text

I have code below to extract a table name and a verb. I receive 4 rows
back. I think fine. Unfortunatly my programmer says that the SP in
question is not part of the return set.
Select routine_name from INFORMATION_SCHEMA.ROUTINES where
Routine_definition like '%PreNote_Transaction_Lookup%'
and Routine_definition like '%insert%'
I change the query to fit the same code he is looking at:
Select routine_name from INFORMATION_SCHEMA.ROUTINES where
Routine_definition like '%insert into PreNote_Transaction_Lookup%'
and I get 0 rows back.
This is from the SP itself:
IF @.Prenote_Trans_ID > 0
BEGIN
INSERT INTO PreNote_Transaction_Lookup Values
(@.Prenote_Trans_ID,null,cast(@.transactio
n_id as varchar(10)))
Select @.Count = count(1) from PreNote_Transaction_Lookup where
Prenote_Transaction_ID = @.Prenote_Trans_ID
I have even put insert into in all caps. Still no rows returned?
Any ideas?Below work fine on my machine:
USE tempdb
GO
CREATE PROC A
AS
DECLARE @.Prenote_Trans_ID int
DECLARE @.transaction_id int
DECLARE @.Count int
IF @.Prenote_Trans_ID > 0
BEGIN
INSERT INTO PreNote_Transaction_Lookup Values
(@.Prenote_Trans_ID,null,cast(@.transactio
n_id as varchar(10)))
Select @.Count = count(1) from PreNote_Transaction_Lookup where Prenote_Trans
action_ID =
@.Prenote_Trans_ID
END
GO
Select routine_name from INFORMATION_SCHEMA.ROUTINES where
Routine_definition like '%insert into PreNote_Transaction_Lookup%'
Is your database case sensitive? Do you have > 4000 charactes in the procedu
re?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"_Stephen" <srussell@.electracash.com> wrote in message
news:O%23Ee6sHRGHA.252@.TK2MSFTNGP10.phx.gbl...
>I have code below to extract a table name and a verb. I receive 4 rows bac
k. I think fine.
>Unfortunatly my programmer says that the SP in question is not part of the
return set.
> Select routine_name from INFORMATION_SCHEMA.ROUTINES where
> Routine_definition like '%PreNote_Transaction_Lookup%'
> and Routine_definition like '%insert%'
> I change the query to fit the same code he is looking at:
> Select routine_name from INFORMATION_SCHEMA.ROUTINES where
> Routine_definition like '%insert into PreNote_Transaction_Lookup%'
> and I get 0 rows back.
> This is from the SP itself:
> IF @.Prenote_Trans_ID > 0
> BEGIN
> INSERT INTO PreNote_Transaction_Lookup Values (@.Prenote_Trans_ID,null,cast
(@.transaction_id as
> varchar(10)))
> Select @.Count = count(1) from PreNote_Transaction_Lookup where Prenote_Tra
nsaction_ID =
> @.Prenote_Trans_ID
>
> I have even put insert into in all caps. Still no rows returned?
> Any ideas?
>