如何确保存储过程的正确返回值。 [英] How to ensure the correct return values from a stored procedure.

问题描述

我在SQL中创建了一个名为'Names'的表，其中包含以下2列

I have created a table called 'Names' in SQL with the following 2 columns

ID	AUTO ID
NAME	AS nVarchar(MAX)

如果该名称不存在，则我有一个存储过程可以在其中添加名称/>

I have a stored procedure to add Name in it if that name doesn't exist already as

CREATE PROCEDURE [dbo].[Ins_NameNew] 
(
	@Name_Input	varChar(Max)
)
AS 
BEGIN
	DECLARE @NameExist INT
	DECLARE @NameID INT
	DECLARE @Action NVARCHAR(5)
	
	-- Check if the name exists
	SET @NameExist =
			CASE
				WHEN EXISTS (SELECT * FROM [dBName].[dbo].[Names] WHERE Name = @Name_Input )
				THEN 1
				ELSE 0
			END
	
	-- Proceed with Insert if doesn't exist
	IF @NameExist = 0

	BEGIN
			INSERT INTO [dBName].[dbo].[Names]
			(Name)
			VALUES
				(@Name_Input);
	END
	
	-- Get the ID whether Name existed already or just added	
	SET @NameID = (SELECT ID FROM [dBName].[dbo].[Names] WHERE Name = @Name_Input)
	
	
	SET @Action =
		CASE
			WHEN @NameExist = 0
			THEN 'Added'
			ELSE 'Exist'
		END

	SELECT @NameExist, @NameID, @Action
END

这在添加新名称时工作正常。但每次它将@NameExist返回为1并将@Action返回为'Exist'



任何我存储过程中出错的建议。



我的尝试：

This works fine when it comes to adding the new name. But each time it returns @NameExist as 1 and @Action as 'Exist'

Any suggestions where I am going wrong with the stored proc.

What I have tried:

CREATE PROCEDURE [dbo].[Ins_NameNew] 
(
	@Name_Input	varChar(Max)
)
AS 
BEGIN
	DECLARE @NameExist INT
	DECLARE @NameID INT
	DECLARE @Action NVARCHAR(5)
	
	-- Check if the name exists
	SET @NameExist =
			CASE
				WHEN EXISTS (SELECT * FROM [dBName].[dbo].[Names] WHERE Name = @Name_Input )
				THEN 1
				ELSE 0
			END
	
	-- Proceed with Insert if doesn't exist
	IF @NameExist = 0

	BEGIN
			INSERT INTO [dBName].[dbo].[Names]
			(Name)
			VALUES
				(@Name_Input);
	END
	
	-- Get the ID whether Name existed already or just added	
	SET @NameID = (SELECT ID FROM [dBName].[dbo].[Names] WHERE Name = @Name_Input)
	
	
	SET @Action =
		CASE
			WHEN @NameExist = 0
			THEN 'Added'
			ELSE 'Exist'
		END

	SELECT @NameExist, @NameID, @Action
END

推荐答案

您好，



似乎您的ID是一个标识列，您可以通过这样的操作轻松地简单地编写代码。

Hello,

Seems that your ID is an Identity column, you could easily simply your code by doing something like this.

CREATE PROCEDURE [dbo].[Ins_NameNew] 
(
	@Name_Input	varChar(Max)
)
AS 
BEGIN
	DECLARE @NameExist INT = 0
	DECLARE @NameID INT

	-- Proceed with Insert if doesn't exist
	IF EXISTS (SELECT * FROM [dBName].[dbo].[Names] WHERE Name = @Name_Input)
	BEGIN
			INSERT INTO [dBName].[dbo].[Names]
			(Name)
			VALUES
				(@Name_Input);
                       SET @NameID = @@IDENTITY;
	END
	ELSE
        BEGIN
                -- Get the ID whether Name existed already or just added, )
                -- remove top 1 if you Name is unique
	       SET @NameID = (SELECT TOP 1 ID FROM [dBName].[dbo].[Names] WHERE Name = @Name_Input)
                SET @NameExist = 1;
        END

 
	SELECT @NameExist, 
                      @NameID, 
                CASE
			WHEN @NameExist = 0
			THEN 'Added'
			ELSE 'Exist'
		END as 'Action'
END

这篇关于如何确保存储过程的正确返回值。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！