SQL Server:返回字符串过程 INITCAP [英] SQL Server : return string procedure INITCAP

问题描述

这就是我所做的.

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

我不断收到错误:

消息 156，级别 15，状态 1，过程 INITCAP，第 97 行
关键字exec"附近的语法不正确.
消息 156，级别 15，状态 1，过程 INITCAP，第 100 行
关键字exec"附近的语法不正确.

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"

What shall I do? I want the procedure INITCAP to work as it does in Oracle: to return a name like: "Steve", "Miller"

推荐答案

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

Solution #1 (I wouln't use this solution)

您可以这样使用 OUTPUT 参数:

You could use OUTPUT parameters thus:

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:相反，我会选择创建一个内联函数:

Solution #2: Instead, I would choose to create an inline function thus:

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:另一个选项可能是标量函数with schemabinding 选项(出于性能原因):

Solution #3: Another option could be a scalar function with schemabinding option (for performance reasons):

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);

这篇关于SQL Server:返回字符串过程 INITCAP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！