要检查是否存在的SQL存储过程，然后添加.如果不更新

Question

我正试图编写一个存储过程来填充用户表（命名为CSLL.Users）

该过程在触发时会提供一系列用户详细信息。

然后，它会检查表中是否存在Alias。如果不是，则添加别名。然后sp继续填充其余列（使用update命令）

它是使用vba调用的，但在调用时抛出了一个错误。我认为这是第一个IF NOT EXISTS语句，但看不出哪里可能出错。

ALTER procedure [CSLL].[UploadUser]
    -- Add the parameters for the stored procedure here
    @Name varchar(max) = NULL,
    @FirstName varchar(max) = NULL,
    @LastName varchar(max) = NULL,
    @Email varchar(max) = NULL,
    @Telephone varchar(max) = NULL,
    @Division varchar(max) = NULL,
    @Manager varchar(max) = NULL,
    @DirectReports varchar(max) = NULL,
    @Title varchar(max) = NULL
AS
SET NOCOUNT ON
IF NOT EXISTS(SELECT 1 FROM [CSLL].[Users] Where [Alias] = @Name) INSERT INTO [CSLL.Users] ([Alias], [Country], [Role]) VALUES (@Name, 'UK', 'Employee')
UPDATE [CSLL].[Users]
    SET FirstName = @FirstName,
        LastName = @LastName,
        Email = @Email,
        Telephone = @Telephone,
        Division = @Division,
        Manager = @Manager,
        DirectReports = @DirectReports,
        Title = @Title
WHERE Alias = @Name
DECLARE @Pos int
DECLARE @DR varchar(max)
SET @DirectReports = @DirectReports + ','
WHILE LEN(@DirectReports) > 0
    BEGIN
    SET @Pos = CHARINDEX(',', @DirectReports, 1)
    SET @DR = (CASE @Pos
        WHEN 0 THEN RTRIM(LTRIM(@DirectReports))
        ELSE RTRIM(LTRIM(LEFT(@DirectReports, @Pos - 1)))
    END)
    IF NOT EXISTS(SELECT 1 FROM [CSLL].[Users] where [Alias] = @DR) INSERT INTO [CSLL].[Users] ([Alias], [Country], [Role]) VALUES (@DR, 'UK', 'Employee')
    SET @DirectReports = RIGHT(@DirectReports, LEN(@DirectReports) - @Pos)
    END

Answer 1

这似乎是由一个拼写错误引起的：

-- you currently use: [CSLL.Users]
IF NOT EXISTS(SELECT 1 FROM [CSLL].[Users] WHERE [Alias] = @Name) 
    INSERT INTO [CSLL.Users] ([Alias], [Country], [Role]) 
    VALUES (@Name, 'UK', 'Employee')

请尝试：

-- use: [CSLL].[Users]
IF NOT EXISTS(SELECT 1 FROM [CSLL].[Users] Where [Alias] = @Name) 
    INSERT INTO [CSLL].[Users] ([Alias], [Country], [Role]) 
    VALUES (@Name, 'UK', 'Employee')