StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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.</p> <p>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.</p> <pre><code>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)) </code></pre> <p>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:</p> <ul> <li><strong>ISDATE ( expression )</strong> - ISDATE returns 1 if the input expression is a valid date; otherwise, it returns 0. The following table shows the return values for a selection of examples.</li> <li><strong>ISNUMERIC (Transact-SQL)</strong> - ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0. A return value of 1 indicates that expression can be converted to at least one of the numeric types.</li> </ul> <p>here is an example of how to handle dates...</p> <pre><code>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 </code></pre> <p>You could use it as follows:</p> <pre><code>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 </code></pre> <p>you could also pass in a parameter to signify what you are passing in:</p> <pre><code>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 </code></pre> <p>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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload