CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Matt Mcdon
Matt Mcdon

Reputation: 305

Behavior dependent on sql server edition

I'm trying to prepare sql script which will be triggered periodically and will behave differently on MS SQL Server Enterprise edition than others. I know that I can check edition by:

SELECT SERVERPROPERTY('edition')

so i thought that it can work like this:

IF EXISTS 
    (SELECT SERVERPROPERTY('edition') as edition WHERE edition LIKE '%Enterprise')
BEGIN
    print 'script for enterprise edition'
END
ELSE
BEGIN
    print 'script for other verisons'
END

but I can't refer to field aliases in the WHERE clause and get error "Invalid column name 'edition'.", so I tried to use CTE:

WITH Test AS (SELECT SERVERPROPERTY('edition') as edition)
SELECT * FROM Test WHERE edition LIKE '%Enterprise'

but it didn't work either ("Argument data type sql_variant is invalid for argument 1 of like function.")

Do you have any suggestion how to achieve behavior dependent on edition?

Upvotes: 2

Views: 154

Answers (2)

Vishal Gajjar
Vishal Gajjar

Reputation: 1019

%Store Results of SERVERPROPERTY to a variable:

DECLARE @Edition SYSNAME
SELECT @Edition = CAST(SERVERPROPERTY('Edition') AS SYSNAME)

IF (@Edition LIKE '%Enterprise%')
BEGIN
    print 'script for enterprise edition'
END
ELSE
BEGIN
    print 'script for other verisons'
END

EDIT: Updated as per comment by @marc_s

Upvotes: 0

dean
dean

Reputation: 10098

Here's the check:

if (cast(serverproperty('edition') as varchar) like '%enterprise%')
    print 'script for enterprise edition'
else
    print 'script for other verisons'

Upvotes: 2

Related Questions