Create a stored procedure using the 'use' statement

Question

i inherited the ms sql server 2008 server application and after checking the stored procedures, I noticed that they start with the USE [ELECS] statement , ELECS , which is the database name.

when I tried to recreate one of the stored procedures, I received an error message stating that the stored procedure could not contain a usage statement.

Msg 154, Level 15, State 1, TESTME procedure, line 3, and the USE database statement are not allowed in a procedure, function, or trigger.

Does ms sql server have a special function that allows this?

Answer 1

You most likely saw USE on the CREATE / ALTER PROCEDURE .

In this case, it was used to install the database in which the procedure should be updated.

It cannot be used in a stored procedure, but if you want to link to another database, you can set this in your table. That is: for a SELECT :

 SELECT * FROM [DatabaseName].dbo.[TableName] 

Answer 2

Use Go before the USE[databaseName] statement. Worked for me in SQL 2005

Example:

 GO USE [Database_Name] Go ALTER PROCEDURE [dbo].[Procedure_name] 

Answer 3

There are certain top-level statements that must be executed first in the execution unit. You can close / close the execution block with the word GO

So:

 USE [db] GO CREATE PROC.... 

If you are talking about a USE expression inside sproc, then no, you cannot do this. Sproc is already bound to the database in which it was created. If you want to access another database, you need to use a three-digit naming convention, for example. ELECS.dbo.tablename Assuming dbo is your schema.

Answer 4

That's right, the USE expression is not really part of the stored procedure, but rather, by its own operator who instructs the tool (i.e. Enterprise Manager or Query Analyzer) which database to use. It never goes to sql server and has no meaning for it ...