SQL Conditional Union Across Databases

Asked Oct 26 '18 at 20:38

Active Oct 29 '18 at 15:33

Viewed 32 times

I have a view with 3 unions combining datasets from 4 different databases. It works fine if the user has access to all 4, but it does fail if a user doesn't have access to any one of the databases queried. Is there a way to have the query return the data from only the databases they have access to without failing?

Something like this:

IF USERACCESS TO DB1 = TRUE
(SELECT A,B,C
FROM DB1..TABLE1
UNION ALL
)
IF USERACCESS TO DB2 = TRUE
(SELECT A,B,C
FROM DB2..TABLE1
UNION ALL
)
IF USERACCESS TO DB3 = TRUE
(SELECT A,B,C
FROM DB3..TABLE1
)

edited Oct 29 '18 at 15:33

asked Oct 26 '18 at 20:38

C-COOP

You would have to use dynamic SQL. – Gordon Linoff Oct 26 '18 at 20:41
Could you check their access? https://stackoverflow.com/questions/26917508/check-postgres-access-for-a-user – Josh Oct 26 '18 at 20:52
@a_horse_with_no_name Sorry, SQL Server – C-COOP Oct 29 '18 at 15:35
If exists(select * from table) – danny117 Oct 29 '18 at 20:45

SQL Conditional Union Across Databases

0 Answers0