解决方案#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;
Usage:
UPDATE e
SET firstname = cap.Result
FROM Employee e
CROSS APPLY dbo.Capitalize1(e.firstname) cap;
解决方案#3:另一个选择可能是标量函数with schemabinding
选项(出于性能原因):
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;
Usage:
UPDATE Employee
SET firstname = dbo.Capitalize2(firstname);