Is it possible to create a global variable in SQL?

1

Hello,

My company uses a custom application which utilizes multiple (over 12) databases. Some of the columns common to all said databases are:

UserID, UserName

UserID is a unique field as it contains an active directory ID.
UserName contains user's first and last name(s).

Example contents of UserID could be BS9870
Example contents of UserName could be Bob Smith

UserName field may not be unique; there may be multiple
John Smith's for example.

One of my tasks is to search all of the databases to see if a user is present there. Currently, we use a query like the following:

Select UserID, UserName as UserName_From_Northdb
From Northdb
Where UserID like '%BS9870%' or UserName like ''%Smith%'

Select UserID, UserName as UserName_From_Southdb
From Southdb
Where UserID like '%BS9870%' or UserName like ''%Smith%'

Select UserID, UserName as UserName_From_Eastdb
From Eastdb
Where UserID like '%BS9870%' or UserName like ''%Smith%'

etc.

There is one of these 3 line queries per each database. All the queries are ran at the same time.

Is there a way to create two global search variables and do something like the following:

At the top of the script enter in a variable for UserID and a variable for UserName then have the script search each database for that variable? The desired state would be entering in the user's UserID and UserName once at the top of the script and having it propagate to all the relevant queries.

Currently, find and replace (within the menu system of SSMS 2012) is being used to find and replace all instances of %BS9870% with %Active Directory ID of user being searched for%'. Another find and replace is used to replace %Smith% with %Last name of user being searched for%.

Note:

I need to know which database the user is found in and do not want to run each of these 3 line queries one by one, which is why UserName has an alias which includes the database name.

There is likely a much better way to write this script.

Thanks for the advice.

2

Why the % before and after the UserID? I can possibly see it for name.

How about something like[code]DECLARE @UserID VARCHAR(50), @UserName VARCHAR(50);
SELECT @UserID = 'BS9870', @UserName = '%Smith%';

Select UserID, UserName as UserName_From_Northdb
From Northdb
Where UserID like @UserID or UserName like @UserName;

Select UserID, UserName as UserName_From_Southdb
From Southdb
Where UserID like @UserID or UserName like @UserName;

Select UserID, UserName as UserName_From_Eastdb
From Eastdb
Where UserID like @UserID or UserName like @UserName;
[/code]

3

try this

select * from (
Select COALESCE( North.UserID,South.UserID,East.UserID,west.UserID)UserID ,
COALESCE( North.UserName,South.UserName,East.UserName,west.UserName)UserName ,
case when North.UserID IS not null then 'North'
when South.UserID IS not null then 'South'
when East.UserID IS not null then 'East'
when West.UserID IS not null then 'West'
end UserIDDBname,
case when North.UserName IS not null then 'North'
when South.UserName IS not null then 'South'
when East.UserName IS not null then 'East'
when West.UserName IS not null then 'West'
end UserNameDBname

From NorthdbName ..Tablename North
full outer join SouthdbName ..Tablename South on North.UserID = South.UserID
full outer join EastdbName ..Tablename East on North.UserID = East.UserID
full outer join westdbName ..Tablename west on North.UserID = west.UserID
) as dt
Where UserID like '%BS9870%' or UserName like '%Smith%'

4

Thank you both very much! Saved me hours and hours of work!

Because we have user id variations based on account type within the Active Directory environment. For example, a user ID could be something like:

BS9870
ADM.XYZ.BS9870

  • where ADM is an admin account, xyz represents a company code, and BS9870 is a standard account. There are a few different .. styles.