Better way to use SQL Server function?

Question

Hey guys I want to use SQL Server function,

I never use function in SQL Server and I only know that function must return a value so I have two stored procedure (1 for insert and 1 for select)

My stored procedures look like this

For insert:

create proc INS_tblteststud  
@stdtid int=null,  
@name varchar(50) =null,  
@fullname varchar(50)=null,  
@address varchar(50)=null,  
@city varchar(50)=null,  
@country varchar(50)=null,  
@contno varchar(50)=null
as  
begin  
insert into tbl_student_test(name,fullname,address,city,country,contno)  
values  
(@name,@fullname,@address,@city,@country,@contno)  
end

And for select:

Create proc SEL_tblteststud  
as
begin
select * from tbl_student_test
end

Now I want to know, how can I convert these statements(Insert, Select) into functions? And which is better to use stored procedure or function?

You'd better read about functions because your "knowing" of functions is wrong. Functions cannot modify data and functions must not return a value, it's the worst case whan function returns a value (scalar function) — sepupic, Jun 14 '17 at 08:11
@sepupic thank you can you please recommend me something from the internet? — , Jun 14 '17 at 08:13
https://learn.microsoft.com/en-us/sql/relational-databases/user-defined-functions/create-user-defined-functions-database-engine — sepupic, Jun 14 '17 at 08:14
https://stackoverflow.com/questions/1179758/function-vs-stored-procedure-in-sql-server — Bridge, Jun 14 '17 at 10:38

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

You are mixing procedure and function...

A procedure is meant for doing something
A function is meant for reading only

It is not allowed to place data changing commands within a function.

What you are trying is just not possible...

UPDATE Some insight about functions and procedures

There are three types of functions

scalar functions: Return one scalar value and are very bad performing
multi statement table valued functions (with BEGIN ... END): Return a resultset, but are bad performing too
inline table valued functions: They are great, but restricted. Think of them like of a VIEW with pre-compiled parameters

A procedure has a return value too, which is of tpye INT and is not used to return the SP's result, but kind of execution status.

A SP might just do something and return no procedure result at all. If you want to return procedural results you must either use

output parameters
or you must call a SELECT within your SP. It is possible to grab the result of an internal SELECT from outside, but this is very cumbersome...

Better way to use SQL Server function?

1 Answers1

UPDATE Some insight about functions and procedures