Posts Tagged user-defined type

Passing structured data to stored procedures

As I wrote about here, I like to pass structured data around using XML (where applicable). While I think it’s the most flexible way, it’s understandable that people might not want to learn XML-parsing syntax, if they don’t have to. Another way of passing data into stored procedures (sprocs) is to use user-defined types (UDTs).

Here’s a simple example:

-- If the UDT already exists, delete it:
IF EXISTS (
	SELECT *
		FROM sys.types
		WHERE is_table_type = 1
		AND [name] = 'ListOfDates'
		AND [schema_id] = SCHEMA_ID('dbo')
	)
BEGIN
	DROP TYPE dbo.ListOfDates
END
GO

-- Create our UDT:
CREATE TYPE dbo.ListOfDates AS TABLE
(
	DateTypeID TINYINT NOT NULL
	,[Date] DATE NOT NULL
)
GO

-- Let's test it out:

SET DATEFORMAT YMD

DECLARE @MyListOfDates AS dbo.ListOfDates

INSERT @MyListOfDates(DateTypeID, [Date])
	VALUES(1, '2016Jan01'),(1, '2016Jan02'),(1, '2016Jan03')
	,(2, '2016Oct10'),(2, '2017Jan01')

SELECT * FROM @MyListOfDates
GO

DateTypeID Date
---------- ----------
1          2016-01-01
1          2016-01-02
1          2016-01-03
2          2016-10-10
2          2017-01-01

Hopefully, that looks straightforward; the way we’ve used it here is not dissimilar to a table variable (e.g. DECLARE @MyTable AS TABLE...), but we can’t pass a table variable to a sproc. We’ll create a test sproc and pass our new user-defined type to it:

-- If the sproc already exists, delete it:
IF OBJECT_ID('dbo.usp_MySproc') IS NOT NULL
BEGIN
	DROP PROCEDURE dbo.usp_MySproc
END
GO

-- Create our sproc:
CREATE PROCEDURE dbo.usp_MySproc
(
	@ListOfDates AS dbo.ListOfDates READONLY
)
AS
BEGIN
	
	SELECT
			DateTypeID
			,MIN([Date]) AS MinDate
			,MAX([Date]) AS MaxDate
		FROM @ListOfDates
		GROUP BY DateTypeID
		ORDER BY DateTypeID

END
GO

-- Test it out:
DECLARE @MyListOfDates AS dbo.ListOfDates
INSERT @MyListOfDates(DateTypeID, [Date])
	VALUES(1, '2016Jan01'),(1, '2016Jan02'),(1, '2016Jan03')
	,(2, '2016Oct10'),(2, '2017Jan01')
	,(3, '2017Feb01'),(3, '2017Feb03'),(3, '2017Feb28')

EXEC dbo.usp_MySproc @ListOfDates = @MyListOfDates
GO

DateTypeID MinDate    MaxDate
---------- ---------- ----------
1          2016-01-01 2016-01-03
2          2016-10-10 2017-01-01
3          2017-02-01 2017-02-28

See the READONLY in the CREATE PROCEDURE? Here’s what happens when you omit it:

The table-valued parameter "@ListOfDates" must be declared with the READONLY option.

This is one of the limitations of user-defined types: they can’t be used to pass data back. So we couldn’t make any changes to @ListOfDates in the sproc, and see those changes reflected outside of the sproc.

There’s another limitation: UDTs can’t be used cross-database. Here’s what happens:

The type name 'MyOtherDatabase.dbo.ListOfDates' contains more than the
maximum number of prefixes. The maximum is 1.

Even if you create the exact same type in another database, with the same name, it won’t work.

(Note that UDTs don’t have to be tables: go here [MSDN] for more info.)


Just for kicks, here’s how I’d replicate the above functionality, but using XML:

-- If the sproc already exists, delete it:
IF OBJECT_ID('dbo.usp_MySproc') IS NOT NULL
BEGIN
	DROP PROCEDURE dbo.usp_MySproc
END
GO

