在sql server中创建函数 [英] Create function in sql server
问题描述
大家好,
我想用sql中的参数创建函数
我的函数应该看起来像这样
功能乐趣(int EmpId)
{
返回状态;
}
i有选择声明需要来自此功能的所有员工状态
从员工中选择EmpId,EmpName,PhoneNo, fun(EmpId)
fun(EmpId)应该是函数fun返回的值/>
请帮我这些....
提前致谢
CREATE 功能 fnGetEmployeeStatus
( @ EmpId INT
)
RETURNS INT
AS
BEGIN
DECLARE @状态 AS INT
SELECT @ Status = Status FROM 员工 WHERE Empid = @EmpId
RETURN @ Status
结束
希望这有帮助
--RDBurmon
示例
创建功能[dbo]。[STRINGSPLIT](@ SOURCESQL VARCHAR(8000),@ STRSEPRATE VARCHAR( 10),@ POSITION INT)
RETURNS VARCHAR(100) - RETURNS @TEMP TABLE(A VARCHAR(100))
BEGIN
DECLARE @I INT
DECLARE @J INT
DECLARE @TEMP VARCHAR(100)
SET @ SOURCESQL = RTRIM(LTRIM(@SOUR) CESQL))
SET @ I = CHARINDEX(@ STRSEPRATE,@ SOURCESQL)
SET @J = 1;
WHILE @ I> = 1
BEGIN
IF @j = @POSITION
BEGIN
SET @TEMP = LEFT(@ SOURCESQL,@ I-1)
--INSERT @TEMP VALUES(LEFT(@ SOURCESQL,@ I-1))
END
SET @ SOURCESQL = SUBSTRING(@ SOURCESQL,@ I + 1,LEN(@) SOURCESQL) - @ I)
SET @ I = CHARINDEX(@ STRSEPRATE,@ SOURCESQL)
SET @j = @j + 1;
END
IF @ SOURCESQL<>''\''和@j = @POSITION
BEGIN
--INSERT @TEMP VALUES(@SOURCESQL)
SET @TEMP = @ SOURCESQL
END
RETURN @TEMP
END
使用下面的函数
这个函数拆分给定的字符串(''willington,robinson'')并拆分('','')并返回位置字符串价值
选择1作为Col,dbo.STRINGSPLIT(''willington,robinson'','','',1)来自用户
Hello all,
I want to create function with parameter in sql
my function should look something like this
function fun(int EmpId)
{
return status;
}
i have select statement which needs status came from this function for all employees
select EmpId,EmpName,PhoneNo,fun(EmpId) from Employee
fun(EmpId) should be a value returned by function fun
Please help me with these....
Thanks in advance
CREATE FUNCTION fnGetEmployeeStatus ( @EmpId INT ) RETURNS INT AS BEGIN DECLARE @Status AS INT SELECT @Status=Status FROM Employee WHERE Empid=@EmpId RETURN @Status END
Hope this helps
--RDBurmon
EXAMPLE
CREATE FUNCTION [dbo].[STRINGSPLIT](@SOURCESQL VARCHAR(8000),@STRSEPRATE VARCHAR(10), @POSITION INT) RETURNS VARCHAR(100) --RETURNS @TEMP TABLE(A VARCHAR(100)) BEGIN DECLARE @I INT DECLARE @J INT DECLARE @TEMP VARCHAR(100) SET @SOURCESQL=RTRIM(LTRIM(@SOURCESQL)) SET @I=CHARINDEX(@STRSEPRATE,@SOURCESQL) SET @J = 1; WHILE @I>=1 BEGIN IF @j = @POSITION BEGIN SET @TEMP = LEFT(@SOURCESQL,@I-1) --INSERT @TEMP VALUES(LEFT(@SOURCESQL,@I-1)) END SET @SOURCESQL=SUBSTRING(@SOURCESQL,@I+1,LEN(@SOURCESQL)-@I) SET @I=CHARINDEX(@STRSEPRATE,@SOURCESQL) SET @j = @j + 1; END IF @SOURCESQL<>''\'' AND @j = @POSITION BEGIN --INSERT @TEMP VALUES(@SOURCESQL) SET @TEMP = @SOURCESQL END RETURN @TEMP END
Use the function like below
this function split the given string(''willington,robinson'') and split by ('','') and returns position ones value
SELECT 1 as Col, dbo.STRINGSPLIT(''willington,robinson'', '','',1) FROM USERS
这篇关于在sql server中创建函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!