create proc proc_name @x1 numeric(12,2), @x2 numeric(12,2), @y1 numeric(12,2), @y2 numeric(12,2), @ret numeric(12,2) output as begin set @ret= SQRT(POWER ((@X1-@X2),2)+POWER ((@Y1-@Y2),2)) endgo declare @i numeric(12,2) exec proc_name 3.0,0.0,4.0,2.2,@i output select @i drop proc proc_name/*-------------- 3.50(所影响的行数为 1 行) */
create proc proc_name @x1 numeric(10,2), @x2 numeric(10,2), @y1 numeric(10,2), @y2 numeric(10,2) as declare @s numeric(12,2) begin set @s = SQRT((X2-X1)*(X2-X1) +(Y2-Y1)*(Y2-Y1)) return @s end
请问各位高手这样可以吗? CREATE FUNCTION pdistance(@x1 int,@x2 int,@y1 int,@y2 int) RETURNS int as begin declare @sqrt as int set @sqrt = SQRT((@y2-@y1)*(@y2-@y1) + (@x2-@x1)*(@x2-@x1)) return @sqrt end
RETURNS int 改为RETURNS decimal(18,2) 或更多的小数位,因为取平方根后一般是带小数的多.
CREATE FUNCTION pdistance(@x1 int,@y1 int,@x2 int,@y2 int) RETURNS int as begin declare @dis as int set @dis = SQRT((@y2-@y1)*(@y2-@y1) + (@x2-@x1)*(@x2-@x1)) return @dis end----------------------------------------SELECT dbo.pdistance(10, 10, 9, 10) AS 距离已经OK了,我就要个整数的,感谢大家!!!!!!
insert into tb values(0,0,1,1)
goselect 距离 = SQRT((y2-y1)*(y2-y1) + (x2-x1)*(x2-x1)) from tbdrop table tb/*距离
-----------------------------------------------------
1.4142135623730951(所影响的行数为 1 行)
*/
@x1 numeric(12,2),
@x2 numeric(12,2),
@y1 numeric(12,2),
@y2 numeric(12,2)
as
begin return SQRT(POWER ((X1-X2),2)+POWER ((Y1-Y2),2))
end
insert into tb values(0,0,1,1)
go--直接查询
select 距离 = SQRT((y2-y1)*(y2-y1) + (x2-x1)*(x2-x1)) from tb
go
/*
距离
-----------------------------------------------------
1.4142135623730951(所影响的行数为 1 行)
*/--创建函数
CREATE FUNCTION get_sqrt(@x1 int,@x2 int,@y1 int,@y2 int) RETURNS decimal(18,2)
as
begin
declare @sqrt as decimal(18,2)
select @sqrt = SQRT((y2-y1)*(y2-y1) + (x2-x1)*(x2-x1)) from tb
return @sqrt
end
goselect * , 距离 = dbo.get_sqrt(x1,x2,y1,y2) from tb
/*
x1 y1 x2 y2 距离
----------- ----------- ----------- ----------- --------------------
0 0 1 1 1.41(所影响的行数为 1 行)
*/drop table tb
drop function dbo.get_sqrt
@x1 numeric(12,2),
@x2 numeric(12,2),
@y1 numeric(12,2),
@y2 numeric(12,2)
as
begin return SQRT(POWER (abs(@X1-@X2),2)+POWER (abs(@Y1-@Y2),2))
endgo
declare @i numeric(12,2)
exec @i =proc_name 3.0,0.0,4.0,0.0
select @i
drop proc proc_name/*--------------
5.00(所影响的行数为 1 行)
*/
create proc proc_name
@x1 numeric(12,2),
@x2 numeric(12,2),
@y1 numeric(12,2),
@y2 numeric(12,2)
as
begin return SQRT(POWER ((@X1-@X2),2)+POWER ((@Y1-@Y2),2))
endgo
declare @i numeric(12,2)
exec @i =proc_name 3.0,0.0,4.0,0.0
select @i
drop proc proc_name/*--------------
5.00(所影响的行数为 1 行)
*/
@x1 numeric(12,2),
@x2 numeric(12,2),
@y1 numeric(12,2),
@y2 numeric(12,2),
@ret numeric(12,2) output
as
begin set @ret= SQRT(POWER ((@X1-@X2),2)+POWER ((@Y1-@Y2),2))
endgo
declare @i numeric(12,2)
exec proc_name 3.0,0.0,4.0,2.2,@i output
select @i
drop proc proc_name/*--------------
3.50(所影响的行数为 1 行)
*/
create proc proc_name
@x1 numeric(10,2),
@x2 numeric(10,2),
@y1 numeric(10,2),
@y2 numeric(10,2)
as
declare @s numeric(12,2)
begin
set @s = SQRT((X2-X1)*(X2-X1) +(Y2-Y1)*(Y2-Y1))
return @s
end
CREATE FUNCTION pdistance(@x1 int,@x2 int,@y1 int,@y2 int) RETURNS int
as
begin
declare @sqrt as int
set @sqrt = SQRT((@y2-@y1)*(@y2-@y1) + (@x2-@x1)*(@x2-@x1))
return @sqrt
end
RETURNS int 改为RETURNS decimal(18,2) 或更多的小数位,因为取平方根后一般是带小数的多.
as
begin
declare @dis as int
set @dis = SQRT((@y2-@y1)*(@y2-@y1) + (@x2-@x1)*(@x2-@x1))
return @dis
end----------------------------------------SELECT dbo.pdistance(10, 10, 9, 10) AS 距离已经OK了,我就要个整数的,感谢大家!!!!!!