April 8, 2011 at 7:39 am
I created a UDF which returns the result of a Union query as a table. The UNION query gets data from as many as 50 tables to return history related to a person. My goal was to have all the data retrieval encapsulated into one function. However, it is possible that some of our customers may not have all 50 tables in thier database so the function can not be created or fails when there are missing tables. The following is a condensed version of what I have created. I need to know if there is a way to modify this to check for the existence of each table and to only include those tables that exist into the Union query. I am under a tight deadline so any help would be appreciated.
CREATE Function [dbo].[MNIHist2](@MNI_ID int)
RETURNs TABLE
AS
RETURN(
SELECT CAST(Arrest.Arrest_ID AS nvarchar(50)) AS ID, CAST(Arrest.Arrest_No AS nvarchar(50)) AS ReferenceNo, Arrest.OArrest_Date AS HistoryDate, 'Arrest' AS Category, Arrest_Offense.Offense AS Offense, '' AS Other, (CASE WHEN ISNUMERIC([Oage]) =1 THEN COALESCE([Oage],0) ELSE 0 END) AS Age, [ORI] AS MNIORI, Arrest.MNI_No AS MNI_No, 'Arrest' AS FormName, 'Arrest_ID' AS KeyFieldName , 'RMS' AS ModuleSource
FROM Arrest LEFT JOIN Arrest_Offense ON Arrest.Arrest_ID = Arrest_Offense.Arrest_ID
WHERE (((Arrest.Arrest_No)>'')) AND MNI_No = @MNI_ID AND EXISTS(SELECT TABLE_Name FROM Information_Schema.tables WHERE Table_name = 'Arrest')
UNION ALL SELECT CAST(Dispatch.Dis_ID AS nvarchar(50)) AS ID, CAST(Dispatch.Dis_No AS nvarchar(50)) AS ReferenceNo, Dispatch.Dis_Date AS HistoryDate, 'CAD: Location' AS Category, '' AS Offense, 'Case Number: ' + [Case_Number] AS Other, (CASE WHEN ISNUMERIC([Oage]) =1 THEN COALESCE([Oage],0) ELSE 0 END) AS Age, Dispatch.Dis_ORI AS MNIORI, Dispatch.MNI_No AS MNI_No, 'frmDispatch' AS FormName, 'MNI_No' AS KeyFieldName, 'RMS' AS ModuleSource
FROM Dispatch LEFT JOIN MNI ON Dispatch.MNI_No = MNI.MNI_ID WHERE MNI_No = @MNI_ID AND EXISTS(SELECT TABLE_Name FROM Information_Schema.tables WHERE Table_name = 'Dispatch')
UNION ALL SELECT CAST(Dispatch.Dis_ID AS nvarchar(50)) AS ID, Dispatch.Dis_No AS ReferenceNo, Dispatch.Dis_Date AS HistoryDate, 'CAD: ' + [Type] AS Category, '' AS Offense, 'Case Number: ' + [Case_Number] AS Other, (CASE WHEN ISNUMERIC([Oage]) =1 THEN COALESCE([Oage],0) ELSE 0 END) AS Age, Dispatch.Dis_ORI AS MNIORI, Dispatch_Persons.MNI_No AS MNI_No, 'frmDispatch' AS FormName, 'MNI_No' AS KeyFieldName , 'RMS' AS ModuleSource
FROM (Dispatch_Persons INNER JOIN Dispatch ON Dispatch_Persons.Dis_ID = Dispatch.Dis_ID) LEFT JOIN MNI ON Dispatch_Persons.MNI_No = MNI.MNI_ID WHERE Dispatch_Persons.MNI_No = @MNI_ID AND EXISTS(SELECT TABLE_Name FROM Information_Schema.tables WHERE Table_name = 'Dispatch')
UNION ALL SELECT CAST(CFS.CFS_ID AS nvarchar(50)) AS ID, CFS.CFS_No AS Reference_No, CFS.CFSDate AS HistoryDate, 'CFS: Location >' AS Category, COALESCE(LAddress,'') + ', ' + COALESCE(LCity,'') + ', ' + COALESCE(LState,'') + ' ' + COALESCE(LZip,'' ) AS Offense, 'Case Number: ' + [Case_No] AS Other, (CASE WHEN ISNUMERIC([Oage]) =1 THEN COALESCE([Oage],0) ELSE 0 END) AS Age, CFS.ORI AS MNIORI, CFS.MNI_ID AS MNI_No, 'CFS' AS FormName, 'CFS_ID' AS KeyFieldName , 'RMS' AS ModuleSource
FROM CFS LEFT JOIN MNI ON CFS.MNI_ID = MNI.MNI_ID
WHERE (((CFS.MNI_Type)='I')) AND CFS.MNI_ID = @MNI_ID AND EXISTS(SELECT TABLE_Name FROM Information_Schema.tables WHERE Table_name = 'CFS')
)
GO
April 8, 2011 at 10:35 am
I'm not sure if the all-in-one approach is the best solution here, especially when talking about 50 tables used in a UNION ALL statement...
I'd probably prefer storing the data from each query in a temp table.
This would make it easier to control whether a specific query needs to run or not.
Something like:
IF EXISTS(SELECT TABLE_Name FROM Information_Schema.tables WHERE Table_name = 'Arrest')
BEGIN
INSERT INTO #table
SELECT
CAST(Arrest.Arrest_ID AS nvarchar(50)) AS ID,
CAST(Arrest.Arrest_No AS nvarchar(50)) AS ReferenceNo,
Arrest.OArrest_Date AS HistoryDate, 'Arrest' AS Category,
Arrest_Offense.Offense AS Offense, '' AS Other,
(CASE WHEN ISNUMERIC([Oage]) =1 THEN COALESCE([Oage],0) ELSE 0 END) AS Age,
[ORI] AS MNIORI, Arrest.MNI_No AS MNI_No,
'Arrest' AS FormName, 'Arrest_ID' AS KeyFieldName , 'RMS' AS ModuleSource
FROM Arrest LEFT JOIN Arrest_Offense ON Arrest.Arrest_ID = Arrest_Offense.Arrest_ID
WHERE (((Arrest.Arrest_No)>'')) AND MNI_No = @MNI_ID
END
April 8, 2011 at 11:44 am
Thanks for your reply!
I kinda thought that a temp table would be a good way to go. I have not been able to find the syntax to retrieve the records from the temp table to create the SELECT statement in the function so that it returns the recordset. I'm pretty new to SQL so would it be possible for you to give me an example of how to iterate through the temp table to construct the Union Query? I am familiar with how to do this in VB but I have never done this in SQL server.
I would really like to do this all in SQL Server rather using external code. Your help would be greatly appreciated.
April 8, 2011 at 3:33 pm
I found what I was looking for. Rather than trying to construct the UNION query I just ran each SELECT statement in code and inserted the returned data to a table variable. The table with data is returned by the function.
I was able to use the If EXIST construct to test whether the table exists and if not that Select query is not run.
Here's what I used:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_MNIHist]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_MNIHist]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION fn_MNIHist(@MNI_ID varchar(50))
RETURNS @returntable TABLE (ID varchar(50),
ReferenceNo varchar(50),
HistoryDate varchar(50),
Category varchar(50),
Offense varchar(50),
Other varchar(50),
Age int,
MNIORI varchar(50),
MNI_NO varchar(50),
FormName varchar(50),
KeyFieldName varchar(50),
ModuleSource varchar(50)
)
AS
BEGIN
If Exists(SELECT TABLE_Name from Information_Schema.tables WHERE Table_Name = 'Arrest')
INSERT @returntable
SELECT CAST(Arrest.Arrest_ID AS nvarchar(50)) AS ID,
CAST(Arrest.Arrest_No AS nvarchar(50)) AS ReferenceNo,
Arrest.OArrest_Date AS HistoryDate, 'Arrest' AS Category,
Arrest_Offense.Offense AS Offense, '' AS Other,
(CASE WHEN ISNUMERIC([Oage]) =1 THEN COALESCE([Oage],0) ELSE 0 END) AS Age,
[ORI] AS MNIORI, Arrest.MNI_No AS MNI_No,
'Arrest' AS FormName, 'Arrest_ID' AS KeyFieldName , 'RMS' AS ModuleSource
FROM Arrest LEFT JOIN Arrest_Offense ON Arrest.Arrest_ID = Arrest_Offense.Arrest_ID
WHERE (((Arrest.Arrest_No)>'')) AND MNI_No = @MNI_ID
If Exists(SELECT TABLE_Name from Information_Schema.tables WHERE Table_Name = 'MNI_Alias')
INSERT @returntable
SELECT MNI_Alias.ID AS ID, '' AS ReferenceNo, '' AS HistoryDate, 'MNI Alias >' AS Category,
(COALESCE([Last_Name],'') + ', ' +
COALESCE([First_Name],'') + ' ' +
COALESCE([Middle_Name],''))
As Offense, '' AS Other, 0 AS Age, '' AS MNIORI, MNI_No AS MNI_No, 'MNI' AS FormName, 'MNI_ID' AS KeyFieldName, 'RMS' AS ModuleSource
FROM MNI_Alias WHERE IsAliasName = -1 AND MNI_No = @MNI_ID AND EXISTS(SELECT TABLE_Name FROM Information_Schema.tables WHERE Table_name = 'MNI_Alias')
If exists(SELECT TABLE_Name from Information_Schema.tables WHERE Table_Name = 'tblDOBHist')
INSERT @returntable
SELECT CAST(tblDOBHist.DOB_ID AS nvarchar(50)) AS ID
, '' AS ReferenceNo
, DateChanged AS HistoryDate
, 'MNI Alternate DOB' AS Category
, '(' + CONVERT(nvarchar(12), tblDOBHist.ODOB, 101) + ') changed to (' + CONVERT(nvarchar(12),tblDOBHist.NDOB, 101) + ')' AS Offense
, '' AS Other
, '' AS Age
, '' AS MNIORI
, MNI_No AS MNI_No
, 'MNI' AS FormName
, 'MNI_ID' AS KeyFieldName
, 'RMS' AS ModuleSource
FROM
tblDOBHist WHERE MNI_No = @MNI_ID AND EXISTS(SELECT TABLE_Name FROM Information_Schema.tables WHERE Table_name = 'tblDOBHist')
If exists(SELECT TABLE_Name from Information_Schema.tables WHERE Table_Name = 'Warrants')
INSERT @returntable
SELECT Warrants.Warrant_ID AS ID, Warrants.Warrant_No AS ReferenceNo, Warrants.WDate AS HistoryDate, 'Warrant' AS Category, Warrants_Offense.Offense, Warrants.Remarks AS Other, (CASE WHEN ISNUMERIC([Oage]) =1 THEN COALESCE([Oage],0) ELSE 0 END) AS Age, ORI AS MNIORI,MNI_No AS MNI_No, 'Warrants' AS FormName, 'Warrant_ID' AS KeyFieldName , 'RMS' AS ModuleSource FROM Warrants LEFT JOIN Warrants_Offense ON Warrants.Warrant_ID = Warrants_Offense.Warrant_ID WHERE MNI_No = @MNI_ID AND EXISTS(SELECT TABLE_Name FROM Information_Schema.tables WHERE Table_name = 'Warrants')
Return
END
go
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Thanks again for your help.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply