CODEWITHSUNDEEP
sql-server
Samsam
Samsam

Reputation: 95

SQL Server query if then logic

IF (EXISTS (SELECT name 
            FROM master.dbo.sysdatabases 
            WHERE name = 'db'))
THEN
    ALTER DATABASE 'db' SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;

Sorry for the lame question but all I want to do is if the database exist then alter it

Upvotes: 0

Views: 61

Answers (3)

PSK
PSK

Reputation: 17943

Another approach can be like following.

if db_id('db') is not null
begin
 ALTER DATABASE db SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE; 
end

Note that with ALTER DATABASE you need to specify the db name as literal, 'db' will not work.

Correct : ALTER DATABASE db SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;

You will get Error : ALTER DATABASE 'db' SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;

Upvotes: 1

ℛɑƒæĿᴿᴹᴿ
ℛɑƒæĿᴿᴹᴿ

Reputation: 5326

Samsam, you can try this:

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = 'db')
BEGIN
    SELECT 'Database Name already Exist' AS Message
END
ELSE
BEGIN
    ALTER DATABASE 'db' SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE; 
END

Reference:

Upvotes: 1

Richardissimo
Richardissimo

Reputation: 5765

T-Sql doesn't have a then keyword as part of the if statement. Source https://learn.microsoft.com/en-us/sql/t-sql/language-elements/if-else-transact-sql?view=sql-server-2017. Just remove the word 'then'.

Upvotes: 2

Related Questions