SQL Function Throws Type Error When Passing in Params

Question

The function is seeking to return an integer value that is equal to the sum of the values that appear before zeros in the column and add it to the final value in the column.

CREATE FUNCTION name
     (@p1 NVARCHAR(MAX),
      @p2 INT, 
      @p3 INT)
RETURNS INT
AS 
BEGIN
    DECLARE @total INT = 0
    DECLARE @cnt INT = 1;
    DECLARE @row INT = 0
    DECLARE @nextRow INT = 0;
    DECLARE @tmp NVARCHAR(MAX)

    SET @tmp = @p1

    DECLARE @myTemp TABLE (Col INT, ID1 INT, ID2 INT, reading NVARCHAR(MAX));
    DECLARE @zeroCols TABLE(rn INT, Col INT);


    INSERT INTO @myTemp
        SELECT ROW_NUMBER() OVER(ORDER BY nHeatID) Col, col2, col3, @tmp 
        FROM schema.table
        WHERE col4 = @p2 
          AND col5 = @p3

    SET @total = (SELECT TOP 1 @tmp FROM @myTemp ORDER BY col DESC)

    INSERT INTO @zeroCols
        SELECT ROW_NUMBER() OVER(ORDER BY col) rn, col  
        FROM @myTemp 
        WHERE @tmp = 0

        WHILE @cnt <= (SELECT COUNT(Col) FROM @zeroCols)
        BEGIN
            SET @row = (SELECT Col FROM @zeroCols WHERE rn = @cnt)

            IF ((SELECT @tmp FROM @myTemp WHERE col = @row-1) <> 0)
                SET @total = @total + (SELECT @tmp FROM @myTemp WHERE col = @row-1); 

            SET @cnt = @cnt + 1;
        END;

        RETURN @total
END;

The @myTemp table place-holds data from my table with row numbers, the @zeroCols table holds the row numbers where the zeros are.

Then I loop over that set of values and decide if there is a nonzero value before the zero at the column provided and if there is, i add it to the total.

This worked before I tried to make it into a function.

Is it not liking the fact that I'm passing in a nvarchar as a column name?

And when I call it like so:

SELECT name('val', 0, 0)

I get this error:

Conversion failed when converting the nvarchar value 'val' to data type int

We're missing the really important part here... The function's DDL. — Thom A, Feb 19 '19 at 14:17
Welcome to SO. It would certainly help if you provide the code. Obviously you use @p1 somewhere in the code, in a way that it gets converted (Probably implicitly) to int. Note that int has a higher priority than text, so if you mix it with others that are int somewhere, it will try to change it. Usually happens when you try to print something. — George Menoutis, Feb 19 '19 at 14:18
The error is in the `...function body...`. We need to see that part. — S3S, Feb 19 '19 at 14:20
The problem isn't the cast of 'val' to the parameter data type of nvarchar(max), the problem is the use of **@p1 as an int** inside the function body. I bet that if you pass the value '1' instead of 'val' you won't get the error. — EzLo, Feb 19 '19 at 14:22
99 times out of a 100 this error occurs when some inexperienced developer tries to compare a column / variable with `int` data type to a column / variable / hard-coded varchar/nvarchar. The reason is that SQL Server will try to implicitly convert the string value to an int value. Without seeing the code of the function, it's impossible to give a more exact answer. — Zohar Peled, Feb 19 '19 at 14:26
Here is the cause: `where @tmp = 0`. However, seems to me like this function can probably be written much better, so perhaps you should describe what the function should do (giving sample data and desired results) - so that we can help you improve it. — Zohar Peled, Feb 19 '19 at 14:42
@ZoharPeled The data I'm interested in is a set of readings that come in from a sensor every few seconds. It could look something like this: ------- 0 ------- 4 ------- 18 ------- 30 ------- 0 ------- 0 ------- 4 ------- 12 ------- 16 ------- What i want to do is find the max values before the extra 0s and add them together and add that to the last value (as I know that last value will be 0 or another max) So in this examoke my answer would be 46 — Jmit, Feb 19 '19 at 14:54

EzLo · Answer 1 · 2019-02-19T14:44:24.407

2

I've reduced the problem to the following:

@p1 nvarchar(max) -- Parameter of the function

--...

DECLARE @total INT = 0
DECLARE @tmp nvarchar(max)
SET @tmp = @p1

--...

DECLARE @myTemp TABLE(Col INT, ID1 INT, ID2 INT, reading nvarchar(max));

--...

SET @total = (SELECT TOP 1 @tmp FROM @myTemp ORDER BY col DESC) -- Can't convert property

You are assigning the value of @tmp (NVARCHAR(MAX)) to @total which is an INT.

I can't infer what you are trying to do with this, so I can't propose a fix, just make sure to assign data types that match.

edited Feb 19 '19 at 14:44

answered Feb 19 '19 at 14:41

EzLo

13,780
10
33
38

I didn't see that, but I did see a problem you've missed in the next statement - so I guess we're even :-) – Zohar Peled Feb 19 '19 at 14:44
I am trying to use the value passed in as @p1 as a column name in the line. It would evaluate to an int if the variable formulated the query properly. – Jmit Feb 19 '19 at 14:48
You can't use variables to reference objects names (like tables, columns or the such). You will have to use dynamic SQL for this and you can't use dynamic SQL in a function. You will have to use an SP for this. – EzLo Feb 19 '19 at 14:55
@EzLo Thanks, I didn't know that. I was hoping to ref this function in a sproc so that it wouldn't make it more messy, but I guess I can just use my non-function inline – Jmit Feb 19 '19 at 15:01

score 0 · Answer 2 · answered Feb 19 '19 at 15:19

0

I found that if I use a switch then I can decide a column name based on the param.

Can I pass column name as input parameter in SQL stored Procedure

answered Feb 19 '19 at 15:19

Jmit

1
1

SQL Function Throws Type Error When Passing in Params

2 Answers2