Function to return int values from a column - SQL

Question

I'm having a problem returning an integer value from an identity column where I only want the values greater than 5 which will be used in another select. The code I have so far:

CREATE FUNCTION [dbo].[fn_Get_Dragon_ID]()
RETURNS INT
AS
BEGIN
    DECLARE @I INT
    SELECT @I = Dragon_ID
    FROM Dragons
    WHERE Dragon_ID > 5
    RETURN @I
END

-- How it's being used:

CREATE PROCEDURE [dbo].[sp_Discovered_Dragons]
AS
SELECT
    dbo.fn_Get_Dragon_ID() AS Dragon_ID,  
    [dbo].[fn_Set_Tracker](ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 7 + 1)
    AS Assigned_Tracker
FROM
    [dbo].[stDrgnInbnd0]

The output I expected:

Dragon_ID   Assigned_Tracker
6           Hiccup
7           Camicazi
8           Hiccup
9           Hiccup
10          Tuffnut
11          Snotface Snotlout
.
.
.

What I'm actually getting:

Dragon_ID   Assigned_Tracker
305         Hiccup
305         Camicazi
305         Hiccup
305         Hiccup
305         Tuffnut
305         Snotface Snotlout
.
.
.

I've searched the site and Google'd my brains out but I must be stating the issue incorrectly. Thanks for any assistance.

UPDATE: The Dragon_ID column is an identity column - (1,1), which I am attempting to get the rows higher than 5 (the row count of the initial table) and the row id's after an insert.

@Gordon Linoff: I tried earlier to use the code inline as you suggested however :

declare @I int
SELECT
    (SELECT @I as Dragon_ID
FROM Dragons
WHERE Dragon_ID > 5),  
    stDrgnInbnd0.Dragon_Name AS Dragon_Type,
    stDrgnInbnd0.Sighting_Date,
    stDrgnInbnd0.Sighting_Location,
    stDrgnInbnd0.Pack_Status,
    stDrgnInbnd0.Day_OR_Night_Sleep,
    [dbo].[fn_Set_Tracker](ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 7 + 1)
    AS Assigned_Tracker
FROM
    [dbo].[stDrgnInbnd0]

And got the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Thanks again.

The variable @I is getting last value from the selected dragon Ids. That's the reason you are getting 305. — fiveobjects
– fiveobjects, Commented Jul 31, 2019 at 17:38
The way you are passing the value to fn_Set_Tracker function in the same way perhaps you can pass some value, compute in the function and return. Unless you show the input schema and the requirement coming up with a solution is difficult. — fiveobjects
– fiveobjects, Commented Jul 31, 2019 at 17:55
@fiveelements I realize the last value is being used, the column I'm selecting is of type int and I need the row numbers after an insert. I will update the question. Thanks. — kn0t
– kn0t, Commented Jul 31, 2019 at 18:04
Is there some reason you need a function rather than a view? — Robert Sievers
– Robert Sievers, Commented Jul 31, 2019 at 18:12
Gordon's answer is spot on for the issue at hand. However I have a feeling that what you really need here is just a join between those two tables. — Sean Lange
– Sean Lange, Commented Jul 31, 2019 at 18:39

Gordon Linoff · Accepted Answer · 2019-07-31 18:16:50Z

2

Your function is a scalar function so it returns a single value. That single value is determined by this logic:

SELECT @I = Dragon_ID
FROM Dragons
WHERE Dragon_ID > 5;

What this is doing is cycling through the Dragons table and returns the "last" encountered value. Because SQL tables represent unordered sets, this is an indeterminate row in the table.

In your case, the "intedeterminate" row is probably the row with the highest id. This is an observations; there are no guarantees.

It sounds like you want a table-valued function. If so, you need to both fix the function and adjust your query to move the function call into the from clause. Actually, though, I see no reason for a function at all -- a view or just putting the code inline would be more efficient.

answered Jul 31, 2019 at 18:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kn0t Over a year ago

Thanks! I'll take a look at table-valued functions, however, I tried inlining the code earlier and got "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." I'll post what I'm using above.

kn0t Over a year ago

I re-read your answer and saw view, I completely didn't think about it as a solution. Thanks again.

Hogan · Accepted Answer · 2019-07-31 18:53:25Z

0

You probably want to do a join which would look like the select below.

The problem is I don't know what column in stDrgnInbnd0 to join on so I put "?" there.

SELECT Dragon_ID,
    stDrgnInbnd0.Dragon_Name AS Dragon_Type,
    stDrgnInbnd0.Sighting_Date,
    stDrgnInbnd0.Sighting_Location,
    stDrgnInbnd0.Pack_Status,
    stDrgnInbnd0.Day_OR_Night_Sleep,
    [dbo].[fn_Set_Tracker](ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 7 + 1)
    AS Assigned_Tracker
FROM Dragons
join stDrgnInbnd0 ON stDrgnInbnd0.?  = Dragons.DragonID 
WHERE Dragon_ID > 5

What are all the columns in stDrgnInbnd0?

answered Jul 31, 2019 at 18:53

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

Collectives™ on Stack Overflow

Function to return int values from a column - SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related