Replacing "hard-codes"

Question

Replacing "hard-codes"

a_ud

SSCrazy

Points: 2457
More actions
November 24, 2010 at 3:25 am

#229555

I've got a SQL project made out of 2 databases that contain hard-codes. The hard-codes:
• Refer to the 2 database names of the project.
• Are included in Functions (Programmability tab>Table-valued functions), although there are a few in Stored procedures too.
The idea is to replace these hardcodes by some kind of variable/lookup into a table/any fix, then you'd be able to update just the names of the databases for each project. Problem is:
–There's no global variables in SQL 2005, right? So you can't define 2 of them and assign the values somewhere, job done.
–Dynamic SQL (creating a @nameDB variable, putting it into a string @sql, then exec(@sql)) apparently cannot be used because Functions don't admit dynamic SQL (or so I've been told by SQL gurus yesterday).
Any fix for this? Any technique that might do the trick? Thanks all, al

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

Synonyms might fit the task at hand, but a synonym points to an object, not a part of a name.

for example, say your proc is looking for a specific 3 part object or 4 part object, say PRODUCTION.dbo.INVOICES or OTHERSERVER.PRODUCTION.dbo.INVOICES

you could add a synonym named "TheInvoices" and point it at that specific table/view.

the procedure would then do a SELECT * FROM TheInvoices instead of the previous code.

then if you restored that database to another server, you could change the synonym to point to, say DEVELOPER.MYBACKUP.dbo.INVOICES, without having to change the code in the procedure itself.

do you think that would fit the objective you were looking for?

IF OBJECT_ID('dbo.TheInvoices', 'SN') IS NOT NULL

DROP SYNONYM dbo.TheInvoices;

CREATE SYNONYM dbo.TheInvoices FOR OTHERSERVER.PRODUCTION.dbo.INVOICES ;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

You could always build a procedure to rebuild the functions with hard-codes: just script out the alter statement into a dynamic SQL script. You could even set up a trigger on the table where you store the database names (SQL's answer to "global variables") so that each time you update a name, it will run your dynamic code.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

a_ud SSCrazy Points: 2457 More actions · Answer 3

Thanks Lowell and Matt, both seem good solutions.

Obviously I would have preferred a synonym just for the database name, because if it has to match just a single object it means using at least 10 synonyms, but it is much better than replacing 100 hardcodes for each new project.

On the trigger solution, I hadn't even thought about it, not sure I'm prepared to script out, but I'll try too for the sake of learning.

You made my day!