我正在尝试创建一个存储过程,该过程将根据客户的ID作为唯一的输入参数返回客户的全名。全名将是表中包含的名字 + 姓氏。不过,我很确定我的代码有(很多)问题......你能帮我发现问题吗?谢谢!
alter procedure udemy
@businessID int,
@firstname varchar(max) output,
@lastname varchar(max) output,
@fullname varchar(max) output
as
begin
select firstname,lastname
from person.Person
where @businessID=BusinessEntityID and
@firstname=FirstName and
@lastname=LastName
set @fullname=@firstname+@lastname
end
一个存储过程,它将根据客户的 ID 返回客户的全名
只需将其分配给变量
alter procedure udemy
@businessID int,
@fullname varchar(max) output
as
begin
select @fullname=firstname + lastname -- Retuen Full Name
from person.Person
where @businessID=BusinessEntityID --Based on ID
end
然后将您的SP
称为
DECLARE @FullName VARCHAR(MAX); --Declare the OUTPUT parameter
EXEC dbo.udemy 1, @FullName OUTPUT; --Pass an ID and the OUT parameter
SELECT @FullName; --See the results
因此,不需要其他两个参数,除非您也想返回FirstName
和LastName
。
另外,既然您有ID
,为什么在 WHERE
子句中使用它们? 同时为两者指定OUTPUT
。
如果您不想使用 OUTPUT
参数,则
alter procedure udemy
@businessID int
as
begin
select firstname + lastname -- Retuen Full Name
from person.Person
where @businessID=BusinessEntityID --Based on ID
end
您可以在select
中完成所需的所有作业:
alter procedure udemy (
@businessID int,
@firstname varchar(max) output,
@lastname varchar(max) output,
@fullname varchar(max) output
) as
begin
select @firstname = firstname,
@lastname = lastname,
@fullname = @firstname+@lastname
from person.Person p
where BusinessEntityID = @businessID;
end;
您可以尝试以下查询,您需要在 select 中编写输出参数,如下所示。
Create Table Person (BusinessEntityID int, firstname varchar(10), lastname varchar(10))
insert into Person Values (1, 'Suraj', 'Kumar'),(2, 'Deepak', 'Kumar')
go
Create procedure udemy
@businessID int,
@fullname varchar(max) output
as
begin
select @fullname=firstname + lastname
from Person
where BusinessEntityID = @businessID
end
go
DECLARE @get VARCHAR(20);
EXEC udemy 1,@get output
SELECT @get
输出如下SurajKumar
你可以在这里找到现场演示现场演示
你可以使用CONCAT,为什么要经历所有的复杂性。
CONCAT(First_Name, '*space*', Midle_Name,'space', Last_Name) AS Full_Name
这是您的查询已更正
alter procedure udemy
@businessID int,
@firstname varchar(max) output,
@lastname varchar(max) output,
@fullname varchar(max) output
as
begin
select CONCAT(firstname,' ',lastname) AS Fll_Name --Here you go
from person.Person
where @businessID=BusinessEntityID and
@firstname=FirstName and
@lastname=LastName
set @fullname=@firstname+@lastname
end