Create function in sql server

Question

0.00/5 (No votes)

See more:

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

Posted 2-Mar-13 0:12am

sunil mali

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RDBurmon · Accepted Answer · 2013-03-02T00:24:00

Solution 1

SQL

 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

Posted 2-Mar-13 0:24am

RDBurmon

Comments

sunil mali 2-Mar-13 6:40am

Superb Solution sir... Thank You very much...

RDBurmon 3-Mar-13 6:13am

Welcome

gvprabu 2-Mar-13 7:08am

Hi Sunil,

For using like this function u will take the value of Status column directly right.. :-)
If any calculation is there then you will go for functions

sunil mali 6-Mar-13 0:25am

For me it will work like
select EmpName,fnGetEmployeeStatus(EmpId) as Status from Employee

So it will give result as
EmpName Status
SUNIL Cold
MAhesh hot

this statuses are dependent on how important this franchisee is...and so many factors.

willington.d · Accepted Answer · 2013-03-02T00:30:00

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