June 18, 2017 at 5:42 pm
I am working in a third-party database that has the following function:
USE [TestDB]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE FUNCTION [dbo].[clrFuncCarParkCounterGet](@SystemUId [uniqueidentifier], @CarParkUId [uniqueidentifier])
RETURNS TABLE (
[CarParkUId] [uniqueidentifier] NULL,
[CurrentCarParkFullTotal] [int] NULL,
[CurrentCarParkFullWithoutReservation] [int] NULL,
[CurrentCarParkFullWithReservation] [int] NULL,
[CurrentShortTermParker] [int] NULL,
[CurrentSeasonParkerWithoutReservation] [int] NULL,
[CurrentSeasonParkerWithReservation] [int] NULL,
[CurrentDebitCardWithoutReservation] [int] NULL,
[CurrentDebitCardWithReservation] [int] NULL,
[CurrentCongressTicketWithoutReservation] [int] NULL,
[CurrentCongressTicketWithReservation] [int] NULL,
[MaxCarparkFull] [int] NULL,
[CarParkFullWithoutReservationLimitMinValue] [int] NULL,
[AutomaticCarPark] [bit] NULL,
[StateCarPark] [smallint] NULL,
[MaxCarparkFullWithReservation] [int] NULL,
[ShortTermParkerPreCounter_1] [int] NULL,
[AutomaticTransparent_1] [bit] NULL,
[StateTransparent_1] [smallint] NULL,
[StateTransparent_1_LimitMinValue] [int] NULL,
[ShortTermParkerPreCounter_2] [int] NULL,
[AutomaticTransparent_2] [bit] NULL,
[StateTransparent_2] [smallint] NULL,
[StateTransparent_2_LimitMinValue] [int] NULL,
[ShortTermParkerPreCounter_3] [int] NULL,
[AutomaticTransparent_3] [bit] NULL,
[StateTransparent_3] [smallint] NULL,
[StateTransparent_3_LimitMinValue] [int] NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [CarParkCounterClr].[Manufacturer.TestDB.Counter.UserDefinedFunctions].[clrFuncCarParkCounterGet]
GO
So if I write a test query using this function
(Actually a stored procedure is calling this function to add data to a table in another database.):
USE [TestDB]
select * from dbo.clrFuncCarParkCounterGet(null, null)
it returns several rows of data. What I don't understand is this: All the function is doing is just creating a table and returning this new table, but how is it populating the table? I have been using SQL Server for years, writing stored procedures and functions and so on and writing data applications and websites, but I can't understand how the table is being populated. There is some process happening that populates the table with data but I can't figure out what it is. I thought, maybe a trigger or something? I have never seen a trigger operate on a function and there aren't any triggers associated with this DB anyway. I have looked in Microsoft knowledge base articles but I don't really know how to frame the question - what is going on here? Can someone please point me in the right direction? After 15 years or so of SQL Server programming I can't believe there is some fundamental process like this that I have never encountered.
June 18, 2017 at 7:22 pm
That is a CLR table-valued function, so you'd need to look at the assembly it references.
See https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration-database-objects-user-defined-functions/clr-table-valued-functions for a little bit of explanation about this.
Cheers!
June 18, 2017 at 7:34 pm
Jacob Wilkins - Sunday, June 18, 2017 7:22 PMThat is a CLR table-valued function, so you'd need to look at the assembly it references.See https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration-database-objects-user-defined-functions/clr-table-valued-functions for a little bit of explanation about this.
Cheers!
Thank you very much Mr. Wilkins - well that's just an example of an unknown unknown - I had no idea "clr" had such significance I thought it was just a naming convention and had no idea how to search for an answer. Thank you for pointing me in the right direction. This is a totally new area in SQL Server for me.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply