Nov
10
2008

SQL Joins and Applies – Test Data

I’m writing a four part series on Joins and Applies in SQL.

Below is the T-SQL to create the two tables and a function used to explain SQL Joins and Applies.

--CREATE A TABLE VALUED FUNCTION
--FUNCTION IS ONLY USED FOR CROSS AND OUTER APPLY
--CREATE A TABLE VALUED FUNCTION
--FUNCTION IS ONLY USED FOR CROSS AND OUTER APPLY
CREATE FUNCTION [dbo].[udfGet5Rows](@inputInteger INT)
RETURNS @t TABLE ([calculatedValue] INT) AS
BEGIN
	IF @inputInteger IS NOT NULL
	BEGIN
		INSERT INTO @t([calculatedValue])
		SELECT @inputInteger		UNION ALL
		SELECT @inputInteger + 1	UNION ALL
		SELECT NULL			UNION ALL
		SELECT @inputInteger + 3	UNION ALL
		SELECT @inputInteger + 4
 	END
 
	RETURN
END;
GO
--CREATE TWO TABLES
CREATE TABLE [dbo].[tblSourceIntegers]
(
sysID INT NULL,
sourceValue INT NULL
);
GO
 
CREATE TABLE [dbo].[tblSourceStrings]
(
sysID INT NULL,
sourceValue VARCHAR(16) NULL
);
GO
 
--INSERT VALUES INTO THE TABLES
INSERT INTO tblSourceIntegers(sysID,sourceValue)
	SELECT 1,	100		UNION ALL
	SELECT 2,	200		UNION ALL
	SELECT NULL,	NULL		UNION ALL
	SELECT 4,	400		UNION ALL
	SELECT 5,	500;
GO
 
INSERT INTO tblSourceStrings(sysID,sourceValue)
	SELECT 2,	'Apple'		UNION ALL
	SELECT 3,	'Banana'	UNION ALL
	SELECT NULL,	NULL		UNION ALL
	SELECT 5,	'Dog'		UNION ALL
	SELECT 6,	'Egg';
GO

Test your results with:

SELECT * FROM dbo.udfGet5Rows(5);
SELECT * FROM dbo.tblSourceIntegers;
SELECT * FROM tblSourceStrings;

Your results should be:

calculatedValue
---------------
5
6
NULL
8
9

(5 row(s) affected)

sysID       sourceValue
----------- -----------
1           100
2           200
NULL        NULL
4           400
5           500

(5 row(s) affected)

sysID       sourceValue
----------- ----------------
2           Apple
3           Banana
NULL        NULL
5           Dog
6           Egg

(5 row(s) affected)
Written by John in: SQL Tips,Tips |

No Comments »

RSS feed for comments on this post. TrackBack URL

Leave a comment