Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to place stored procedure in desired location?

Tags:

sql

sql-server

On SQL Server 2008, how can I place my stored procedures in "Stored Procedures" folder of my DB?

When I declare it this way:

CREATE PROCEDURE mySchema.myProc

It goes to:

MYSERVER\System Databases\Master\Programmability\Stored procedures folder.

How to tell server, to store it in:

MYSERVER\System Databases\MYDB\Programmability\Stored procedures folder.

EDIT:

If I declare it like that:

CREATE PROCEDURE [myDB].mySchema.myProc

It complains about:

'CREATE/ALTER PROCEDURE' does not allow specifying the database name as a prefix to the object name.

If I use the 'USE' keyword, It complains:

a USE database statement is not allowed in a procedure, function or trigger.

Maybe the problem is that I'm using MS Management Studio, and connecting directly to the server, and not to any particular DB?

like image 879
kofucii Avatar asked Sep 12 '10 11:09

kofucii


People also ask

Can I use stored procedure in where clause?

You can not use Stored Procedure in where clause but can use User Defined Function in where clause. If you cant convert SP to function then you have to first get bit value from executing SP and use that variable in where clause..

Where is the location of stored procedure in SQL Server?

You can find the stored procedure in the Object Explorer, under Programmability > Stored Procedures as shown in the following picture: Sometimes, you need to click the Refresh button to manually update the database objects in the Object Explorer.

How do you find where a stored procedure is used?

Using SQL Server Management Studio Expand Databases, expand the database in which the procedure belongs, and then expand Programmability. Expand Stored Procedures, right-click the procedure and then click View Dependencies. View the list of objects that depend on the procedure.

Where does stored procedure reside?

Within SQL Server Studio, stored procedures, or procedures for short, reside within any database, under the programmability subdirectory.


2 Answers

Try the following

USE MyDb 
GO 
CREATE PROCEDURE mySchema.myProc
like image 79
Rob Avatar answered Oct 10 '22 23:10

Rob


It looks like you are creating the procedure in master database instead of your database. Add Use MYDB above the create script of the stored procedure and it will be created in your database.

like image 27
Giorgi Avatar answered Oct 10 '22 23:10

Giorgi