7 Replies - 416 Views - Last Post: 07 June 2017 - 04:32 PM

#1 depricated  Icon User is offline

  • Nero


Reputation: 2287
  • View blog
  • Posts: 5,926
  • Joined: 13-September 08

Using dynamic database names in a stored procedure

Posted 06 June 2017 - 12:57 PM

I know how to do this in C# by constructing a SQL statement, but I can't really seem to find anything on ye olde Google about how I might do this in a stored procedure:

I have a series of about 25 databases all with the same name and a suffix. i.e. "database_client1" and "database_client2"

That's right, someone decided to store data in the database names. (insert dramatic sigh) They're otherwise identical.

What I want is to write a series of stored procedures that can be called to run on any of them from a linked server.

so for instance, I want to be able to call "exec backfill client1" and it automatically use database_client1 as the db

as I said, I know how I would do this in C#:
string sql = "SELECT * FROM linkedServer.database_" + clientDBSuffix + ".table"



But it seems, to me, inelegant to have a program running a job that should be a sql agent job. This is the best method that occurs to me. I'm no SQL expert though, so please, I'm open to suggestions as well.

Is This A Good Question/Topic? 0
  • +

Replies To: Using dynamic database names in a stored procedure

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13395
  • View blog
  • Posts: 53,464
  • Joined: 12-June 08

Re: Using dynamic database names in a stored procedure

Posted 06 June 2017 - 01:05 PM

Don't forget you should be able to get a list of database silo names with:

SELECT name 
FROM master.dbo.sysdatabases



The thinking is you get your list of databases.. then cycle through them and have the 'use' statement alter (since the rest should be teh same, right?).

declare @foo varchar(100)
SELECT @foo = name FROM master.dbo.sysdatabases WHERE dbname like 'what evs'

exec ('use ' + @foo) -- execute the concatenation of the 'use' statement and a db.
-- rest of the queries would be the same but no actual db reference.


Was This Post Helpful? 2
  • +
  • -

#3 andrewsw  Icon User is offline

  • lashings of ginger beer
  • member icon

Reputation: 6340
  • View blog
  • Posts: 25,562
  • Joined: 12-December 12

Re: Using dynamic database names in a stored procedure

Posted 06 June 2017 - 01:06 PM

Concatenation in a stored procedure is the same

+ (String Concatenation) (Transact-SQL)
Was This Post Helpful? 1
  • +
  • -

#4 depricated  Icon User is offline

  • Nero


Reputation: 2287
  • View blog
  • Posts: 5,926
  • Joined: 13-September 08

Re: Using dynamic database names in a stored procedure

Posted 06 June 2017 - 03:05 PM

Ah, that works almost perfectly. Except I need it to be within the query, which will make troubleshooting difficult if I wrap the entire thing in an exec.

Just to verify that, I wrote this:
DECLARE @table varchar(100)
set @table = 'linkedServer.Database_Client1.dbo.Table'
exec ('select top 1 * from ' + @table)


And that will certainly do the job, any thoughts on how to make that easier to step through though?
Was This Post Helpful? 0
  • +
  • -

#5 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13395
  • View blog
  • Posts: 53,464
  • Joined: 12-June 08

Re: Using dynamic database names in a stored procedure

Posted 06 June 2017 - 04:34 PM

I am not quite following..
Was This Post Helpful? 0
  • +
  • -

#6 maceysoftware  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 348
  • View blog
  • Posts: 1,491
  • Joined: 07-September 13

Re: Using dynamic database names in a stored procedure

Posted 07 June 2017 - 12:56 AM

Sadly the answer is no (as far as I know), there is no good way to step through the dynamic SQL. Which is why dynamic SQL is often regarded as bad.

However there are often times when you do need to do it.

We do all our upgrade scripts as dynamic SQL (not sure why, never asked? I should really) and occasionally these scripts can have issues, we normally deal with this with Try Catch surrounding the dynamic SQL, then outputting the error message when something goes wrong.

You may not want to output but rather log to a table or even raise a error containing the information perhaps?

Example of it in in the second answer here:

https://stackoverflo...sql-server-2008

I guess you could run SQL profiler to see what is going on, however that won't really help with stepping through.

This post has been edited by maceysoftware: 07 June 2017 - 12:57 AM

Was This Post Helpful? 1
  • +
  • -

#7 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon


Reputation: 6966
  • View blog
  • Posts: 14,572
  • Joined: 16-October 07

Re: Using dynamic database names in a stored procedure

Posted 07 June 2017 - 04:28 AM

View Postdepricated, on 06 June 2017 - 02:57 PM, said:

What I want is to write a series of stored procedures that can be called to run on any of them from a linked server.

so for instance, I want to be able to call "exec backfill client1" and it automatically use database_client1 as the db


Dynamic SQL is to be avoided. Programmers think about passing parameters. For a database, it's about storage, normalization, and speed; elegant programming doesn't often come into play.

My solution would be to simply store a copy of every stored procedure in every database. Have a master somewhere you can push updates from. The code would then be for the current schema, no worries.

So, rather than think exec backfill client1, think exec database_client1.backfill. This offers several advantages, the only disadvantage being storage which is trivial to a database.

Now, if you still want to run through a list, at least your dynamic SQL can be boiled down to a single call to each stored procedure and not all that creating stuff with table names.

Hope this helps.
Was This Post Helpful? 1
  • +
  • -

#8 depricated  Icon User is offline

  • Nero


Reputation: 2287
  • View blog
  • Posts: 5,926
  • Joined: 13-September 08

Re: Using dynamic database names in a stored procedure

Posted 07 June 2017 - 04:32 PM

25 databases, 106 tables each

Here's what I've put together.

Basically we have 25 identical databases with 106 identical tables. Literally the only difference is the name of the database. So I've created one consolidated database I want to populate from each db by linking to the server and querying each table

Maybe this is why programmers and DBAs are closely linked but separate.

I did make the names generic for this but this is essentially what I'm running right now and it seems to be working pretty nicely. Thanks for the suggestion on adding some logging - I threw in a table to track start/complete of the process by client and table, so if it breaks down I can see where it broke down.

BEGIN

DECLARE @table varchar(50)
DECLARE @client varchar(3)

DECLARE database_tables CURSOR FOR
SELECT name FROM sys.tables
where schema_id = 1

OPEN database_tables


FETCH NEXT FROM database_tables
into @table

WHILE @@FETCH_STATUS = 0
BEGIN
	
	DECLARE client_codes CURSOR FOR
	SELECT dbcode FROM schema2.dbcodes
	where active = 1;

	OPEN client_codes

	FETCH NEXT FROM client_codes
	into @client
	
	exec ('truncate table ' + @table)

	WHILE @@FETCH_STATUS = 0
	BEGIN
		exec schema2.Log_Backfill_Started @client, @table
		exec ('INSERT INTO ' + @table + '
			SELECT ''' + @client + ''', * FROM [Server].[database_' + @client + '].[dbo].[' + @table + ']')
		exec schema2.Log_Backfill_Completed @client, @table
		FETCH NEXT FROM client_codes
		into @client
	END

	CLOSE client_codes
	DEALLOCATE client_codes

	FETCH NEXT FROM database_tables
	into @table

END

CLOSE database_tables
DEALLOCATE database_tables


END


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1