T-SQL Dynamically execute stored procedure

Question

I have a logging function in T-SQl similiar to this:

CREATE PROCEDURE [logging]    
@PROCEDURE VARCHAR(50),
@MESSAGE VARCHAR(MAX)
AS
BEGIN
    PRINT @MESSAGE

END;
GO

I am able to call it like this:

execute logging N'procedure_i_am_in', N'log_message';

As my stored procedure names are a bit long winded, I want to write an alias or an inline function or so, to call the logging procedure for me, with the current procedure. Something like this (which is broken):

declare @_log varchar(max)
set @_log = 'execute logging N''procedure_i_am_in'', '
execute @_log N'MESSAGE!'

And i would put that alias at the top of each procedure.

What are your thoughts?

Not clear on your question. Do you want help on your code (that you seem to indicate is broken) or do you want opinions on your approach to logging? — Russell McClure
– Russell McClure, Commented Dec 7, 2011 at 22:48
@Russel McClure Question was for help with code, but im receptive to any criticism — Chris
– Chris, Commented Dec 7, 2011 at 22:51
@Chris: your idea is good and I use it, but you can have a genericway to resolve "procedure_i_am_in" as per my answer — gbn
– gbn, Commented Dec 7, 2011 at 23:01

gbn · Accepted Answer · 2011-12-08 15:17:21Z

2

Quite simple

CREATE PROCEDURE [logging]    
   @PROCID int,,
   @MESSAGE VARCHAR(MAX)
-- allows resolution of @PROCID in some circumstances
-- eg nested calls, no direct permission on inner proc
WITH EXECUTE AS OWNER
AS
BEGIN
    -- you are using schemas, right?
    PRINT OBJECT_SCHEMA_NAME(@PROCID) + '.' + OBJECT_NAME(@PROCID);
    PRINT @MESSAGE
END;
GO

Then

execute logging @@PROCID, N'log_message';

MSDN on OBJECT_SCHEMA_NAME and @@PROCID

Edit:

Beware of logging into tables during transactions. On rollback, you'll lose the log data

edited Dec 8, 2011 at 15:17

answered Dec 7, 2011 at 22:56

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tony Hopkinson · Accepted Answer · 2011-12-07 22:47:23Z

0

More trouble than it's worth, but it would be

Set @_log = 'exec ....N' + 'MESSAGE!'
Exec (@log)

So not a lot of use.

Personally I'sd just rename the SP, or at a push use a tersely named function. Building strings and exec'ing them is an only if you must admin style facility IMHO

answered Dec 7, 2011 at 22:47

Tony Hopkinson

20.4k3 gold badges35 silver badges40 bronze badges

Collectives™ on Stack Overflow

T-SQL Dynamically execute stored procedure

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related