I have several databases I'm managing, and each needs a copy of a particular stored procedure.
The problem is, the stored procedure insert into a table for all of the databases except one of them. For that one database, the table is a partitioned view and is not updatable, and I don't need to insert.
I've tried to do something like the following:
CREATE PROCEDURE st_doit AS
-- Do lots of other stuff...
IF(DB_NAME() <> 'db3') BEGIN
INSERT INTO mytable...;
END
I still get an error message when attempting to execute the stored procedure in db3
because of the INSERT
statement, even though in that database, the insert won't actually be executed. 开发者_如何学JAVAWrapping the INSERT
in a TRY...CATCH
block didn't help either.
For ease of maintenance, I would REALLY like to avoid having a special copy of the procedure in db3
. Usually I wouldn't put an IF statement like this in a procedure, but in this particular case, it really is the best solution, and it won't grow over time into a nest of other special cases over time.
So, how do I force SQL Server to just execute the stored procedure and only generate an error if I actually try to insert into the table?
Edit: Since using dynamic SQL was a bit too messy for me, my end solution was to keep the IF
block as a trap, but to comment out the offending block within db3
. Not ideal, but works. Fortunately I won't be changing this SP often, but I wanted a "complete" copy of it in every database.
I think the only way is to use dynamic sql
if( objectproperty(object_id('dbo.MyTable'), 'isUserTable') = 1 ) begin
-- insert the value 32 into MyColumn of MyTable
exec sp_executesql
N'insert into MyTable(MyColumn) values(@MyColumnParam)',
N'@MyColumnParam int',
32
end
You'd have to "hide" the INSERT from SQL Server using dynamic SQL.
CREATE PROCEDURE st_doit AS
-- Do lots of other stuff...
IF(DB_NAME() <> 'db3') BEGIN
EXEC SP_EXECUTESQL N'INSERT INTO mytable...';
END
Would it be possible/handier to have an SP for the part within the IF
block? Only for DB3
that would be a dummy SP doing nothing and for all the others performing the actual insert. The original SP would be then the same for every DB.
EDIT: Of course, that would call for support for two 'similar' SPs, but I can imagine some practicalness of such an approach when the initial SP is quite complex, and so the special case might be easier to maintain for the second, smaller one, which either does or doesn't do the insert.
精彩评论