Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE [SDS_DevSchoolDistrict]
- GO
- /****** Object: UserDefinedFunction [dbo].[WidgetDataForQuickLinksAsXml] Script Date: 11/28/2011 20:10:24 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --SELECT dbo.WidgetDataForQuickLinksAsXml()
- CREATE FUNCTION [dbo].[WidgetDataForLoginsAndLinksAsXml]
- (
- @ApplicationUserId int,
- @EncryptIds bit = 1
- )
- RETURNS Xml
- AS
- BEGIN
- ---------------------------------------------
- -- Prepare for performance metrics
- ---------------------------------------------
- DECLARE @XmlAssemblyStartTime time
- DECLARE @XmlAssemblyEndTime time
- DECLARE @XmlAssemblyTimeInMilliseconds int
- SET @XmlAssemblyStartTime = GETDATE()
- ---------------------------------------------
- ---------------------------------------------
- -- Declare the return variable here
- ---------------------------------------------
- DECLARE @Xml Xml
- SET @Xml = '<Xml xmlAssemblyTimeInMilliseconds=""></Xml>'
- ---------------------------------------------
- ---------------------------------------------
- DECLARE @QuickLinks Xml
- ---------------------------------------------
- SET @QuickLinks = (
- SELECT QL.QuickLinkId AS '@QuickLinkId',
- QL.QuickLinkText AS '@QuickLinkText',
- QL.QuickLinkOrdinal AS '@QuickLinkOrdinal'
- FROM QuickLinks QL
- FOR XML PATH('QuickLink'), ROOT('QuickLinks')
- )
- ---------------------------------------------
- --SELECT @QuickLinks AS '@QuickLinks'
- ---------------------------------------------
- ---------------------------------------------
- DECLARE @AutomatedLoginAccounts Xml
- ---------------------------------------------
- IF(@EncryptIds = 0)
- BEGIN
- SET @AutomatedLoginAccounts = (
- SELECT Ap.AutomatedLoginApplicationId AS '@AutomatedLoginApplicationId',
- Ap.AutomatedLoginApplication AS '@AutomatedLoginApplication',
- Ac.AutomatedLoginUserName AS '@AutomatedLoginUserName',
- Ap.AutomatedLoginApplicationLogoFilePath AS '@AutomatedLoginApplicationLogoFilePath',
- Ap.AutomatedLoginApplicationActionFilePath AS '@AutomatedLoginApplicationActionFilePath',
- Ac.AutomatedLoginAccountId AS '@AutomatedLoginAccountId',
- Ac.AutomatedLoginLabel AS '@AutomatedLoginLabel'
- FROM AutomatedLoginApplications Ap
- JOIN AutomatedLoginAccounts Ac
- ON Ap.AutomatedLoginApplicationId = Ac.AutomatedLoginApplicationId
- AND Ac.ApplicationUserId = @ApplicationUserId
- FOR XML PATH('AutomatedLoginAccount'), ROOT('AutomatedLoginAccounts')
- )
- END
- ELSE
- BEGIN
- SET @AutomatedLoginAccounts = (
- SELECT EncryptedAutomatedLoginApplicationIds.EncryptedId AS '@AutomatedLoginApplicationId',
- Ap.AutomatedLoginApplication AS '@AutomatedLoginApplication',
- Ac.AutomatedLoginUserName AS '@AutomatedLoginUserName',
- Ap.AutomatedLoginApplicationLogoFilePath AS '@AutomatedLoginApplicationLogoFilePath',
- Ap.AutomatedLoginApplicationActionFilePath AS '@AutomatedLoginApplicationActionFilePath',
- EncryptedAutomatedLoginAccountIds.EncryptedId AS '@AutomatedLoginAccountId',
- Ac.AutomatedLoginLabel AS '@AutomatedLoginLabel'
- FROM AutomatedLoginApplications Ap
- JOIN EncryptedIds EncryptedAutomatedLoginApplicationIds
- ON Ap.AutomatedLoginApplicationId = EncryptedAutomatedLoginApplicationIds.Id
- JOIN AutomatedLoginAccounts Ac
- ON Ap.AutomatedLoginApplicationId = Ac.AutomatedLoginApplicationId
- JOIN EncryptedIds EncryptedAutomatedLoginAccountIds
- ON Ac.AutomatedLoginAccountId = EncryptedAutomatedLoginAccountIds.Id
- AND Ac.ApplicationUserId = @ApplicationUserId
- FOR XML PATH('AutomatedLoginAccount'), ROOT('AutomatedLoginAccounts')
- )
- END
- ---------------------------------------------
- -- SELECT @AutomatedLoginAccounts AS '@AutomatedLoginAccounts'
- ---------------------------------------------
- ---------------------------------------------
- DECLARE @AutomatedLoginApplications Xml
- ---------------------------------------------
- IF(@EncryptIds = 0)
- BEGIN
- SET @AutomatedLoginApplications = (
- SELECT Ap.AutomatedLoginApplicationId AS '@AutomatedLoginApplicationId',
- Ap.AutomatedLoginApplication AS '@AutomatedLoginApplication'
- FROM AutomatedLoginApplications Ap
- FOR XML PATH('AutomatedLoginApplication'), ROOT('AutomatedLoginApplications')
- )
- END
- ELSE
- BEGIN
- SET @AutomatedLoginApplications = (
- SELECT EncryptedAutomatedLoginApplicationIds.EncryptedId AS '@AutomatedLoginApplicationId',
- Ap.AutomatedLoginApplication AS '@AutomatedLoginApplication'
- FROM AutomatedLoginApplications Ap
- JOIN EncryptedIds EncryptedAutomatedLoginApplicationIds
- ON Ap.AutomatedLoginApplicationId = EncryptedAutomatedLoginApplicationIds.Id
- FOR XML PATH('AutomatedLoginApplication'), ROOT('AutomatedLoginApplications')
- )
- END
- ---------------------------------------------
- --SELECT @AutomatedLoginApplications AS '@AutomatedLoginApplications'
- ---------------------------------------------
- ---------------------------------------------
- -- Assemble output Xml
- ---------------------------------------------
- SET @Xml = dbo.XmlAdopt(@Xml, @AutomatedLoginApplications, 'Xml', 'AutomatedLoginApplication')
- SET @Xml = dbo.XmlAdopt(@Xml, @AutomatedLoginAccounts, 'Xml', 'AutomatedLoginAccount')
- SET @Xml = dbo.XmlAdopt(@Xml, @QuickLinks, 'Xml', 'QuickLink')
- ---------------------------------------------
- --SET @Xml = dbo.XmlNormalize(@Xml)
- ---------------------------------------------
- ---------------------------------------------
- -- Track Assembly time
- ---------------------------------------------
- SET @XmlAssemblyEndTime = GETDATE()
- SET @XmlAssemblyTimeInMilliseconds = DATEDIFF(ms, @XmlAssemblyStartTime, @XmlAssemblyEndTime)
- SET @Xml = dbo.XmlAssignAttributeValues(@Xml, '//Xml', 'xmlAssemblyTimeInMilliseconds', @XmlAssemblyTimeInMilliseconds)
- ---------------------------------------------
- --SELECT @Xml AS '@Xml'
- RETURN @Xml
- END
Add Comment
Please, Sign In to add comment