Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: Procedure, know thyself...

    Anybody got a code snippet that will allow a stored procedure to determine its own name at execution?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It gets real ugly real fast. I think only admins can run this code with no error. Any problems with just teaching each sproc its own name by hard-coding? That's how I learned my name, anyway ;-)

    Code:
    create procedure testproc
    as
    create table #temp
    (col1 nvarchar(20),
     col2 int,
     col3 varchar(255))
    
    insert into #temp
    exec ('dbcc inputbuffer(@@spid)')
    
    select col3 from #temp
    drop table #temp
    go
    
    exec testproc

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Pat pointed me to this: SELECT OBJECT_NAME(@@PROCID)

    Works well:
    Code:
    CREATE PROCEDURE testprocedure AS
    SELECT OBJECT_NAME(@@PROCID) AS 'ProcName'
    GO
    EXEC testprocedure
    GO
    drop procedure testprocedure
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I gotta ask...why do you need to do this?

    I do sproc loggin in some systems, with a nested logging call, and still don't see the need for this
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    For event logging.
    One less hard-code value.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    CREATE PROC <procname>

    well I guess it can be a resusable piece of code...make it part of tql template I guess
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This code goes at the top of the procedures I want to monitor:
    Code:
    --Log Event--------------------------------------------------------------------------------------------------
    declare	@EventText varchar(500)
    declare	@ProcedureName varchar(50)
    
    set	@ProcedureName = OBJECT_NAME(@@PROCID)
    set	@EventText = @ProcedureName + ' '
    		+ dbo.LogParam('@Param1', @Param1)
    		+ dbo.LogParam('@Param2', @Param2)
    		...
    		+ dbo.LogParam('@ParamN' ,@ParamN)
    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LogEvent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    	exec LogEvent @ProcedureName, @EventText
    --------------------------------------------------------------------------------------------------------------------
    If I get a chance, I'm going to look for a way to generate the parameter names and values automatically as well, though that would involve dynamic code.
    This logs a record that contains a complete execute statement to recreate in query analyzer whatever sproc and parameters are submitted by a user.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT name FROM dbo.syscolumns WHERE (@@procid) = id
    -PatP

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oh yeah, get chummy with QuoteName() too.

    -PatP

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    This logs a record that contains a complete execute statement to recreate in query analyzer whatever sproc and parameters are submitted by a user.

    That has got to be a bad idea...if it's a low OLTP, then maybe...but why do you care

    I mostly care about data changes with history...next would be performance
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This is for debugging. So when the developer comes to me and says "your stored procedure returns the wrong data", I can say "Your code submitted the wrong parameters".
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    How about
    Code:
    DECLARE @func VARCHAR(50)
        	SELECT @func = Object_name(@@Procid)
    as in
    Code:
    ErrorHandler:
    	DECLARE @func VARCHAR(50)
        	SELECT @func = Object_name(@@Procid)
        	EXECUTE [dbo].[ErrorHandler] @m_error, 'MyDBIndex sp', @func
    	RAISERROR ('Stored Procedure %s failed with error number %d', 16, 1, @func, @m_error)
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  13. #13
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Dang, just when I thought smugly "I know this one!", I get scooped. damn. Back to the mines I go...
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm too lazy to modify code... SQL Profiler will give me all the evidence I need to abuse the lazy, without requiring anything more on my part than simply turning SQL Profiler on and watching the fun.

    -PatP

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I like my code because I am lazy.
    I don't have to have profiler running constantly to catch the executed sprocs, and I can copy and paste the code directly from my eventlog table into query analyzer to reproduce the issue.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •