how to write this function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

how to write this function

Author

Topic

rosetulip
Starting Member

15 Posts

Posted - 2012-07-11 : 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

885 Posts

Posted - 2012-07-11 : 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

52326 Posts

Posted - 2012-07-11 : 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

15 Posts

Posted - 2012-07-11 : 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

52326 Posts

Posted - 2012-07-11 : 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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources