Public Function sThisWeekOpening(sDate As String) As String
    sThisWeekOpening = " DateDiff(ww, DateAdd(d, -2, IsNull([" & sDate & "], DateAdd(wk, 1, GetDate()))), DateAdd(d, -2, GetDate())) > 0"
End Function

Select all Open in new window

    OpenTPSQL = "((" & sThisWeekOpening(IIf(sTable = "view_Openings", "DayStart", "Day"))
    OpenTPSQL = OpenTPSQL & " or (" & sThisWeekOpening("StatusDate") & " or StatusDate is null))"
    OpenTPSQL = OpenTPSQL & " or " & IIf(sTable = "view_Openings", "DayStart", "Day") & " < getdate()-1)"
    OpenTPSQL = OpenTPSQL & " and InActiveStatus is null"

Select all Open in new window

    OpenTPSQL = "((" & sThisWeekOpening(IIf(sTable = "view_Openings", "DayStart", "Day"))
    OpenTPSQL = OpenTPSQL & " or (" & sThisWeekOpening("StatusDate") & " or StatusDate is null))"
    OpenTPSQL = OpenTPSQL & " or " & IIf(sTable = "view_Openings", "DayStart", "Day") & " < getdate()-1)"
    OpenTPSQL = OpenTPSQL & " and InActiveStatus is null"

Select all Open in new window

USE master

GO

DECLARE @DatabaseName VARCHAR(32)   
DECLARE @SQL NVARCHAR(max)
DECLARE @User VARCHAR(64)
SET @User = '[SQL\srvSSISAcc]' --Your User

DECLARE Grant_Permission CURSOR LOCAL FOR
SELECT name FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')  

OPEN Grant_Permission  
FETCH NEXT FROM Grant_Permission INTO @DatabaseName  
WHILE @@FETCH_STATUS = 0  
BEGIN  

    SELECT @SQL = 'USE '+ '[' + @DatabaseName + ']' +'; '+ 'CREATE USER ' + @User + 
    'FOR LOGIN ' + @User + '; EXEC sp_addrolemember N''db_datareader'', 
    ' + @User + '; EXEC sp_addrolemember N''db_datawriter'', ' + @User + ''

    EXEC sp_executesql @SQL
    PRINT @SQL

FETCH NEXT FROM Grant_Permission INTO @DatabaseName  
END  
CLOSE Grant_Permission  
DEALLOCATE Grant_Permission

Select all Open in new window

SELECT DISTINCT  
       personlocation.LocationID,
       phone.PhoneNumber
FROM dbo.personProducts pp
     INNER JOIN dbo.personPhonesLocations phoneLocation ON pp.personLocationRecID = phoneLocation.personLocationRecID
     INNER JOIN dbo.personLocations personlocation ON pp.personLocationRecID = personlocation.personLocationRecID
     INNER JOIN dbo.Phones phone ON phoneLocation.PhoneID = phone.PhoneID
     INNER JOIN dbo.PhoneTypes PhoneTypes ON pl.PhoneTypeID = PhoneTypes.PhoneTypeID
WHERE pp.PersonNumber = 'ABC123'
      AND PhoneTypes.PhoneTypeName = 'Contact Number'

Select all Open in new window

var personPhone = (from pp in dataContext.personProducts
from phoneLocation in dataContext.personPhonesLocations
from personLocation in dataContext.personLocations
from phone in dataContext.Phones
from phoneType in dataContext.PhoneTypes
where pp.PersonNumber == "ABC123" && phoneType.PhoneTypeName == "Contact Number"
select new
{
LocationId = personLocation.LocationID,
phonenumber = phone.PhoneNumber
}).Distinct();


if (personPhone != null)
{
    string locationId = ???; <-- need help
    string phonenumber = ??  <-- need help

}

Select all Open in new window