If, else - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

If, else

Author

Topic

lane0618
Posting Yak Master

134 Posts

Posted - 2002-10-21 : 19:05:14

How do I convert the following statement to work in SQL? I want the fintemp1 table to be dropped only id it exists.

if not fintemp1.EOF
begin
drop table fintemp1
end

Thanks,
Lane

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-10-21 : 19:57:52

Something like this:


CREATE TABLE #Data(ID INT)
CREATE TABLE #DeleteMe(MyField VARCHAR(50))

INSERT INTO #Data(ID) VALUES(1234)
INSERT INTO #Data(ID) VALUES(5678)

DECLARE @ID INT
SET @ID = 1234
--SET @ID = 1111

IF (SELECT 1 FROM #Data WHERE ID = @ID) = 1
	BEGIN
		DROP TABLE #DeleteMe
	END

SELECT * from #DELETEME

DROP TABLE #Data
--DROP TABLE #DELETEME

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-21 : 22:33:28

[q] I want the fintemp1 table to be dropped only idif it exists[/q]

Your question says that you want to drop a table only if it exists.
Your example drops a table if there is no data in a recordset.
Not quite sure what you want, you could be looking to drop a table if it contains a certain set or if it exists.
MichaelP's answers the first here is the answer to the second.

You can check the existance of tables by using the INFORMATION_SCHEMA VIEWS.

IF (
SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = 'fintemp1'
) = 1
BEGIN
DROP TABLE fintemp1
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources