Here is the function I'm trying to write. The purpose is to replace MS Access Val() function. I'm not finished with the logic, I'm just trying to get this much to work now. Here is the function:create function DBO.NumValue
-- This function will get the numbers from the front of a field
-- and return the value of those numbers in a numeric data type
(@.mNumInput as charvar(100))
RETURNS numeric
AS
BEGIN
declare @.x as tinyint
declare @.x1 as tinyint
SET @.x = 1
WHILE IsNumeric(SubString(@.mNumInput, @.x, 1))
BEGIN
SET @.x1 = @.x
SET @.x = @.x + 1
CONTINUE
END
If @.x1 > 0
BEGIN
RETURN CAST(LEFT(@.mNumInput, @.x1), Numeric
END
END
Here are the two error messages I'm getting from this function.Server: Msg 156, Level 15, State 1, Procedure NumValue, Line 12
Incorrect syntax near the keyword 'BEGIN'.
Server: Msg 156, Level 15, State 1, Procedure NumValue, Line 20
Incorrect syntax near the keyword 'END'. I have no idea what these two error messages mean.
TIA,Here is the function I'm trying to write. The purpose is to replace MS Access Val() function. I'm not finished with the logic, I'm just trying to get this much to work now. Here is the function:create function DBO.NumValue
-- This function will get the numbers from the front of a field
-- and return the value of those numbers in a numeric data type
(@.mNumInput as charvar(100))
RETURNS numeric
AS
BEGIN
declare @.x as tinyint
declare @.x1 as tinyint
SET @.x = 1
WHILE IsNumeric(SubString(@.mNumInput, @.x, 1))
BEGIN
SET @.x1 = @.x
SET @.x = @.x + 1
CONTINUE
END
If @.x1 > 0
BEGIN
RETURN CAST(LEFT(@.mNumInput, @.x1), Numeric
END
END
Here are the two error messages I'm getting from this function. I have no idea what these two error messages mean.
TIA,
The error messages mean you have syntax errors in your function definition.
You've got a few issues:
1. there's no such type as charvar. I think you mean varchar
2. a while condition needs to evalulate to a boolean. but isnumeric returns 1 or 0 (an int). so you need this: WHILE IsNumeric(SubString(@.mNumInput, @.x, 1)) = 1
3. your cast at the end has syntax errors.
4. you don't return a value at the end if the IF isn't satisfied. all functions must return a value. Here's your corrected function:
create function DBO.NumValue
-- This function will get the numbers from the front of a field
-- and return the value of those numbers in a numeric data type
(@.mNumInput as varchar(100))
RETURNS numeric
AS
BEGIN
declare @.x as tinyint
declare @.x1 as tinyint
SET @.x = 1
WHILE IsNumeric(SubString(@.mNumInput, @.x, 1)) = 1
BEGIN
SET @.x1 = @.x
SET @.x = @.x + 1
CONTINUE -- continue isn't necessary here, but whatever.
END
If @.x1 > 0
BEGIN
RETURN CAST(LEFT(@.mNumInput, @.x1) as Numeric)
END
return -1 -- if no digits found at the beginning of the string
END
sample usage:
select dbo.NumValue('asdf') -- returns -1
select dbo.NumValue('555asdf') -- returns 555|||jezemine,
Thank you VERY much for your reply. I'm a VBA programmer, so this T-SQL coding language is still a little awkward, therefore, this question:
WHILE IsNumeric(SubString(@.mNumInput, @.x, 1)) = 1
BEGIN
SET @.x1 = @.x
SET @.x = @.x + 1
CONTINUE -- continue isn't necessary here, but whatever.
END
You said the Continue is not necessary here, but I thought the Continue was the "Loop back to the Where statement" command. How else do you get the "Where" to loop?
Thanks for your patience!|||it will loop from the point where you have END.
you only use continue if you want to jump back to the beginnign of the loop before END, that is, if you have statements between continue and end that you don't want to be executed on a particular pass thru the loop.
Certainly they have while loops in VB, don't they?|||Of course there are loops in VB, but with differing syntax. Within VB, there is no equivilent to "Continue" in T-SQL. Rather than using a command statement such as "Continue", in VB if there are lines after a certain point that should not be executed, that would be done with an If statement. There is only one Loop point in VB. So, this was not a conceptual question (what is a loop?) but a syntax question because of a new language. And, this is the first language for me that uses the concept of a Begin/End I have seen enough of Java and C to know that both of them have that same concept, but as far as writing in a language that uses this concept, this is new and different.
Thanks a ton for your help and I will be asking some more questions, soon.|||so in VB they have while loops, but no way to go back to the beginning of the loop early?
I know nothing of VB, my question was an honest one. :)|||Sample VB While Loop:
While TestIsTrue
i = i + 1
If i > 5 then
j = j + 2
End If
WEnd 'End of While, therefore loop until TestIsTrue becomes equal to False.
Same While loop in T-SQL (as I understand it)
While @.TestIsTrue
Begin
@.i = @.i + 1
If @.i <= 5 Continue
@.j = @.j + 2
End
I'm not sure I got the T-SQL syntax correct, but I tried. :)
And yes, I know there is no way out of my loop, but I really don't think it was that important.|||i see.
In T-SQL, it's more similar to what you have in C/C++ (you can use continue to jump to the top, or break to break out)
For flow control in sql, a good resource is BOL:
http://msdn2.microsoft.com/en-us/library/ms189826.aspx
http://msdn2.microsoft.com/en-us/library/ms178642.aspx (funny thing about the first example on this page is they have a noop continue just like you had :))
