SQL Statement with if and is null

I have the following statement

declare @BusinessRules_PreventDefault bit
declare @Result_Focus nvarchar(255)
declare @Result_ShowViewMessage nvarchar(255)
select b.SRequestID, a.SRequestID
from  ServiceRequest a
LEFT JOIN   ServiceRequestAddFru b  ON b.SRequestID = a.SRequestID;
if b.SRequestID is null
begin
set @BusinessRules_PreventDefault = 1
set @Result_Focus = 'You must enter at least one Fru'
set @Result_ShowViewMessage = 'Error You must enter a Fru'
end;

Open in new window


What I am trying to accomplish is to trigger a message if you create a order header when you hit a submit button to add detail and you don't add detail then the message will display and will not let you submit.  If you do add detail all is good.

The error I get on the if b.SRequestID is multi-part identifier could not be bound.
JDay2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You can't test a table field like that.

Here's a way to you do it:
declare @BusinessRules_PreventDefault bit
declare @Result_Focus nvarchar(255)
declare @Result_ShowViewMessage nvarchar(255)

if exists(select 1
	from  ServiceRequest a
		LEFT JOIN   ServiceRequestAddFru b  ON b.SRequestID = a.SRequestID;
	where b.SRequestID is null)
begin
	set @BusinessRules_PreventDefault = 1
	set @Result_Focus = 'You must enter at least one Fru'
	set @Result_ShowViewMessage = 'Error You must enter a Fru'
end;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JDay2Author Commented:
Now this does not print a message and when I try to submit with details it does nothing as well.

Do I need an else statement?  That when header and detail exist continue.
Vitor MontalvãoMSSQL Senior EngineerCommented:
This is only the T-SQL part. I don't know where is you header and detail. Are you talking about a screen form or a web page or a report?
In SSMS for example, you can use PRINT command to show the variable values (add the following lines to the end of the above code):
PRINT @BusinessRules_PreventDefault 
PRINT @Result_Focus 
PRINT @Result_ShowViewMessage

Open in new window

10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

JDay2Author Commented:
Or I need the Parameter @SRequestID in the statement.
JDay2Author Commented:
I have gotten it to print using the functions above but on my web page I have a submit button and it need the @SRequestID parameter which is the current record id if that makes sense.
Vitor MontalvãoMSSQL Senior EngineerCommented:
I can't understand what's really the issue. Maybe I'm not seeing all picture of your problem.
@SRequestID is a SQL Server variable? If so you just can test it if is null or not: IF @SRequestID IS NULL
JDay2Author Commented:
Okay I will try to explain the best I can.  I have a command button with the following code

Attached is the code
if exists(select 1
	from  ServiceRequest a
		LEFT JOIN   ServiceRequestAddFru b  ON b.SRequestID = a.SRequestID
	where b.SRequestID is null)
begin
	set @Result_Focus = 'You must enter at least one Fru'
	set @Result_ShowViewMessage = 'Error You must enter a Fru'
end;

update ServiceRequest
set SRequestStatusID = '1'
where SRequestID = @SRequestID

Open in new window


When you create and order header it creates SRequestID with Address and a SRequestStatusID (ServiceRequest table)
The next step is it goes to Se
It then goes (ServiceRequestAddFru table) to add parts.  The link is SRequestID
On this page I have a command button which is basically a submit button
I need to display a message if someone tries to hit submit before and parts are added.
Once parts are added then the submit should update the status.

I hope I am making sense here.
JDay2Author Commented:
The reason why I have the @ServiceRequestID is its the current record on the screen.
Scott PletcherSenior DBACommented:
Ultimately you will want an actual trigger on the table to verify that the condition has been met, even if you check it in code first (and you probably should):


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER ServiceRequest__TRG_INS
ON dbo.ServiceRequest
AFTER INSERT
AS
SET NOCOUNT ON;
IF EXISTS(
SELECT 1
FROM inserted i
LEFT OUTER JOIN ServiceRequestAddFru b ON b.SRequestID = i.SRequestID
WHERE b.SRequestID IS NULL
)
BEGIN
    RAISERROR('You must enter at least one Fru for every SR.', 16, 1)
    ROLLBACK TRANSACTION
END --IF
GO --end of trigger
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.