Call a function from within a Stored Procedure
-
Guys hi,
I need your help on this. I have created a function that it finds and substracts all letters from specific codes that are contained in a table.
The function is as follows
CREATE FUNCTION fn_GetNumber (@strNumber varchar(50))
RETURNS int AS
BEGIN
DECLARE @number varChar(50)
DECLARE @result int
DECLARE @len int
DECLARE @i int
SET @result = null
SET @number = ''
SET @len=LEN(@strNumber)
SET @i=0
while @i<@len
begin
SET @i = @i+1
if (IsNumeric(SubString(@strNumber,@i,1)) = 1)
begin
set @number = @number + SubString(@strNumber,@i,1)
end
end
if (@number <> '')
begin
set @result = CONVERT(int,@number)
end
return @result
END
I want to create a stored procedure that calls this function, and extracts from a table field called 'card_number' of the table 'candidates' all the numbers.
How will i call the function within the stored procedure?
I am new to SQL so your please explain a bit.
Thank you,
Dionisis
-
CREATE Procedure GetCardNumber
as
BEGIN
SET NOCOUNT ON
Select dbo.fn_GetNumber(card_number) from candidates
SET NOCOUNT OFF
END
Hope this is what you want
Thanks
Brij
-
Dionisis,
I'd say your function has a bit of a bug in it...
SELECT dbo.fn_GetNumber('123-456')
It choked on that pretty hard, I'd say. The problem is that ISNUMERIC allows the "+", "-", "$", ",", and a bunch of other symbology as numeric characters. It's not a bug... ISNUMERIC was never intended to be an ISALLDIGITS function.
I've embolded the changes you need to make to your function to turn it into a ConvertToDigits function...
CREATE FUNCTION fn_GetNumber (@strNumber varchar(50))
RETURNS int AS
BEGIN
DECLARE @number varChar(50)
DECLARE @result int
DECLARE @len int
DECLARE @i int
SET @result = null
SET @number = ''
SET @len=LEN(@strNumber)
SET @i=0
while @i<@len
begin
SET @i = @i+1
if SubString(@strNumber,@i,1) NOT LIKE '%[^0-9]%'
begin
set @number = @number + SubString(@strNumber,@i,1)
end
end
if (@number <> '')
begin
set @result = CONVERT(int,@number)
end
return @result
END
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thank you both for your help!
Jeff, when i saw your edit, i couldn't help laugh, and think that you are the god of SQL!!!
lol lol lol ..... very very impressive mate! 🙂 i am speechless, and i mean it! i will never stop learning from people like you (both of you, that answered) and this is why i love sqlcentral! 🙂
Cheers,
DF
-
Thanks for the compliment but I'm no God of SQL
... even after 11 years with SQL Server, I still make some really whacko 
code once in a while... I gotta agree on the other part, though... I've learned lot's of the tricks of the trade right here on SQLSeverCentral... great place to get lot's of info.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply