August 1, 2014 at 5:21 am
Hi All,
I want to write a sql query which which check if a table exists , then populate the data from that table else the query should retrieve results from a different table.
I wanted to write something similar to the below query:
SELECT
CASE WHEN (SELECT COUNT(*) FROM databasename.sys.tables WHERE databasename.sys.tables.name= 'tableX')=1
THEN
CASE WHEN c.NAME IS NULL
THEN (ISNULL((SELECT TOP 1 NAME FROM databasename.tableX) cc WHERE cc.cnr=c.ccnr
END),c.cname)))
ELSE
UPPER(c.FinalNAME)
END AS 'Name'
FROM
tableY c
Can anyone help on this? Thanks in advance
August 1, 2014 at 5:41 am
You can use a query in the case statement, simplifies thing
😎
SELECT
CASE
WHEN EXISTS (SELECT * FROM DB_1.sys.tables WHERE name = 'TBL_TRIP') THEN (SELECT TOP 1 name FROM DB_1.sys.tables WHERE name = 'TABLE_NAME')
WHEN EXISTS (SELECT * FROM DB_2.sys.tables WHERE name = 'TBL_TRIP') THEN (SELECT TOP 1 name FROM DB_2.sys.tables WHERE name = 'TABLE_NAME')
ELSE 'NOT FOUND'
END AS DB_CHECK
August 1, 2014 at 5:44 am
Can't you use an IF statement?
IF (
SELECT COUNT(*)
FROM databasename.sys.tables
WHERE databasename.sys.tables.NAME = 'tableX'
) = 1
BEGIN
SELECT TOP 1 NAME
FROM databasename.tableX
WHERE cc.cnr = c.ccnr
END
ELSE
BEGIN
SELECT UPPER(c.FinalNAME) AS 'Name'
FROM tableY c
END
August 1, 2014 at 8:03 am
If the table may or may not exist, you'll have to make references to that table only in dynamic SQL. Otherwise, if the code runs and the table does not exist, you'll get "object not found" errors.
If you're willing to use dynamic SQL to resolve this, let me know and I'll provide specifics on how to do so in your case.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 1, 2014 at 8:31 am
If your query is quite simple, then something like the code below will work.
BEGIN TRY
EXEC('SELECT name FROM sys.databased')
END TRY
BEGIN CATCH
EXEC('SELECT name FROM sys.databases')
END CATCH
If it's a bit more complex, you're probably better off building your command line by line and then executing it at the end, which I imagine is what Scott had in mind.
Do you have any control over the structure of the database, because you shouldn't be having this problem if it's properly designed?
John
August 1, 2014 at 8:34 am
ScottPletcher (8/1/2014)
If the table may or may not exist, you'll have to make references to that table only in dynamic SQL. Otherwise, if the code runs and the table does not exist, you'll get "object not found" errors.If you're willing to use dynamic SQL to resolve this, let me know and I'll provide specifics on how to do so in your case.
Querying the sys.tables or INFORMATION_SCHEMA.TABLES will not error if the table does not exist.
😎
August 1, 2014 at 8:57 am
Eirikur Eiriksson (8/1/2014)
ScottPletcher (8/1/2014)
If the table may or may not exist, you'll have to make references to that table only in dynamic SQL. Otherwise, if the code runs and the table does not exist, you'll get "object not found" errors.If you're willing to use dynamic SQL to resolve this, let me know and I'll provide specifics on how to do so in your case.
Querying the sys.tables or INFORMATION_SCHEMA.TABLES will not error if the table does not exist.
😎
Eirikur Eiriksson (8/1/2014)
ScottPletcher (8/1/2014)
If the table may or may not exist, you'll have to make references to that table only in dynamic SQL. Otherwise, if the code runs and the table does not exist, you'll get "object not found" errors.If you're willing to use dynamic SQL to resolve this, let me know and I'll provide specifics on how to do so in your case.
Querying the sys.tables or INFORMATION_SCHEMA.TABLES will not error if the table does not exist.
😎
That's what your code does. The original code queries the actual table itself.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply