有的時候我們在下語法會需要依照不同的資料庫版本做不同的指令

把取得資料庫版本寫成 function 就會很方便

SET ANSI_NULLS ON
GO 

SET QUOTED_IDENTIFIER ON
GO 

-- =============================================
-- Author: Henry
-- Author URL: http://www.dotblogs.com.tw/henryli/
-- Version: 1.2
-- MSSQL Version: 2000+
-- Create date: 2014-05-29
-- Update date: 2014-05-31
-- Description: 取得SQL Server版本
-- Require: none
-- =============================================
IF OBJECT_ID(N'fn_getSQLServerVersion', N'FN') IS NOT NULL
    DROP FUNCTION [dbo].[fn_getSQLServerVersion];
GO

CREATE FUNCTION [dbo].[fn_getSQLServerVersion]()
RETURNS VARCHAR(4)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @version VARCHAR(4); 

    -- Add the T-SQL statements to compute the return value here
    SELECT @version =
    CASE 
        WHEN LEFT(CAST(serverproperty('productversion') as char), 1) = 8 THEN '2000'
        WHEN LEFT(CAST(serverproperty('productversion') as char), 1) = 9 THEN '2005'
        WHEN LEFT(CAST(serverproperty('productversion') as char), 2) = 10 THEN '2008'
        WHEN LEFT(CAST(serverproperty('productversion') as char), 2) = 11 THEN '2012'
        WHEN LEFT(CAST(serverproperty('productversion') as char), 2) = 12 THEN '2014'
    END 

    -- Return the result of the function
    RETURN @version 

END 

GO

 

使用範例：

SELECT [dbo].[fn_getSQLServerVersion]() AS SQLVersion;

 

參考資料：

How do I drop a function if it already exists?

 

本著作係採用創用 CC 姓名標示-相同方式分享 4.0 國際 授權條款授權，文章歡迎轉載，請註明出處，謝謝~~~