select from different database in stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

select from different database in stored procedure

Author

Topic

-Dman100-
Posting Yak Master

210 Posts

Posted - 2007-02-27 : 11:52:09

How do I correctly query from a different database table in a stored procedure?

Here is the procedure:

CREATE PROCEDURE spInsertRegistration

@firstname varchar(25),
@lastname varchar(25),
@job_title varchar(50),
@manager varchar(50),
@cost_center char(3),
@address varchar(50),
@city varchar(50),
@state_id INT,
@zip char(5),
@email varchar(50),
@phone char(10),
@roommate varchar(50),
@arrival datetime,
@departure datetime,
@special_needs varchar(500),
@result INT OUTPUT

AS

DECLARE @stateabr VARCHAR(2)

SET @result=0

SELECT @stateabr=state_abr FROM Public.dbo.state WHERE state_id=@state_id

INSERT INTO registration_form
VALUES(@firstname,@lastname,@job_title,@manager,@cost_center,@address,@city,@stateabr,@zip,@email,@phone,@roommate,@arrival,@departure,@special_needs)

IF @@rowcount>0
SET @result=1
GO

This is the specific line where I'm trying to access a different database:

SELECT @stateabr=state_abr FROM Public.dbo.state WHERE state_id=@state_id

Thank you.

Kristen
Test

22859 Posts

Posted - 2007-02-27 : 12:21:36

You should be able to do

OtherDatabase.dbo.MyTable

Is [Public] the name of the other database?

Is it on the same server?

Kristen

-Dman100-
Posting Yak Master

210 Posts

Posted - 2007-02-27 : 12:27:27

Yes, Public is the correct name of the db and it's on the same server. Once I saw you post, I added the brackets around the db name and it worked.

Does that indicate 'Public' was a reserved word?

Thank you.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-27 : 22:21:19

Yes. public is a reserved word. It should shown in BLUE in Query Analyser.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources