I cant see my Procedure. Why? This is my first Stored Procedure.

Hello,

I created the "MyDb" database using Microsoft SQL 2005 Server Management Studio and I added the table "dbo.Surveys".

Then I right clicked on Store Procedures and created a new procedure.
When I close it I am asked to save it. I save it and gave the file a name.
However my stored procedure doesn't show in the Stored Procedures list.
I can only access it by loading the file (File > Open > File). Why?

When I open the File the Connect Window shows again.

I believe the procedure is created because I used CREATE.
When I execute it twice I got the message that there is already another procedure with that name so I change CREATE to ALTER.

Could you explain to me how to make my procedure visible in:
Databases/MyDb/Programmability/StoredProcedores

How to run it with dbo.Surveys table and see all the records that returns?

Thank You,
Miguel

My Stored Procedure code is:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE dbo.Surveys_GetSurveyBySurveyName
-- Procedure Parameters
@.SurveyName nvarchar
AS
BEGIN
-- Prevent extra result sets from interfering with SELECT statements
SET NOCOUNT ON;

-- Procedure statements
SELECT m.SurveyName
FROM dbo.application_Surveys
WHERE @.SurveyName = u.SurveyName

IF ( @.@.ROWCOUNT = 0 ) -- Survey Name not found
RETURN -1
RETURN 0

END
GO

USE MyDb

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.Surveys_GetSurveyBySurveyName
-- Procedure Parameters
@.SurveyName nvarchar
AS
BEGIN
-- Prevent extra result sets from interfering with SELECT statements
SET NOCOUNT ON;

-- Procedure statements
SELECT m.SurveyName
FROM dbo.application_Surveys
WHERE @.SurveyName = u.SurveyName

IF ( @.@.ROWCOUNT = 0 ) -- Survey Name not found
RETURN -1
RETURN 0

END
GO

this one can create on sp on my machine.

Limno

|||

You must have created the stored proc in some other database.

Here's a script from SQLServerCentral.com that you can use to find any object: Compile the script in master database and exec it with the stored proc name.

/*
Purpose- Search object across database server.
Author-Vidyadhar P.
Email-vidya_pande@.yahoo.com
Date-6th-Feb-2006
Location- Pune,India
Input Parameters
@.object_name= Name of object to be searched part of object nameto be searched
@.ExactORLikeSearch= If no parameters are passed Sp will search for exact object names.
if 'L' is passes as parameter it will to like seach.
*/

create procedure dbo.sp_find_object -- 'product_master','L'
@.object_name varchar(100),
@.ExactORLikeSearch char(1)=E --E/L
as
begin
set nocount on
declare @.databases table (colid int identity ,dbname varchar(50))
create table ##object_db (dbName varchar(100), objectName varchar(100),objectType varchar(100))
insert into @.databases (dbname) select name from sysdatabases where dbid>4 and name not in ('pubs','northwind', 'holding_tank')
declare @.max_dbs int
select @.max_dbs=max(colid) from @.databases
declare @.current_db varchar(100)

declare @.Qstr nvarchar(2000)
declare @.Qstr1 nvarchar(2000)
set @.Qstr='insert into ##object_db select '
set @.Qstr1=''

declare @.i int
set @.i=1

while @.i<=@.max_dbs
begin
select @.current_db=dbname from @.databases wherecolid=@.i
--------------------------
if @.ExactORLikeSearch='E'

set @.Qstr1=@.Qstr+''''+@.current_db+''''+',name, case xtype when '+''''+'U'+'''' +' then ' +''''+'table'+''''+' when ' +''''+'P'+''''+' then '+''''+'procedure'+''''+' when '+''''+'F'+''''+' then '+''''+'function'+''''+' when '+''''+'V'+''''+' then '+''''+'view'+''''+ ' end as ObjectType
from '+@.current_db+'.dbo.sysobjects wherename='+''''+@.object_name+''''
--------------------------
if @.ExactORLikeSearch='L'

set @.Qstr1=@.Qstr+''''+@.current_db+''''+',name, case xtype when '+''''+'U'+'''' +' then ' +''''+'table'+''''+' when ' +''''+'P'+''''+' then '+''''+'procedure'+''''+' when '+''''+'F'+''''+' then '+''''+'function'+''''+' when '+''''+'V'+''''+' then '+''''+'view'+''''+ ' end as ObjectType
from '+@.current_db+'.dbo.sysobjects where namelike'+''''+'%'+@.object_name+'%'+'''' +'and xtype in ('+''''+'U'+''''+','+''''+'P'+''''+','+''''+'F'+','+''''+''''+'V'+''''+')'
--------------------------

exec sp_executesql @.Qstr1
set @.i=@.i+1
end

select * from ##object_db
drop table ##object_db

end

sql