Is possible to pass type as param in SQL function?
thank in advance.
Is possible to pass type as param in SQL function?
thank in advance.
CREATE FUNCTION
dbo.MyFunction(@Param1 VARCHAR(50), @Param2 INT, @Param3 UNIQUEIDENTIFIER)
RETURNS
INT
Sure you can define a type for your function parameters - but I guess that's not really what your asking, right? Please clarify!
Marc
the parameter type is defined within the function header, as a result it is always the same within the function. When you pass in a parameter that is a different types than the defined type parameter type, SQL Server does its best to that to the defined function parameter type.
I have a few functions that I want to work just like what you are after. The way I work around this issue is to define the function parameter as a varchar(x), and then issue any necessary CVONVERT() or formatting on the incoming parameter in the function call the parameter when you call it.
CREATE FUNCTION dbo.QuoteValue
(
@InputStr varchar(8000) --value to format
)
RETURNS
varchar(8000)
AS
BEGIN
RETURN COALESCE(''''+@InputStr+'''','null')
END
GO
DECLARE @DateValue datetime
SET @DateValue=GETDATE()
PRINT '@DateValue='+dbo.QuoteValue(@DateValue)
PRINT '@DateValue='+dbo.QuoteValue(CONVERT(varchar,@DateValue,121))
Depending on what you are after, if you want to work with dates or numbers, make the function parameter varchar(x) and within the function use:
here is an example of how to handle dates...
CREATE FUNCTION dbo.DateXYZ
(
@InputStr varchar(50) --date value manipulate
)
RETURNS
datetime
AS
BEGIN
DECLARE @DateValue datetime
IF ISDATE(@InputStr)=1
BEGIN
SET @DateValue=@InputStr
--do some datetime manipulation here
END
RETURN @DateValue --will return null if not given a valid date
END
GO
You could use it as follows:
DECLARE @X varchar(50)
DECLARE @Y varchar(50)
DECLARE @BadDate datetime
DECLARE @GoodDate datetime
SET @X='bad date!!'
SET @Y='1/1/2009'
SET @BadDate=dbo.DateXYZ(@X)
SET @GoodDate=dbo.DateXYZ(@Y)
SELECT @BadDate,@GoodDate
you could also pass in a parameter to signify what you are passing in:
CREATE FUNCTION dbo.AnyType
(
@InputStr varchar(8000) --general purpose value
,@Type char(1) --"I"nt, "F"loat, "D"atetime, "S"tring, etc...
)
RETURNS
varchar(8000)
AS
BEGIN
DECLARE @ReturnValue varchar(8000)
IF @Type='I' --int
BEGIN
DECLARE @IntValue int
SET @IntValue=@InputStr
--do some int manipulation here
SET @ReturnValue=@IntValue
END
ELSE IF @Type='F' --float
BEGIN
DECLARE @FloatValue float
SET @FloatValue=@InputStr
--do some float manipulation here
SET @ReturnValue=@FloatValue
END
ELSE IF @Type='D' --datetime
BEGIN
DECLARE @DatetimeValue datetime
SET @DatetimeValue=@InputStr
--do some datetime manipulation here
SET @ReturnValue=CONVERT(varchar(23),@DatetimeValue,121)
END
--etc...
--etc...
RETURN @ReturnValue
END
GO
Functions can only return a single fixed type, make it varchar(x) and have the caller assign it to a variable of the the proper data type, or CAST it in a result set as necessary.