写一个function自动返回类型。
-- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Alex
-- Create date:
-- Description:
-- =============================================
CREATE FUNCTION GetType
(
-- Add the parameters for the function here
@p1 int
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @Result int -- Add the T-SQL statements to compute the return value here
SELECT @Result = ID
FROM T2
Where @p1 between minVol and maxVol -- Return the result of the function
RETURN @ResultEND
GO表结构如下:create table t1
(id int identity(1,1) not null primary key,
Vol int,
type int)
gocreate table t2
(id int identity(1,1) not null primary key,
minvol int,
maxvol int)
goinsert t1 values(5, '')
insert t1 values(10, '')
insert t1 values(14, '')
insert t1 values(17, '')insert t2 values(0, 10)
insert t2 values(11, 15)
insert t2 values(16, 25)--select * from t1
--select * from t2update t1
set type = dbo.GetType(Vol)select * from t1
-- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Alex
-- Create date:
-- Description:
-- =============================================
CREATE FUNCTION GetType
(
-- Add the parameters for the function here
@p1 int
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @Result int -- Add the T-SQL statements to compute the return value here
SELECT @Result = ID
FROM T2
Where @p1 between minVol and maxVol -- Return the result of the function
RETURN @ResultEND
GO表结构如下:create table t1
(id int identity(1,1) not null primary key,
Vol int,
type int)
gocreate table t2
(id int identity(1,1) not null primary key,
minvol int,
maxvol int)
goinsert t1 values(5, '')
insert t1 values(10, '')
insert t1 values(14, '')
insert t1 values(17, '')insert t2 values(0, 10)
insert t2 values(11, 15)
insert t2 values(16, 25)--select * from t1
--select * from t2update t1
set type = dbo.GetType(Vol)select * from t1
解决方案 »
- sql 日期拆分
- Rowlock 与Updlock 的区别
- 这两个表约束要怎么建啊
- 给存储过程中in条件传参数
- sql server2000 备份过程中可以写入数据记录么?
- 关于数据库恢复问题,我用命令restore database film from disk = 'f:\film.bak'为什么会出错
- sql的查询结果怎样自动生成一个文本文件呢
- 请问:一个标示符字段,varchar(20),如何自动生成'YYYYMMDD'+XXX的形式?
- 高手帮帮忙好吗:请教关于开发vb+sqlserver2000存取大字段类型(text)的问题
- Access中查询中的字段求和问题。
- sqlserver2000订阅问题
- 期待指点.....
set type = dbo.GetType(Vol)
上面用于调用function,并更t1。
下面是结果。id Vol type
----------- ----------- -----------
1 5 1
2 10 1
3 14 2
4 17 3(4 row(s) affected)
drop table t1
drop table t2
create table t1
(id int identity(1,1) not null primary key,
Vol int,
type int)
gocreate table t2
(id int identity(1,1) not null primary key,
minvol int,
maxvol int)
goinsert t1 values(5, '')
insert t1 values(10, '')
insert t1 values(14, '')
insert t1 values(17, '')insert t2 values(0, 10)
insert t2 values(11, 15)
insert t2 values(16, 25)
select t1.* ,t2.id as type from t1 t1, t2 t2 where t1.Vol between t2.minvol and t2.maxvol
/*
id Vol type type
----------- ----------- ----------- -----------
1 5 0 1
2 10 0 1
3 14 0 2
4 17 0 3(4 row(s) affected)*/