检查记录值更新存储过程MS SQL
问题描述:
后,该执行存储过程检查记录值更新存储过程MS SQL
EXEC dbo.Web_update
@hHomeEmail = '[email protected]',
@pPassword = 'XXXXX',
@mMemberID = '123456'
存储过程
UPDATE Member
SET pass_w = @pPassword,
HomeEmail = @hHomeEmail
WHERE [email protected];
我想检查HomeEmail的值和更新后pass_w如果NULL或不要因该调用返回布尔值。
---------- ---------- UPDATE
USE [Test]
GO
/****** Object: StoredProcedure [dbo].[Web_update] Script Date: 03/21/2017 12:37:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[Web_update]
-- Add the parameters for the stored procedure here
@hHomeEmail nvarchar(10) = NULL,
@MemberNo nvarchar(10) = NULL,
@pPassword nvarchar(10) = NULL,
@mMemberID INT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
UPDATE Member SET pass_w = @pPassword, HomeEmail = @hHomeEmail WHERE [email protected];
-- Insert statements for procedure here
SELECT CASE WHEN EXISTS (SELECT 1 FROM member WHERE [email protected] AND pass_w = NULL OR HomeEmail= NULL)
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT) END
END
现在我想检查:
pass_w = NULL
OR
pass_w = ''
OR
HomeEmail = NULL
OR
HomeEmail = ''
如果它们中的一个然后浇铸(1 AS BIT)
ELSE CAST(0 AS BIT)
答
您可以将输出参数添加到您的存储过程:
CREATE PROCEDURE dbo.Web_update
(
@hHomeEmail = varchar(10),
@pPassword = varchar(10),
@mMemberID = varchar(10)
@@pIsNull bit OUTPUT
)
AS
UPDATE Member
SET pass_w = @pPassword,
HomeEmail = @hHomeEmail
WHERE [email protected];
SELECT @pIsNull = CAST(CASE WHEN EXISTS (
SELECT 1
FROM Member
WHERE [email protected]
AND pass_w IS NULL
AND HomeEmail IS NULL) THEN 1
ELSE 0 END As Bit)
+0
嗨,感谢您的回复 –
为什么所有首先在这些列中归零? –
可能重复的[Get Updated Row](http://*.com/questions/1367018/get-updated-row) –
为什么允许null HomeEmail和pass_w都被添加到成员表中。现在会员可以在线登录,但必须先更新HomeEmail和pass_w才能继续。 –