Stored Procedure (Using a variable in FROM Clause)

[Bill Schanks]

I have this stored proc:

Create PROCEDURE dbo.spoc_ev_update_branch_cds (
@dbName varchar(25))
AS

-- ===============================================================
-- Procedure: spoc_ev_update_branch_cds
-- Written By: xxxxxxxxxxxxxxxxxxxxxxxxxx

-- Purpose: Deletes all records from Branch Table,
-- and updates with new values.
--
-- Updates from @dbName

-- Date Developer Description
-- ------- ----------------- -------------------
-- 03/08/2006 Bill Schanks Initial version

-- ===============================================================

begin

Set nocount on

/* Delete old records */
DELETE FROM t_BranchCodes

/* Bring in new records */
INSERT INTO dbo.t_BranchCodes (Branch_Cd, Branch_Nm)
SELECT Branch_Cd, Branch_Nm FROM (e-mail address removed)

end

GO


However I can't seem to use variables in the FROM Clause. Select and
Where are fine. Is there a workaround for this? I am getting a syntax
error.

I could create all the SQL in the Front end adp, but I would prefer to
do it via sp. Any ideas?

Note: This is pulling from a linked server, and works just fine if I
hard code the dbname. The reason I want this to be a variable is
because the linked server is a data wharehouse and we have 16+
Database's (Each are a snapshot of the End of Month Data on our
MainFrame)

 

[Malcolm Cook]

Bill,

See http://www.sommarskog.se/dynamic_sql.html#Dyn_table for good discussion on pros/cons/approaches to 'dynamic sql' such as you
are wanting to do.

In brief, it will advise you against doing it, will suggest some alternative approaches, and will nonetheless give examples of using
using EXEC() and sp_executesql which you may adapt to your circumstance.

I assume you don't hold sway over the warehouse (xxxxxxx in your code). If you did, I would advise considering
refactoring/remodelling it.

Good luck.

 

[Bill Schanks]

I will look at the link ... Thank you.

And you are correct, I don't have any control over the data wharehouse.
It's a vendors system, and many companies use it. So to change the
schema just isn't going to happen.

The schema on that database is not really a bad one, just unique. Each
database is very large, so there would be performance hits to combine
them (by adding an AsOf Date in the tables). It's very clean, you know
what data you are getting when you choose a particular database.

 

[Tckg]

could u pls be more specific?

 

[Bill Schanks]

Be more specfic on what?

 

[ÚÈÏÇáÑÍãä]

ÃÊãäÇ Ãä Êßæä ÕÏÇÞå ÈíäÇäÇ