Bit to Boolean?

I built a simple sproc that runs an executable using xp_cmdshell.  I want to be able to identify errors when the executable is called.  The variable XPCmdShellResult contains a bit value.  When the compiler hits the IF clause it balks, presenting the following:

An expression of non-boolean type specified in a context where a condition is expected, near 'BEGIN'.

If I modify the IF clause to the following:

   IF @XPCmdShellResult > 0

The procedure runs properly, but I should be able to @XPCmdShellResult to behave as a Boolean without the comparison operator.  (It's cleaner.)
CREATE PROCEDURE dbo.CallRTXMLExportTool
AS
DECLARE 
   @XPCmdShellCommand varchar(200),
   @XPCmdShellResult bit   
   SET @ErrorHint = 'Not Configured'
   SET @XPCmdShellCommand = 'C:\RTXMLExportTool.exe'
   EXEC @XPCmdShellResult = xp_cmdshell @XPCmdShellCommand, NO_OUTPUT
   IF @XPCmdShellResult -- Problematic code here.
   BEGIN
     -- Error code not shown.
   END
GO

Open in new window

jdanaAsked:
Who is Participating?
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.

SharathData EngineerCommented:
Try with CASE statement.
CREATE PROCEDURE dbo.CallRTXMLExportTool
AS
DECLARE
   @XPCmdShellCommand varchar(200),
   @XPCmdShellResult bit  
   SET @ErrorHint = 'Not Configured'
   SET @XPCmdShellCommand = 'C:\RTXMLExportTool.exe'
   EXEC @XPCmdShellResult = xp_cmdshell @XPCmdShellCommand, NO_OUTPUT
   CASE WHEN @XPCmdShellResult  = 1 --code here
        ELSE --code here
   END  -- Problematic code here.
   BEGIN
     -- Error code not shown.
   END
GO
 
0
HoggZillaCommented:
A bit datatype can also be NULL so that kills your implicit conversion to TRUE or FALSE.
0
chapmandewCommented:
Since it is a bit, it will behave as a boolean (just not the way you're trying though)

if @XPCmdShellResult  = 0  --false
begin
--do something
end
else
begin
--do something else for true.
end
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

SharathData EngineerCommented:
CREATE PROCEDURE dbo.CallRTXMLExportTool
AS
DECLARE
   @XPCmdShellCommand varchar(200),
  @XPCmdShellResult bit  
   SET @ErrorHint = 'Not Configured'
  SET @XPCmdShellCommand = 'C:\RTXMLExportTool.exe'
  EXEC @XPCmdShellResult = xp_cmdshell @XPCmdShellCommand, NO_OUTPUT
  CASE WHEN ISNULL(@XPCmdShellResult,0)  = 0 --code when your variable is FALSE or NULL
       ELSE ----code when your variable is TRUE
  END  -- Problematic code here.
  BEGIN
    -- Error code not shown.
  END
GO
0
dportasCommented:
Firstly, BIT is a numeric data type. So you must use a numeric comparison:

IF @XPCmdShellResult = 1 ... etc

Secondly, the return value of xp_cmdshell is not a BIT. All procs return INTEGER values.
0

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
SharathData EngineerCommented:
did you check my code. I used ISNULL function to resolve the problem.
0
dportasCommented:
Sharath_123, you can't use CASE like that. CASE is not a statement.
0
SharathData EngineerCommented:
If CASE is not working, check this.
CREATE PROCEDURE dbo.CallRTXMLExportTool
AS
DECLARE
   @XPCmdShellCommand varchar(200),
   @XPCmdShellResult bit  
   SET @ErrorHint = 'Not Configured'
   SET @XPCmdShellCommand = 'C:\RTXMLExportTool.exe'
   EXEC @XPCmdShellResult = xp_cmdshell @XPCmdShellCommand, NO_OUTPUT
   IF @XPCmdShellResult IS NULL OR @XPCmdShellResult = 0 -- code when the variable is FALSE .
   ELSE @XPCmdShellResult = 1 -- code when the variable is TRUE
   BEGIN
     -- Error code not shown.
   END
GO
0
jdanaAuthor Commented:
dportas - I appreciate the two salient points.  J
0
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.