the best way to return an integer 2

[NuJoizey]

If I have a value stored in SQL Server as an integer, say NumOfUnits sold, and I want to return that value based on an ID field to use in the UI of my ASP.NET app, what is the best way to do this? Currently, I am writing an Scalar valued UDF like this in SQL Server:

CREATE FUNCTION [dbo].[udf_GetNumOfUnitsSoldByID]
(
  @TicketID int
)
RETURNS int
AS
BEGIN       
	RETURN
		(	
			SELECT		NumUnitsSold
			FROM		Inventory
			WHERE		ticketid = @TicketID
		)

END

but in my data access layer and in UI, I seem to keep having issues with variable typing. I find that sometimes I have to use CINT/CTYPE or some such function or else the value will not render correctly.

Is a scalar UDF the way to go on this, or is there a better way?

 

[PWise]

CREATE FUNCTION [dbo].[udf_GetNumOfUnitsSoldByID](@TicketID int)
RETURNS int
AS
BEGIN
DECLARE @NumUnitsSoldint
SELECT  @NumUnitsSoldint=NumUnitsSold
FROM        Inventory
WHERE        ticketid = @TicketID
Return @NumUnitsSoldint
END

 

[NuJoizey]

Thanks Pwise - is there anything wrong with the way I had been doing it? ie, do you think it has the potential to cause oddball behavior? I'm wondering if it's worth going back and changing some of the code in other similar UDF's throughout my database.

I would like to have a statement concise in length but vast in profundity to put here, but I just can't think of one.

 

[markros]

Yes, in your function you declared that it's a scalar function, but you were trying to return a result of a select. This UDF, IMHO, should not even compile.

PluralSight Learning Library

 

[PWise]

Corrected syntex

CREATE FUNCTION [dbo].[udf_GetNumOfUnitsSoldByID](@TicketID int)
RETURNS int
AS
BEGIN
DECLARE @NumUnitsSold int
SELECT  @NumUnitsSold=NumUnitsSold
FROM        Inventory
WHERE        ticketid = @TicketID
Return @NumUnitsSold
END

 

[NuJoizey]

thanks all.

I would like to have a statement concise in length but vast in profundity to put here, but I just can't think of one.