MySQL vs SQLSERVER语法，用于选择声明的变量
我需要验证尚未输入客户，在mysql中，我可以通过在下面的变量（@V_CustomerID）中选择客户ID来做到这一点。但是，ssms不喜欢该语法，还有另一种方法可以实现此目的吗？
Create procedure AddCustomer(
@CustomerFirstName as varchar(50),
@CustomerLastName as varchar(50),
@SiteName as varchar(50),
@CustomerPhone1 as varchar(20),
@CustomerPhone2 as varchar(20),
@CustomerAddress1 as varchar(20),
@CustomerAddress2 as varchar(20),
@CustomerCity as varchar(50),
@CustomerState as varchar(5),
@CustomerZip as char(5)
)
as
begin
declare @V_CustomerID as int,--use this to store the returned value
@V_CustomerExists as bit --use this to store the result of the if else condition
--First we should check if the customer data already exists in the table--
select dbo.customer.CustomerID
into @V_CustomerID
from
dbo.Customer
where
dbo.customer.FirstName = @CustomerFirstName and dbo.customer.LastName = @CustomerLastName and dbo.customer.sitename = @SiteName;
if @V_CustomerID is null
begin
--Yay! we can insert the customer--
insert into Customer
values(@CustomerFirstName,@CustomerLastName,@SiteName);
end
else
begin
set @V_CustomerExists = 1
print 'Customer already exists';
end
end
展开
收起
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
阿里云 AI 助理回答
您的问题似乎是关于在SQL Server Management Studio (SSMS) 中使用变量存储查询结果，并基于该结果执行不同的逻辑。您尝试的代码中，直接将查询结果赋值给声明的变量时，使用的语法不正确。在T-SQL中，可以使用SET或SELECT语句为变量赋值，但当从查询中获取值时，应使用SELECT @variable = column FROM ...的形式。
下面是修改后的存储过程，修正了赋值语法并保持原有逻辑：
CREATE PROCEDURE AddCustomer
(
@CustomerFirstName AS VARCHAR(50),
@CustomerLastName AS VARCHAR(50),
@SiteName AS VARCHAR(50),
@CustomerPhone1 AS VARCHAR(20),
@CustomerPhone2 AS VARCHAR(20),
@CustomerAddress1 AS VARCHAR(20),
@CustomerAddress2 AS VARCHAR(20),
@CustomerCity AS VARCHAR(50),
@CustomerState AS VARCHAR(5),
@CustomerZip AS CHAR(5)
)
AS
BEGIN
DECLARE @V_CustomerID AS INT, -- Use this to store the returned value
@V_CustomerExists AS BIT -- Use this to store the result of the IF ELSE condition
-- First we should check if the customer data already exists in the table --
SELECT @V_CustomerID = CustomerID
FROM dbo.Customer
WHERE dbo.customer.FirstName = @CustomerFirstName
AND dbo.customer.LastName = @CustomerLastName
AND dbo.customer.sitename = @SiteName;
IF @V_CustomerID IS NULL
BEGIN
-- Yay! We can insert the customer --
INSERT INTO Customer
VALUES (@CustomerFirstName, @CustomerLastName, @SiteName);
END
ELSE
BEGIN
SET @V_CustomerExists = 1;
PRINT 'Customer already exists';
END
END
这段代码应该可以在SSMS中正常运行。它首先尝试从dbo.Customer表中根据提供的名字、姓氏和站点名找到匹配的客户ID，如果找到（即@V_CustomerID不是NULL），则打印消息表示客户已存在；如果未找到，则插入新客户记录。
有帮助
无帮助
AI 助理回答生成答案可能存在不准确，仅供参考
问答标签：
相关问答