Get the name of the executing SQL Stored Procedure, UDF, or trigger inside the object

On SQL Serer using T-SQL how do you determine the name of the object, such as stored procedure or UDF, that is executing from inside the object while it is executing.

FROM BOL:

@@PROCID (Transact-SQL)

Returns the object identifier (ID) of the current Transact-SQL module. A Transact-SQL module can be a stored procedure, user-defined function, or trigger. @@PROCID cannot be specified in CLR modules or the in-process data access provider.

couple this with the Object_Name() function and Wah-Lah!

Try the following inside a stored procedure:

SELECT
	  SCHEMA_NAME(SO.schema_id) AS SchemaName
	, OBJECT_NAME(@@PROCID) AS ObjName
	, @@PROCID AS ProcID
FROM	
	sys.objects so
WHERE
	SO.object_id = @@PROCID

If you don't need the schema save the trip to the sys.objects table.

This code even works in nested calls

If run as just a script, not inside a module, it returns NULL.

{With thanks to Mike}

Write code for the next developer….Because it might be you. {Or worse, your boss}

There are many philosophies to writing software, XP, Agile, RAD, SCRUM etc.  Having studied many of them and practicing a few there is one universal methodology that should be adhered  to no matter how inconvenient it may seem at the time:

Always write code for the next developer. 

Usually this means simple things like leaving good comments as to what was to be accomplished, maybe even why it was needed such as a special case that came up in testing or use.  Just as important is following a style or technique guide.  When we work in teams it is very beneficial if we all follow the same patterns and coding techniques.  It is especially important to avoid bad code habits.

It is surprising how much test code winds up in production because it’s convenient.  Why let your bad habits reflect poorly on you and frustrate those who come through your code after you.  It really might be you again months later or worse you boss!

{I wish I could disable the ability in T-SQL to use the SELECT INTO directive to create a new table based on the results of a query.   ARRRGH!!  There are readers of this blog, who shall rename nameless but know who they are that that gripe was directed.}

SQL Output Clause into a table variable with more columns then the output

It turns out that if you are trying to use an output clause to insert data into a table variable / temp table and the number of fields in the table variable is greater then the number of fields you are actually using in the output clause an error will be raised. To solve this problem you will have to output a NULL or default value for the missing columns in the output clause.

DECLARE @NewPreson TABLE(
	  ID_Person		BIGINT NOT NULL
	, [RS]			UNIQUEIDENTIFIER NOT NULL
	, ID_Person_tmp	BIGINT NULL
)

INSERT INTO dbo.Person (
	  [First Name]
	, [Last Name]
	, [MI]
	, [Birth Date]
	, [RS]
        )
OUTPUT 
	  INSERTED.ID_Person -- Identity column
	, INSERTED.[RS]
	, NULL -- Needed to make the column count match
INTO 
	@NewPreson	
SELECT 
	  [First Name]
	, [Last Name]
	, [MI]
	, [Birth Date]
	, [RS]
FROM 
	#Staging Stg

that's it

SSRS Formatting output

Frankly I'm frustrated by the formatting options available in SSRS when trying to get a field to look the way you want. The built in choices are good for a few things but it is tricky to get a custom one to work. I have gotten some to work but it is very hit or miss. My Solution is to use the Format() function as part of the expression. This gives the most options with the best resulst a documentation that can be found easily through google. The only gotcha so far is that it the formatting codes are case sensitive: =Format(Fields!MyDateField.Value,"MMM-yy") results in a lovely Jan-09 output "mmm-YY" gives an odd 00-YY

Cannot use CTE with Exists Clause

Apparently the syntax for the CTE does not work inside an EXISTS function. The solution, however, is very simple; Just use a derived table instead. {Oh yeah forgot that before CTEs we use to use derived tables instead.}

-- Sorry this code doesn't work due to CTE in EXISTS Function
IF EXISTS(
    WITH LargeDepartmentNewHires AS (
        SELECT
              HREDH.DepartmentID
        FROM
            HumanResources.EmployeeDepartmentHistory HREDH
        WHERE
            HREDH.StartDate BETWEEN '1/1/1999' AND '12/31/1999'
        GROUP BY
              HREDH.DepartmentID
             , YEAR(HREDH.StartDate)
        HAVING
              COUNT(*) >=10
        )
    SELECT
          HRE.*
    FROM
        HumanResources.Employee HRE
            INNER JOIN
        HumanResources.EmployeeDepartmentHistory HREDH
            ON HRE.EmployeeID = HREDH.EmployeeID
            INNER JOIN
        LargeDepartmentNewHires LDNH
            ON HREDH.DepartmentID = LDNH.DepartmentID
    )
    BEGIN
        PRINT 'Yes it exists'
    END
ELSE
    BEGIN
        PRINT 'Does NOT exists'
    END

Use a derived table instead of a CTE

--This does work
IF EXISTS(
    SELECT
          HRE.*
    FROM
        HumanResources.Employee HRE
            INNER JOIN
        HumanResources.EmployeeDepartmentHistory HREDH
            ON HRE.EmployeeID = HREDH.EmployeeID
            INNER JOIN
        (
        SELECT
              HREDH.DepartmentID
        FROM
            HumanResources.EmployeeDepartmentHistory HREDH
        WHERE
            HREDH.StartDate BETWEEN '1/1/1999' AND '12/31/1999'
        GROUP BY
              HREDH.DepartmentID
             , YEAR(HREDH.StartDate)
        HAVING
              COUNT(*) >=10
        ) LDNH --LargeDepartmentNewHires
            ON HREDH.DepartmentID = LDNH.DepartmentID
    )
    BEGIN
        PRINT 'Yes it exists'
    END
ELSE
    BEGIN
        PRINT 'Does NOT exists'
    END

If your CTE is a hierarchical query then there isn't much you can do inside the exist statement. However, remember what and how the EXISTS function works. Its purpose is to return a true as soon as the containing query returns any rows if any exist at all. To mimic this functionality rewrite the query to return a count of the rows would have been found in the EXISTS function. If the count is not 0 then it the true that the rows exist. This isn't the most efficient but it is a valid solution. Keep in mind to optimize performance return as little data as possible.

DECLARE @ReturnedRows    INT

;
WITH LargeDepartmentNewHires AS (
    SELECT
          HREDH.DepartmentID
    FROM
        HumanResources.EmployeeDepartmentHistory HREDH
    WHERE
        HREDH.StartDate BETWEEN '1/1/1999' AND '12/31/1999'
    GROUP BY
          HREDH.DepartmentID
         , YEAR(HREDH.StartDate)
    HAVING
          COUNT(*) >=10
    )
SELECT
      @ReturnedRows = COUNT(*)
FROM
    HumanResources.Employee HRE
        INNER JOIN
    HumanResources.EmployeeDepartmentHistory HREDH
        ON HRE.EmployeeID = HREDH.EmployeeID
        INNER JOIN
    LargeDepartmentNewHires LDNH
        ON HREDH.DepartmentID = LDNH.DepartmentID

IF @ReturnedRows != 0
    BEGIN
        PRINT 'Yes it exists'
    END
ELSE
    BEGIN
        PRINT 'Does NOT exists'
    END