| Author |
 Topic  |
|
|
rosetulip
Starting Member
4 Posts |
 Posted - 07/11/2012 : 01:31:07
|
I am a new bie to this. I need a function to pass in the PIN and return the IP number as formatted from the right()
create function ( @pin varchar, @IP bigint)
RETURNS bigint
AS
BEGIN
DECLARE @ip_num BIGINT
select 30 + right(PIN,6,3) from network where port_pin = @pin;
RETURN @ip_num;
END;
Please help me correct this function.
This is MS SQL 2008 function.
Also, how to test it if it's correct??? |
|
|
lionofdezert
Aged Yak Warrior
Pakistan
864 Posts |
Posted - 07/11/2012 : 04:21:22
|
CREATE FUNCTION fncMyFunction ( @pin VARCHAR )
RETURNS BIGINT
AS BEGIN
DECLARE @ip_num BIGINT
SELECT @ip_num = 30 + SUBSTRING(PIN, 6, 3)
FROM network
WHERE port_pin = @pin ;
RETURN @ip_num ;
END ;
--------------------------
http://connectsql.blogspot.com/ |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 07/11/2012 : 10:14:37
|
will @pin have only one matching record in network table always? also you dont need @IP parameter at all here
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
rosetulip
Starting Member
4 Posts |
Posted - 07/11/2012 : 15:55:07
|
@pin may have more than one matching record in network table. Also, to be able to test the result, can I use the following
Select * from fncMyFunction (@pin)
--@pin is a data from your network table,
is it the right way to test in this select statement?? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 07/11/2012 : 16:00:23
|
quote:
Originally posted by rosetulip
@pin may have more than one matching record in network table. Also, to be able to test the result, can I use the following
Select * from fncMyFunction (@pin)
--@pin is a data from your network table,
is it the right way to test in this select statement??
yep that correct
but are you aware that this will just return you random result if you've multiple ip values present in your table for same pin value? is this what you really intend to get?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
how to write this function
