Newbie T-SQL dynamic stored procedure -- how can I improve it?
- by Andy Jones
I'm new to T-SQL; all my experience is in a completely different database environment (Openedge). I've learned enough to write the procedure below -- but also enough to know that I don't know enough!
This routine will have to go into a live environment soon, and it works, but I'm quite certain there are a number of c**k-ups and gotchas in it that I know nothing about.
The routine copies data from table A to table B, replacing the data in table B. The tables could be in any database. I plan to call this routine multiple times from another stored procedure. Permissions aren't a problem: the routine will be run by the dba as a timed job.
Could I have your suggestions as to how to make it fit best-practice? To bullet-proof it?
ALTER PROCEDURE [dbo].[copyTable2Table]
@sdb varchar(30),
@stable varchar(30),
@tdb varchar(30),
@ttable varchar(30),
@raiseerror bit = 1,
@debug bit = 0
as
begin
set nocount on
declare @source varchar(65)
declare @target varchar(65)
declare @dropstmt varchar(100)
declare @insstmt varchar(100)
declare @ErrMsg nvarchar(4000)
declare @ErrSeverity int
set @source = '[' + @sdb + '].[dbo].[' + @stable + ']'
set @target = '[' + @tdb + '].[dbo].[' + @ttable + ']'
set @dropStmt = 'drop table ' + @target
set @insStmt = 'select * into ' + @target + ' from ' + @source
set @errMsg = ''
set @errSeverity = 0
if @debug = 1
print('Drop:' + @dropStmt + ' Insert:' + @insStmt)
-- drop the target table, copy the source table to the target
begin try
begin transaction
exec(@dropStmt)
exec(@insStmt)
commit
end try
begin catch
if @@trancount > 0
rollback
select @errMsg = error_message(),
@errSeverity = error_severity()
end catch
-- update the log table
insert into HHG_system.dbo.copyaudit
(copytime, copyuser, source, target, errmsg, errseverity)
values( getdate(), user_name(user_id()), @source, @target, @errMsg, @errSeverity)
if @debug = 1
print ( 'Message:' + @errMsg + ' Severity:' + convert(Char, @errSeverity) )
-- handle errors, return value
if @errMsg <> ''
begin
if @raiseError = 1
raiserror(@errMsg, @errSeverity, 1)
return 1
end
return 0
END
Thanks!
