开发者

How do I avoid a T-SQL error in a stored procedure?

开发者 https://www.devze.com 2023-02-04 15:10 出处:网络
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. F

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.

0

精彩评论

暂无评论...
验证码 换一张
取 消