Case Statement in SQL Stored Procedure

create procedure getdataRaj
        @en varchar(20),@s int
        as
        begin
                select dbo.Emp100.Eno,ename,sal    from Emp100
                where ename =
                            CASE ename   
                                    WHEN @en THEN ename
                            end
                            or sal=
                            CASE sal
                                    WHEN @s THEN Sal
                            end
        end
                       

select * from Emp100       
exec getdataRaj    Null,NULL
exec getdataRaj    'Jaggu',NULL
exec getdataRaj    NULL,9000
exec getdataRaj 'SAI',NULL

What are the differences between stored procedure and functions

functions are used for computations where as procedures 
can be used for performing business logic
2) functions MUST return a value, procedures need not be.
3) you can have DML(insert, update, delete) statements in a 
function. But, you cannot call such a function in a SQL 
query..eg: suppose, if u have a function that is updating a 
table.. you can't call that function in any sql query.- 
select myFunction(field) from sometable; will throw error.
4) function parameters are always IN, no OUT is possible

Procedure may return none or more values.Function must always return one value either a scalar value or a table.
Procedure have input,output parameters.Functions have only input parameters.
Stored procedures are called independently by EXEC command whereas Functions are called from within SQL statement.
Functions can be called from procedure.Procedures cannot be called from function.
Exception can be handled in Procedure by try-catch block but try-catch block cannot be used in a function.(error-handling)
Transaction management possible in procedure but not in function.




exec ufn_GetMaxValue 1,2,3,4
CREATE FUNCTION [dbo].[ufn_GetMaxValue]
( @pInt1 INT, @pInt2 INT, @pInt3 INT, @pInt4 INT )
RETURNS INT
AS
BEGIN
DECLARE @IntTable TABLE ( [IntValue] INT )
DECLARE @MaxValue INT

INSERT INTO @IntTable ( [IntValue] ) VALUES ( @pInt1 )
INSERT INTO @IntTable ( [IntValue] ) VALUES ( @pInt2 )
INSERT INTO @IntTable ( [IntValue] ) VALUES ( @pInt3 )
INSERT INTO @IntTable ( [IntValue] ) VALUES ( @pInt4 )

SELECT @MaxValue = MAX( [IntValue] )
FROM @IntTable

RETURN @MaxValue
END
GO