-- Create the sproc:
CREATE PROCEDURE dbo.usp_MySproc
(
	@ListOfDates XML
)
AS
BEGIN
	
	WITH cte_ExtractFromXML AS
	(
		SELECT 
				DateTypeID	= d.i.value('(@type)[1]', 'INT')
				,[Date]		= d.i.value('.[1]', 'DATE')
			FROM @ListOfDates.nodes('//date') d(i)
	)
	SELECT
			DateTypeID
			,MIN([Date]) AS MinDate
			,MAX([Date]) AS MaxDate
		FROM cte_ExtractFromXML
		GROUP BY DateTypeID
		ORDER BY DateTypeID

END
GO

-- Test it with some XML data:
DECLARE @MyListOfDates XML
SET @MyListOfDates = '
<listOfDates>
	<date type="1">2016Jan01</date>
	<date type="1">2016Jan02</date>
	<date type="1">2016Jan03</date>
	<date type="2">2016Oct10</date>
	<date type="2">2017Jan01</date>
	<date type="3">2017Feb01</date>
	<date type="3">2017Feb03</date>
	<date type="3">2017Feb28</date>
</listOfDates>'

EXEC dbo.usp_MySproc @ListOfDates = @MyListOfDates
GO

DateTypeID  MinDate    MaxDate
----------- ---------- ----------
1           2016-01-01 2016-01-03
2           2016-10-10 2017-01-01
3           2017-02-01 2017-02-28

If we wanted to, we could declare our @ListOfDates parameter as OUTPUT, and make changes to it in the sproc, e.g.:

Drop the sproc if it exists:
IF OBJECT_ID('dbo.usp_MySproc') IS NOT NULL
BEGIN
	DROP PROCEDURE dbo.usp_MySproc
END
GO

-- Create our (new and improved) sproc:
CREATE PROCEDURE dbo.usp_MySproc
(
	@ListOfDates XML OUTPUT
)
AS
BEGIN
	
	DECLARE @Aggregated XML

	;WITH cte_ExtractFromXML AS
	(
		SELECT 
				DateTypeID	= d.i.value('(@type)[1]', 'INT')
				,[Date]		= d.i.value('.[1]', 'DATE')
			FROM @ListOfDates.nodes('//date') d(i)
	)
	SELECT @Aggregated = (
		SELECT
				DateTypeID AS '@id'
				,MIN([Date]) AS '@MinDate'
				,MAX([Date]) AS '@MaxDate'
			FROM cte_ExtractFromXML
			GROUP BY DateTypeID
			ORDER BY DateTypeID
			FOR XML PATH('DataType'), ROOT('Aggregated')
	)

	SELECT @ListOfDates = (
		SELECT @ListOfDates, @Aggregated FOR XML PATH('Output'), TYPE
	)

END
GO

-- Run it:
DECLARE @MyListOfDates XML
SET @MyListOfDates = '
<listOfDates>
	<date type="1">2016Jan01</date>
	<date type="1">2016Jan02</date>
	<date type="1">2016Jan03</date>
	<date type="2">2016Oct10</date>
	<date type="2">2017Jan01</date>
	<date type="3">2017Feb01</date>
	<date type="3">2017Feb03</date>
	<date type="3">2017Feb28</date>
</listOfDates>'

EXEC dbo.usp_MySproc @ListOfDates = @MyListOfDates OUTPUT

SELECT @MyListOfDates AS MyListOfDates
GO

<Output>
  <listOfDates>
    <date type="1">2016Jan01</date>
    <date type="1">2016Jan02</date>
    <date type="1">2016Jan03</date>
    <date type="2">2016Oct10</date>
    <date type="2">2017Jan01</date>
    <date type="3">2017Feb01</date>
    <date type="3">2017Feb03</date>
    <date type="3">2017Feb28</date>
  </listOfDates>
  <Aggregated>
    <DataType id="1" MinDate="2016-01-01" MaxDate="2016-01-03" />
    <DataType id="2" MinDate="2016-10-10" MaxDate="2017-01-01" />
    <DataType id="3" MinDate="2017-02-01" MaxDate="2017-02-28" />
  </Aggregated>
</Output>

As you can see, we’ve returned our original list, along with the aggregated data, both under a new parent node.

, , , , ,

Leave a comment