function doubt - 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

General SQL Server Forums

New to SQL Server Programming

function doubt

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-02-04 : 01:09:29

Dear All,
i've a function like this
CREATE FUNCTION f1 (@inId CHAR(2))
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @strName AS VARCHAR(50)
SET @strName =
CASE @inId
WHEN '1' THEN 'Active'
WHEN '2' THEN 'InActive'
WHEN '3' THEN 'Closed'
end
return @strName
end

while we are using this in application, it is taking almost 20Min of time.

sometimes the input is going to be null. is that be a reason for this?

then can i put there
else null

thank you in advance for the time

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-04 : 01:41:18

You could make the function "inline".

CREATE FUNCTION f1
(
	@inId CHAR(2)
) 
RETURNS VARCHAR(20) 
AS 
BEGIN 
	RETURN	CASE @inId 
			WHEN '1' THEN 'Active' 
			WHEN '2' THEN 'InActive' 
			WHEN '3' THEN 'Closed' 
			ELSE 'unknown'
		END
END

E 12°55'05.25"
N 56°04'39.16"

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-02-04 : 02:52:29

Thank you Peso,
if i didn't put the ELSE 'unknown' statment at the last, and the @inid is null, then what will happens?

please let me know

Vinod
Even you learn 1%, Learn it with 100% confidence.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-04 : 03:26:35

CASE will return NULL and function will return same to user.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-02-04 : 04:26:05

so there is no need to put
ELSE 'unknown'
at the last.....
am i correct????visakh??

Vinod
Even you learn 1%, Learn it with 100% confidence.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-02-04 : 04:27:26

and for 2 lakhs of records, is it meaningful not putting the ELSE 'unknown' statement at the last???

Vinod
Even you learn 1%, Learn it with 100% confidence.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-04 : 04:41:10

Yeah its just that you wil get NULL returned for them

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-04 : 06:10:46

or have a lookup table with indid defenitions and join with it

Create table ind_ids(indid int, descriptions varchar(10))
insert into ind_ids
select 1,'Active' union all
select 2,'InActive' union all
select 3,'Closed'

Select m.cols,i.description from main_table m inner join ind_ids i on m.ind_id=i.ind_id

Madhivanan

Failing to plan is Planning to fail

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-02-04 : 07:00:46

Great Help
thank you very much

Vinod
Even you learn 1%, Learn it with 100% confidence.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources