Calling stored procedures

[IanH]

Afternoon all,

We want to create a central database for common stored procedures (call it SP_DB). Assuming we are working on tables in Database_A does anyone have any simple thoughts on how to call stored procedures from SP_DB so as they run on the tables in the working database.

At the moment, when calling the procedures it is looking at for the tables in SP_DB. Can you, for example, declare a parameter for the database name, and use this when calling the tables?

All advice appreciated.

Ian

 

[pdx_man]

You can do this, but you have to build your SPs dynamically.

Create Procedure dbo.TestSP(@DBName VARCHAR(25)) AS

DECLARE @SqlStr VARCHAR(8000)

SELECT @SqlStr = 'Select TheFields From ' + DBName + '.dbo.TheTable'
EXEC @SqlStr

I would not suggest doing this at all. Not only difficult to write and you do not get the benefit of having stored execution plans, but you open yourself up to security issues.

Instead of passing in a database name, I could pass a SQL string that could do damage to the DBs.

Do a search on Google about the dangers of using Dynamic SQL.

 

[IanH]

Thanks for this - I may give it a miss, and suggest that the team use reusable code and amend for each database as needed.

Cheers

Ian