The Heap™ – Consultancy ©®

hot2use

6:42 AM

Hi everybody :-)

Any mods arounds?

User hacksoar could possibly become a royal PITA looking at this question:

1

Q: questionbcvsdgggggggggggggggggggggfx

questiongsdfvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv

I've flagged and added a comment

Rolled back question. Nonetheless, might be a good idea to give him a nudge.

Paul White

7:59 AM

@sp_BlitzErik Hadn't heard of it before today!

It would be great if, instead of (as well as) translating a scalar UDF to a relational expression, the engine could give the code to implement it as an in-line table-valued function.

@hot2use Thanks (to everyone who flagged)

McNets

Norming

Upps morning

Tom V

8:49 AM

Morning

gbn

Morning all

Andy K

9:04 AM

evening and morning

Andriy M

Morning

Paul White

9:47 AM

morning

McNets

9:59 AM

Is there a way to get OBJECT_ID() function text?

exec sp_helptext @objname = 'OBJECT_ID';
doesn't works

hot2use

OBJECT_DEFINITION ( object_id )

docs.microsoft.com/en-us/sql/t-sql/functions/…

McNets

@hot2use Thanks

There isn't the code

hot2use

10:27 AM

Hmmm.

@McNets SELECT * FROM syscomments WHERE TEXT LIKE 'CREATE FUNCTION %VAR%'

Might work in some cases.

McNets

@hot2use which db should I use, master?

hot2use

There is a lot in master. Depends where the function is.

Found this gem:

create function sys.fn_numberOf1InVarBinary (@bm varbinary(128))
returns int
as
begin
    declare @len int
    declare @count int
    declare @subbyte binary
    set @count=0
    set @len=DATALENGTH (@bm)
    while (@len>0)
    begin
            set @subbyte=SUBSTRING(@bm, @len, 1)
            set @count=@count+sys.fn_numberOf1InBinaryAfterLoc(@subbyte, 8)
            set @len=@len-1
    end
    return @count

end

McNets

I wonder if there is some built-in UNQUOTENAME function

gbn

@McNets REPLACE(@SomeString, '[', '')?

McNets

Something like:

CREATE FUNCTION UNQUOTENAME(@TXT NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
RETURN IIF(LEFT(@TXT, 1) = N'[' AND RIGHT(@TXT, 1) = ']', SUBSTRING(@TXT, 2, LEN(@TXT) - 2), @TXT);
END;

if fact OBJECT_ID allows to use both quoted and un-quoted names as a parameter

gbn

10:38 AM

@McNets This is not consistent with all system function. I dunno which but I've been bitten before

McNets

ok

hot2use

TRANSLATE() as of SQL 2017

Andriy M

11:04 AM

I don't think you can apply sp_helptext/OBJECT_DEFINITION to built-in T-SQL functions like OBJECT_ID or otherwise get their source code in T-SQL.

As for accepting unquoted string parameters just as well as quoted, I think that's just a peculiarity of the syntax, and it works with built-in functions but probably not with those created using CREATE FUNCTION.

It works with stored procedures too, by the way.

USE tempdb
GO
CREATE PROCEDURE dbo.TestProc (@StringParam varchar(100))
AS
SELECT @StringParam;
GO
EXECUTE dbo.TestProc test;
GO
DROP PROCEDURE dbo.TestProc;
GO

Paul White

10

Q: What allows SQL Server to trade an object name for a string passed to a system procedure

What causes it to be legal to pass an object name to the system stored procedure sp_helptext? What mechanism converts the object name to a string? e.g. -- works sp_helptext myproc sp_helptext [myproc] sp_helptext [dbo.myproc] -- and behaves the same as a string sp_helptext 'myproc' sp_helptext...

sql-server stored-procedures syntax parameter database-internals

You may find that relevant/helpful @McNets

Transcript for

The Heap™ – Consultancy ©®