create stored procedure with a 'use' statement

Question

i inherited an ms sql server 2008 server application and upon checking the stored procedures, i noticed that they start with a USE [ELECS] statement, ELECS being the database name.

when i tried to recreate one of the stored procedures, what i got was an error saying that a stored procedure cannot have a use statement.

Msg 154, Level 15, State 1, Procedure TESTME, Line 3 a USE database statement is not allowed in a procedure, function or trigger.

does ms sql server have a special feature that allows this?

Answer 1

You most likely saw USE above the CREATE/ALTER PROCEDURE statement.

In which case it was being used to set which database the procedure should be updated on.

It cannot be used within a stored procedure, but if you wish to make reference to a different database you can set this in your table reference. Ie: for a SELECT statement:

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