SQL Server MS SQL Call function passing in bit parameters returns Conversion failed when converting the nvarchar value 'Y' to data type bit

r1zhe5dt 于 2023-02-21 发布在其他

关注(0)|答案(2)|浏览(114)

I have a table with 7 bit elements defined (Form1 - Form7). I need to determine which element contains a value. The data looks like this:

Form1    Form2    Form3    Form4    Form5    Form6    Form7
  1        0        0        0        0        0        0 
  0        0        0        1        0        0        0 
  0        0        0        0        0        0        1 
  0        1        0        0        0        0        0

I want to call a function which takes in each of these elements and returns an integer value so I can insert the integer value into a new table.

I call my function in an insert comment like this:

INSERT INTO [DatabaseName].[dbo].[Table] (FormValue)
SELECT dbo.fnBuildFormType(a.Form1, a.Form2, a.Form3, a.Form4, a.Form5, a.Form6, a.Form7)
FROM [DatabaseName].[dbo].[SourceTable] a

And this is the code I have in my function:

ALTER FUNCTION [dbo].[fnBuildFormType] 
(
    @Form1 bit,
    @Form2 bit,
    @Form3 bit,
    @Form4 bit,
    @Form5 bit,
    @Form6 bit,
    @Form7 bit
)
RETURNS integer
AS
BEGIN
    DECLARE @ReturnInteger integer
    IF (@Form1 = 1)
    BEGIN
        SET @ReturnInteger = 1
    END
    ELSE
    BEGIN
        IF (@Form2 = 1)
        BEGIN
            SET @ReturnInteger = 2
        END
        ELSE
        BEGIN
            IF (@Form3 = 1)
            BEGIN
                SET @ReturnInteger = 3
            END
            ELSE
            BEGIN
                IF (@Form4 = 1)
                BEGIN
                    SET @ReturnInteger = 4
                END
                ELSE
                BEGIN
                IF (@Form5 = 1)
                    BEGIN
                    SET @ReturnInteger = 5
                END
                ELSE
                BEGIN
                    IF (@Form6 = 1)
                    BEGIN
                        SET @ReturnInteger = 6
                    END
                    ELSE
                    BEGIN
                        SET @ReturnInteger = 8
                    END
                END
                END
            END
        END
    END
    RETURN @ReturnInteger
END

The error message that I get back states sql can not convert the nvarchar value 'Y' to data type bit. The sql table that I am converting originated in MS Access and was converted into a sql table. When I expand the table in MS SSM and look at the definition of Form1 - Form7, it says (bit, null) for each.

Any ideas why I would be getting this error message?

Thank you.

UPDATED:

In an effort to simplify this, I have changed my function to look like this:

ALTER FUNCTION [dbo].[fnBuildFormType] 
(
    @FormOther char(1)        
)
RETURNS integer
AS
BEGIN
    DECLARE @ReturnInteger integer
    SET @ReturnInteger = CASE
        WHEN @FormOther = 'Y' THEN 7
        ELSE 0
    END
    RETURN @ReturnInteger
END

And I have changed my query to look like this:

INSERT INTO [DatabaseName].[dbo].[Table] (FormValue)
SELECT dbo.fnBuildFormType(a.Form1)
FROM [DatabaseName].[dbo].[SourceTable] a

I am still getting the same error.

sql-server

来源：https://stackoverflow.com/questions/75514389/ms-sql-call-function-passing-in-bit-parameters-returns-conversion-failed-when-co

2条答案

按热度按时间

webghufk1#

You can convert the tbale into a varbinary and then get the bit number quite easyly

CREATE FUNCTION fnBuildFormType
(
    @varbin varbinary
)
RETURNS integer
AS
BEGIN
  

    DECLARE @i INT = 0;
    DECLARE @ReturnInteger INT = 0;

        WHILE @i <= 7
        BEGIN
               IF GET_BIT (  @varbin, @i) = 1
                    BEGIN
                      SET @ReturnInteger =  @i + 1  ;
                      BREAK;
                  END 
              SET @i = @i + 1;
        END
    RETURN(@ReturnInteger);
End;

SELECT dbo.fnBuildFormType(CONVERT(BIGINT,CONVERT(BINARY(7),1 * [Form1] + 10 * [Form2] + 100 * [Form3] +  1000 * [Form4] 
+ 10000 * [Form5] + 100000 * [Form6] +  10000000 * [Form7])))
FROM Table1

(No column name)
1
4
8
2

fiddle

赞(0）回复(0）举报 2023-02-21

yhxst69z2#

Well, doing a select, or insert command from VBA, or running/using a stored procedure in SQL server is a Mount Everest of a DIFFERENT question.

but, say this table

select * from MachineElements

ID	Form1	Form4	Form7	Machine
1	1	0	0	Stamping 1
2	0	1	0	Stamping 2
3	0	0	1	Stamping 2
4	2	0	0	Stamping 3

And a query to take the 1-7 values can look like this:

select id, Machine,
CASE
   When Form1 > 0 Then 1
   When Form2 > 0 Then 2
   When Form3 > 0 Then 3
   When Form4 > 0 Then 4
   When Form5 > 0 Then 5
   When Form6 > 0 Then 6
   When Form7 > 0 Then 7
   ELSE  8
END as BformType
FROM MachineElements

And now we see, get this:

id	Machine	BformType
1	Stamping 1	1
2	Stamping 2	4
3	Stamping 2	7
4	Stamping 3	1

So, then this:

INSERT INTO tblMachineResults
     (ID,MachineName, BformType)
SELECT ID, Machine, 
CASE 
    WHEN Form1 > 0 THEN 1 
    WHEN Form2 > 0 THEN 2 
    WHEN Form3 > 0 THEN 3 
    WHEN Form4 > 0 THEN 4 
    WHEN Form5 > 0 THEN 5 
    WHEN Form6 > 0 THEN 6 
    WHEN Form7 > 0 THEN 7 
    ELSE 8 
END AS BformType
FROM     MachineElements

and now in tblMachineResults, we have

select * from tblMachineResults

And thus this:

ID	MachineName	BformType
1	Stamping 1	1
2	Stamping 2	4
3	Stamping 2	7
4	Stamping 3	1

so, at this point?

It not clear what the ms-access client part is supposed to do?

However, I can most certainly cut + paste in the above query into the Access query editor, and run it.

We just have to set the query as pass-though, and thus this:

And since it not supposed to return records, then don't forget this setting in the query sheet:

So, you are free to run that query from SQL manager, or as above shows, right from inside of Access.

赞(0）回复(0）举报 2023-02-21

我来回答

SQL Server MS SQL Call function passing in bit parameters returns Conversion failed when converting the nvarchar value 'Y' to data type bit

2条答案

相关问题

热门标签

最新问答