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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-08-16 : 09:28:41
|
| Sachin writes "Hi,I need to be able to develop and maintain generic stored procedures for a server with about 20 databases on it. The stored procedures need to be able to work on any of the 20 databases (they all have the same basic set of tables), but I need the procedures to exist in only one place, so that I don't have to maintain 20 copies of each stored procedure whenever a change comes along (which is quite often). The trick is thatthe stored procedures won't know what database they're working on until run time.The way I'm doing this now is by writing stored procedures that build their syntax at run time, which is a pain to work with, and really, really ugly. The following example shows what I currently have to go through in order to make a simple update to a table:create proc MyProc @dbName varchar(50), -- The database to run against @newVal varchar(20), -- New value to apply to records @limit varchar(20) -- Limit to use in the WHERE clauseas declare @stmt varchar(1000) -- This will hold the generic statement set @stmt = '' set @stmt = @stmt + 'update ' + @dbName + '..MyTable ' set @stmt = @stmt + 'set someCol = ''' + @newVal + ''' ' set @stmt = @stmt + 'where someOtherCol = ''' + @limit + ''' ' exec(@stmt)This will build and execute the following statement (assuming it's given 'XX', 'YY' and 'ZZ' as the 3 arguments): update XX..MyTable set someCol= 'YY' where someOtherCol = 'ZZ'... which would have been a small, simple stored procedure, if I didn't have to deal with the fact that I don't know the name of the database until run time. As you can see, this gets ugly and out of hand in a hurry. Not to mention that trying to get any kind of status information from the query executed via exec() is a pain.So, does anyone have any ideas for a better way to accomplish this? Any suggestions are welcome!Thanks!" |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-08-16 : 10:02:14
|
| if you want to run from one central location then, without using Dynamic SQL you cannot do. for getting the status of the stored procedure you can use sp_executesql, where you can get the value of the output parameters.Read on Bol for more details.Chirag |
 |
|
|
|
|
|
|
|
Generic Stored Procedures on a central Database