T-SQL if exists

Question

I am summer intern new to T-SQL and I have to run an sql select statement on various databases. What I would like to do is use 'if exists' to keep an error from occuring because some of the databases on the list to have this statement executed on no longer exist. However, I cannot figure out how to apply it to my statement. Any help would be greatly appreciated. Below is the statment me and another intern wrote:

select distinct mg.MatterName, mg.ClientNumber, mg.MatterNumber,grp.groupName as SecurityGroup
from (select distinct mat.matterName, mat.clientNumber, mat.matterNumber, usr.GroupID
    from <db_name>.dbo.matter mat
    inner join <db_name>.dbo.usrAccount usr
    on usr.NTlogin=mat.matterCreateBy) as mg
          inner join <db_name>.dbo.usrGroup grp
          on mg.groupID=grp.groupID
order by matterName

the < db_name> is where the passed in parameter that is the name of the database, would go.

http://stackoverflow.com/questions/679000/how-to-check-if-a-database-exists-in-sql-server — codingbiz, Jun 13 '12 at 21:14
I guess I must not have expained the part where I tried to simply insert "if exists" around my statement and it didnt work... — Mike, Jun 13 '12 at 21:32

score 1 · Accepted Answer · answered Jun 13 '12 at 21:21

1

You could use sp_MSforeachdb to enumerate all of the databases on the instance.

This would be similar to:

exec sp_MSforeachdb 'select distinct mg.MatterName, mg.ClientNumber, mg.MatterNumber,grp.groupName as SecurityGroup from (select distinct mat.matterName, mat.clientNumber, mat.matterNumber, usr.GroupID     from ?.dbo.matter mat     inner join ?.dbo.usrAccount usr     on usr.NTlogin=mat.matterCreateBy) as mg           inner join ?.dbo.usrGroup grp           on mg.groupID=grp.groupID order by matterName'

Alternatively, you could use dynamic sql to manufacture a script:

select 'use ' + name + ';' + char(13) + 'select distinct mg.MatterName, mg.ClientNumber, mg.MatterNumber,grp.groupName as SecurityGroup' +CHAR(13) + 'from (select distinct mat.matterName, mat.clientNumber, mat.matterNumber, usr.GroupID' + char(13) + 'from dbo.matter mat' + char(13) + 'inner join dbo.usrAccount usr on usr.NTlogin=mat.matterCreateBy) as mg' + char(13) + 'inner join dbo.usrGroup grp on mg.groupID=grp.groupID' + CHAR(13) + 'order by matterName;'
from master.sys.databases where database_id>4

If you redirect your output to "Results to Text" in SSMS then run the script, you will see a script written that you can then put into a query editor to execute.

answered Jun 13 '12 at 21:21

Duncan Howe

2,965
19
18

I think that that would work well, but what is happening is there is the IT department has a list on intranet sites and each site can be clicked to view a log of who accessed it. you can also click a link for each site to see what active 'matters' exist for the site. however some sites are no longer used so the log still exists but the database of 'matters' is gone so when the link to the matters is clicked it errors out. So what I'd like to do is: when the link is clicked and the SQL statement returns that the db doesnt exist it loads a different page saying that the DB no longer exists – Mike Jun 13 '12 at 21:41
I would suggest that the problem here is to do with the application not handling the errors properly. Do you have access to refactor it to call a stored procedure? – Duncan Howe Jun 13 '12 at 21:45
I do have access, but I don't know how to create a stored procedure. that's why I was hoping the 'if exists' would be a quick fix – Mike Jun 14 '12 at 13:47
Apologies Mike, I have only just picked up your message. To create a stored procedure is as simple as typing `CREATE PROCEDURE AS BEGIN END GO`. You could then incorporate the fix mentioned in the solution and if the database doesn't exist you would just return. However, well done with your solution - it is perfectly acceptable with the exception being that any exception thrown would report that the database didn't exist when in fact it may not. May be better to set the text to `ex.Message` – Duncan Howe Jun 16 '12 at 20:15

score 0 · Answer 2 · answered Jun 14 '12 at 15:40

I got it to work. I think this is a bit hackey but what I did was catch the exception thrown and just change a label on the page to reflect that the database doesnt exist.

 DataAccess dal = new DataAccess();
 dal.SelectedConnectionString = "WebServer08";

 String exNetName = Request.QueryString["name"];
 if (exNetName != null && !exNetName.Equals(""))
 {
     try
     {
        gvMatters.DataSource = dal.GetMatters(exNetName);
        gvMatters.DataBind();
      }
      catch (Exception ex)
      {
         noDB.Text = "This database doesn't exist.";
         gvMatters.Visible = false;
      }
 }

And i just left the SQL statement the way it was rather than try to screw around with it

T-SQL if exists

2 Answers2