即使空值保存数据后存储过程

发布于 2024-12-11 17:49:05 字数 648 浏览 0 评论 0原文

ALTER PROCEDURE [dbo].[STL_ADDNEWROLE](@ROLENAME VARCHAR(100), @STATUS BIT)
AS
BEGIN   
    SET NOCOUNT ON;
    IF(@ROLENAME IS NULL OR @STATUS IS NULL)
    BEGIN
       RETURN 0
    END
    ELSE
    BEGIN
       IF EXISTS (SELECT [RoleName], [Status] FROM ST_Roles 
                  WHERE [RoleName] = @ROLENAME)
       BEGIN
          RETURN 0
       END
       ELSE IF(@ROLENAME IS NOT NULL)
       BEGIN
          INSERT INTO ST_Roles ([RoleName], [Status]) VALUES(@ROLENAME, @STATUS)
          RETURN 1
       END
    END
END

上面是我的存储过程。当传递 NULL 值时，它应该返回 0。即使传递 1 个值而未传递另一个值，它也应该返回 0，而不是插入记录。

非常感谢任何帮助。

原文

ALTER PROCEDURE [dbo].[STL_ADDNEWROLE](@ROLENAME VARCHAR(100), @STATUS BIT)
AS
BEGIN   
    SET NOCOUNT ON;
    IF(@ROLENAME IS NULL OR @STATUS IS NULL)
    BEGIN
       RETURN 0
    END
    ELSE
    BEGIN
       IF EXISTS (SELECT [RoleName], [Status] FROM ST_Roles 
                  WHERE [RoleName] = @ROLENAME)
       BEGIN
          RETURN 0
       END
       ELSE IF(@ROLENAME IS NOT NULL)
       BEGIN
          INSERT INTO ST_Roles ([RoleName], [Status]) VALUES(@ROLENAME, @STATUS)
          RETURN 1
       END
    END
END

The above is my stored proc. When NULL values are passed it should return 0. Even when 1 value is passed and another value is not passed it should return 0 instead of inserting the record.

Any help is really appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

枯叶蝶 2024-12-18 17:49:05

在你描述你的问题之前，我先猜测一下。我认为问题出在默认参数上。也许您只是不传递参数或者传递空字符串，所以尝试这种方式

 ALTER PROCEDURE [dbo].[STL_ADDNEWROLE](
               @ROLENAME VARCHAR(100) = NULL, 
                @STATUS BIT = NULL)
        AS
        BEGIN   
            SET NOCOUNT ON;
            IF(ISNULL(@ROLENAME, '') = '' OR @STATUS IS NULL)
                BEGIN
                    RETURN 0
                END
            ELSE
                BEGIN
                    IF EXISTS (SELECT [RoleName],[Status] from ST_Roles where  [RoleName] = @ROLENAME)
                        BEGIN
                            RETURN 0
                        END
                    ELSE IF(@ROLENAME IS NOT NULL)
                        BEGIN
                            INSERT INTO ST_Roles ([RoleName],[Status]) VALUES(@ROLENAME,@STATUS)
                            RETURN 1
                        END
                END


        END

Before you describe your problem, I'll just guess. I suppose the problem is in default parameters. Maybe you just don't pass parameters or you pass empty string, so try this way

 ALTER PROCEDURE [dbo].[STL_ADDNEWROLE](
               @ROLENAME VARCHAR(100) = NULL, 
                @STATUS BIT = NULL)
        AS
        BEGIN   
            SET NOCOUNT ON;
            IF(ISNULL(@ROLENAME, '') = '' OR @STATUS IS NULL)
                BEGIN
                    RETURN 0
                END
            ELSE
                BEGIN
                    IF EXISTS (SELECT [RoleName],[Status] from ST_Roles where  [RoleName] = @ROLENAME)
                        BEGIN
                            RETURN 0
                        END
                    ELSE IF(@ROLENAME IS NOT NULL)
                        BEGIN
                            INSERT INTO ST_Roles ([RoleName],[Status]) VALUES(@ROLENAME,@STATUS)
                            RETURN 1
                        END
                END


        END

回复收藏 0 原文

~没有更多了~