Why does my stored procedure not show up in my SQL Server database?

Question

I created a database in SQL Server. I follow these:

Programmability -> Stored Procedures -> right click -> new stored procedures.

I write this code in my stored procedure:

create procedure Insert_Users_legal
(
    @name   nvarchar(50),
    @agentPosition  int,
    @email  varchar(50),
    @mobile char(11),
    @phone  char(11),
    @postalCode char(10),
    @address    nchar(10)
)
as
    set nocount on

    insert into users_legal (name, agentPosition, email, mobile, phone, postalCode, address)
    values (@name, @agentPosition, @email, @mobile, @phone, @postalCode, @address)

    select CAST(scope_identity() as int) as id_legalUser
    return

But when I save my stored procedure, it doesn't show up in stored procedure folder in the Object Explorer.

What can I do?

enter image description here

In which DB Procedure is created. Try to use fully qualified name. You might be checking in wrong Database — Pரதீப், Dec 28 '14 at 17:13
There is no "stored procedure folder" in a database; this is the functionality of the client you're using to access the database. You haven't said what client you're using... Having said that are you sure you've actually created the procedure? Does the procedure show up in [`INFORMATION_SCHEMA.ROUTINES`](http://stackoverflow.com/a/219510/458741)? — Ben, Dec 28 '14 at 17:17
execute this query `Select name FROM sys.procedures WHERE name LIKE '%Insert_Users_legal%'` — M.Ali, Dec 28 '14 at 17:19
what happens when you run this `create procedure municipalityDB.dbo.Insert_Users_legal....` if you are not getting error then Procedure is created in some other DB — Pரதீப், Dec 28 '14 at 17:23
Have you tried to execute `sp_helptext 'Insert_Users_legal'`? — chridam, Dec 28 '14 at 17:28
You need to execute the statements that you wrote while writing the stored procedure. This was the mistake I was doing. — Shishir Gupta, Mar 28 '17 at 07:03

score 9 · Answer 1 · edited Dec 18 '15 at 11:00

9

First click on Execute, then click on refresh

edited Dec 18 '15 at 11:00

nacho4d

43,720
45
157
240

answered Dec 18 '15 at 08:06

Farzaneh

89
1
1

score 6 · Accepted Answer · answered Dec 28 '14 at 17:29

execute the following statements and it must create a stored procedure in your correct database:

/*****   use the correct database *****/
USE municipalityDB;
GO

/*****   Drop Procedure if already exist *****/
IF (OBJECT_ID('Insert_Users_legal') IS NOT NULL)
  DROP PROCEDURE Insert_Users_legal
GO

/*****   Now create the procedure *****/
create procedure Insert_Users_legal
(
@name           nvarchar(50),
@agentPosition  int,
@email          varchar(50),
@mobile         char(11),
@phone          char(11),
@postalCode     char(10),
@address        nchar(10),
@id_legalUser   INT OUTPUT   --<-- use output param to get new id 
)
as
BEGIN
 set nocount on;
  insert into users_legal (name,agentPosition,email,mobile,phone,postalCode,[address])
  values (@name,@agentPosition,@email,@mobile,@phone,@postalCode,@address)

  SELECT  @id_legalUser = scope_identity();     --<-- No need to cast as INT 
-- return    --<-- Not required   
END
GO


/*****   Check if procedure exists *****/
Select name FROM sys.procedures 
WHERE name LIKE '%Insert_Users_legal%'
GO

You do not need to write all of this code, if you would have selected the correct database from your SSMS from top left drop down list, it would have created the procedure in the correct database. anyway the important change I have made in this procedure's definition is the OUTPUT parameter for newly generated identity value. Using an output parameter will allow you to use this value in other places in your code, otherwise I don't see any point of just selecting it. — M.Ali, Dec 28 '14 at 17:44

Why does my stored procedure not show up in my SQL Server database?

2 Answers2