SQL 服务器 : return string procedure INITCAP

Question

这就是我所做的。

create proc INITCAP(@string varchar(30))
as
begin
    SET @string = UPPER(LEFT(@string,1)) + LOWER(RIGHT(@string, LEN(@string) -1))
end

declare @lastname varchar
set @lastname = exec INITCAP 'MILLER'

declare @firstname varchar
set @firstname = exec INITCAP 'StEvE'

UPDATE Employee SET firstname = @firstname, lastname = @lastname WHERE empID = 7934

我不断收到错误:

Msg 156, Level 15, State 1, Procedure INITCAP, Line 97
Incorrect syntax near the keyword 'exec'.
Msg 156, Level 15, State 1, Procedure INITCAP, Line 100
Incorrect syntax near the keyword 'exec'.

我该怎么办？我希望过程 INITCAP 像在 Oracle 中一样工作:返回一个名称，例如:“Steve”、“Miller”

Answer 1

解决方案#1(我不会使用这个解决方案)

您可以这样使用 OUTPUT 参数:

create proc INITCAP(@string varchar(30) OUTPUT)
as
begin
    SET @string = UPPER(LEFT(@string,1)) + LOWER(SUBSTRING(@string, 2, 8000))
end
go 

declare @lastname varchar
set @lastname = 'MILLER'
exec INITCAP @lastname OUTPUT

declare @firstname varchar
set @firstname = 'StEvE'
exec INITCAP @firstname OUTPUT

解决方案 #2:相反，我会选择这样创建一个内联函数:

CREATE FUNCTION dbo.Capitalize1(@string varchar(30))
RETURNS TABLE
AS
RETURN
SELECT UPPER(LEFT(@string,1)) + LOWER(SUBSTRING(@string, 2, 8000)) AS Result;

用法:

UPDATE e 
SET firstname = cap.Result
FROM Employee e
CROSS APPLY dbo.Capitalize1(e.firstname) cap;

解决方案#3:另一种选择可能是标量函数带有模式绑定(bind)选项(出于性能原因):

CREATE FUNCTION dbo.Capitalize2(@string varchar(30))
RETURNS VARCHAR(30)
WITH SCHEMABINDING
AS
BEGIN
    RETURN UPPER(LEFT(@string,1)) + LOWER(SUBSTRING(@string, 2, 8000));
END;

用法:

UPDATE Employee
SET firstname = dbo.Capitalize2(firstname);