bit value as parameter

Hi,
I have a Store Procedure and include a bit parameter need to pass, such as:
exec spGenerateL1 'Bu01', Bit Value,'Administrator'
But I don't know how to pass the Bit Value,
I try to use 0, 1, TRUE, FALSE, the error message always response:
Error converting data type varchar to bit.
Someone can give me a favor, Thanks!
Angi
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Store Procedure as follow:
CREATE PROCEDURE spGenerateL1
@.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
@.Chart AS NVARCHAR(30)
AS
SET NOCOUNT ON
DECLARE @.tKpiName AS NVARCHAR(40)
DECLARE @.tUnitType AS NVARCHAR( 1)
DECLARE @.tCurrentValue AS DECIMAL(7,2)
DECLARE @.tRange AS BINARY
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE spCursor CURSOR FOR
SELECT a.KpiName, a.UnitType, b.CurrentValue
FROM BisKpi a INNER JOIN BisKpiDetail b
ON a.KpiGUID = b.KpiGUID
AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn =
@.Orgn
AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
DATEDIFF(DAY, FullDate, GETDATE()) = 0)
OPEN spCursor
FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.tRange = (SELECT CASE
WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
COALESCE(range10,range20,range30,range40
,range50,range60,range70,range80,ran
ge90,range100)
WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
COALESCE(range20,range30,range40,range50
,range60,range70,range80,range90,ran
ge100)
WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
COALESCE(range30,range40,range50,range60
,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
COALESCE(range40,range50,range60,range70
,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
COALESCE(range50,range60,range70,range80
,range90,range100)
WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
COALESCE(range60,range70,range80,range90
,range100)
WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
COALESCE(range70,range80,range90,range10
0)
WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
COALESCE(range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
ELSE range0
END AS Indicator
FROM Bis.DBO.ChartConfig WHERE ChartName = '-TpO')
SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
END
CLOSE spCursor
DEALLOCATE spCursorIn your table "BisKpi", what data type is the field "KpuUserMode"
defined as?|||Hi, Tracy,
The field's data type is Bit.
Angi
"Tracy McKibben" <tracy.mckibben@.gmail.com>
'?:1148386860.513912.28450@.y43g2000cwc.googlegroups.com...
> In your table "BisKpi", what data type is the field "KpuUserMode"
> defined as?
>|||Hi Angi
Try this:
declare @.mybit bit
set @.mybit = 1 -- or set @.mybit = 0
exec spGenerateL1 'Bu01', @.mybit ,'Administrator'
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"angi" <angi@.news.microsoft.com> wrote in message
news:%23Hb6UdkfGHA.3916@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a Store Procedure and include a bit parameter need to pass, such
> as:
> exec spGenerateL1 'Bu01', Bit Value,'Administrator'
> But I don't know how to pass the Bit Value,
> I try to use 0, 1, TRUE, FALSE, the error message always response:
> Error converting data type varchar to bit.
> Someone can give me a favor, Thanks!
> Angi
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Store Procedure as follow:
> CREATE PROCEDURE spGenerateL1
> @.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
> @.Chart AS NVARCHAR(30)
> AS
> SET NOCOUNT ON
> DECLARE @.tKpiName AS NVARCHAR(40)
> DECLARE @.tUnitType AS NVARCHAR( 1)
> DECLARE @.tCurrentValue AS DECIMAL(7,2)
> DECLARE @.tRange AS BINARY
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> DECLARE spCursor CURSOR FOR
> SELECT a.KpiName, a.UnitType, b.CurrentValue
> FROM BisKpi a INNER JOIN BisKpiDetail b
> ON a.KpiGUID = b.KpiGUID
> AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn =
> @.Orgn
> AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
> DATEDIFF(DAY, FullDate, GETDATE()) = 0)
> OPEN spCursor
> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.tRange = (SELECT CASE
> WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
> COALESCE(range10,range20,range30,range40
,range50,range60,range70,range80,r
ange90,range100)
> WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
> COALESCE(range20,range30,range40,range50
,range60,range70,range80,range90,r
ange100)
> WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
> COALESCE(range30,range40,range50,range60
,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
> COALESCE(range40,range50,range60,range70
,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
> COALESCE(range50,range60,range70,range80
,range90,range100)
> WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
> COALESCE(range60,range70,range80,range90
,range100)
> WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
> COALESCE(range70,range80,range90,range10
0)
> WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
> COALESCE(range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
> WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
> ELSE range0
> END AS Indicator
> FROM Bis.DBO.ChartConfig WHERE ChartName = '-TpO')
> SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
> END
> CLOSE spCursor
> DEALLOCATE spCursor
>|||Kalen,
Thank u very much!
Thanks for Tracy, too.
For this point, if Bit field needs to implement such way to pass parameter,
it seems inconvenient and let me want to change it's data type as char or
something else to be a flag.
So, any good idea about use Bit as a flag? or what's kind of data type is
better to be a flag?
Thanks!
Angi
"Kalen Delaney" <replies@.public_newsgroups.com> glsD:OTlfCgtfGHA.4880@.TK2MSFTNGP03
.phx.gbl...
> Hi Angi
> Try this:
> declare @.mybit bit
> set @.mybit = 1 -- or set @.mybit = 0
> exec spGenerateL1 'Bu01', @.mybit ,'Administrator'
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "angi" <angi@.news.microsoft.com> wrote in message
> news:%23Hb6UdkfGHA.3916@.TK2MSFTNGP04.phx.gbl...
>