Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

create stored procedure with a 'use' statement

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?

like image 442
user571099 Avatar asked Nov 16 '12 15:11

user571099


1 Answers

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]
like image 64
Curtis Avatar answered Nov 04 '22 19:11

Curtis