?
Solved

Bit to Boolean?

Posted on 2008-11-10
9
Medium Priority
?
1,069 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:jdana
9 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 22924796
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
 
LVL 17

Assisted Solution

by:HoggZilla
HoggZilla earned 200 total points
ID: 22924849
A bit datatype can also be NULL so that kills your implicit conversion to TRUE or FALSE.
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 200 total points
ID: 22924867
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
LVL 41

Expert Comment

by:Sharath
ID: 22924887
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
 
LVL 22

Accepted Solution

by:
dportas earned 400 total points
ID: 22924903
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
 
LVL 41

Expert Comment

by:Sharath
ID: 22924931
did you check my code. I used ISNULL function to resolve the problem.
0
 
LVL 22

Expert Comment

by:dportas
ID: 22925245
Sharath_123, you can't use CASE like that. CASE is not a statement.
0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 200 total points
ID: 22925270
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
 

Author Closing Comment

by:jdana
ID: 31515214
dportas - I appreciate the two salient points.  J
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question