SQL Server stored procedure not working to display a message when char value = N [closed]

8gsdolmq  于 2023-10-15  发布在  其他
关注(0)|答案(1)|浏览(113)

Closed. This question needs debugging details . It is not currently accepting answers.

Edit the question to include desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem . This will help others answer the question.

Closed 2 days ago.
Improve this question

I have written the below stored procedure to raise an Error message based on my IF Statements. It works correctly and returns the message on the Int parameters (Question 19) but it does not display a message for the char(1) parameters. The Default Value for the char(1) Fields is 'N'. How do I write the code correctly for the char(1) parameters to display the Message 'You must select at least one option in Question 1.' when the Default Value is 'N'?

create procedure dbo.sp_Intake
    (
    @id char(36), 
    @box17under char(1), 
    @box18to30 char(1),
    @box31to50 char(1),
    @box51over char(1),
    @males17under int,
    @males18to30 int,
    @males31to50 int,
    @males51over int,
    @females17under int,
    @females18to30 int,
    @females31to50 int,
    @females51over int
    )
as 
--Question 1
Begin
    IF
        @box17under = 'N' and @box18to30 = 'N' and @box31to50 = 'N' and @box51over = 'N'
    BEGIN
            raiserror('You must select at least one option in Question 1.',16,1)
            rollback
            return
    END
End
-- Question 19
Begin
    IF @males17under is null and @males18to30 is null and @males31to50 is null and @males51over is null and
    @females17under is null and @females18to30 is null and @females31to50 is null and @females51over is null 
    BEGIN
            raiserror('You must select at least one option in Question 19.',16,1)
            rollback
            return
    END
End
GO

The stored procedure is running on a Form and the Form is pointing to my SQL table below :

yx2lnoni

yx2lnoni1#

You could give this way a shot if the default value is N

Begin
    IF
        (CASE WHEN @box17under = 'N' THEN 0 ELSE 1 END) +
        (CASE WHEN @box18to30 = 'N' THEN 0 ELSE 1 END) +
        (CASE WHEN @box31to50 = 'N' THEN 0 ELSE 1 END) +
        (CASE WHEN @box51over = 'N' THEN 0 ELSE 1 END) = 0
    BEGIN

If Null or N is the default you could try this instead:

IF
        (CASE WHEN @box17under = 'N' OR @box17under IS NULL THEN 0 ELSE 1 END) +
        (CASE WHEN @box18to30 = 'N' OR @box18to30 IS NULL THEN 0 ELSE 1 END) +
        (CASE WHEN @box31to50 = 'N' OR @box31to50 IS NULL THEN 0 ELSE 1 END) +
        (CASE WHEN @box51over = 'N' OR @box51over IS NULL THEN 0 ELSE 1 END) = 0
    BEGIN

相关问题