Stored procedures with parameters and scalar function

In SQL Server, we usually use NEWID (), GETDATE (), and so on some database functions, these functions are very helpful, and then the database can let us write our own functions, namely function, the following simple function of the wording.

Scalar function: The result is a single value that can contain a logical process. It is not possible to use uncertain system functions such as GETDATE ().

CREATE FUNCTION <scalar_function_name, sysname, functionname>
(
--Add your parameters, and their data types
@Parameter1 DataType,

@Parameter2 DataType

)

--Declare your return value type
RETURNS DataType
As
BEGIN
--Declare your return parameters

DECLARE @ReturnValue DataType

--You can invoke the parameters you set for logical operation

SET @ReturnValue = @Parameter1 + @Parameter2

-Return returns the number you want to return
RETURN @ReturnValue

END
GO

-----------------------------------------------------

In this scalar function, cannot use the INSERT statement, I even want to use Select to set the parameter value is not, the specific reason is not checked, but the error message is

: Select statements included within a function cannot return data to a client.

There is time to study the theory later on.

Because the project required me to execute the INSERT statement through the database, function could not satisfy me and go to the stored procedure.

Due to the lack of contact, I do not know much about the difference between stored procedures and function, and I only feel that stored procedures can also set parameters, except that return only returns int type comparison, as if it is stronger than function. Then we have to study ...

CREATE PROCEDURE procedurename
--Add input parameters (no comma separation required), after the data type with the Output property, you can export this parameter
@Parameter1 DataType OUTPUT

@Parameter2 DataType
As
BEGIN
--SET NOCOUNT off/on on indicates that the executed SQL does not return a specific number of affected rows, and OFF returns the number of affected rows
SET NOCOUNT OFF;

--Declaration of internal constants (comma separated required)

Declare @Parameter3 DataType,

Declare @Parameter4 DataType

--Assign values to internal constants (you can query the desired values with the SELECT statement)

SET @Parameter3 = Select Value from Table

--Start executing the DML statement you want to execute

Insert/update/delete

--Remember to assign values to the parameters you want to output (you can use SQL Server's own function to calculate something)

SET @Parameter2 = Select ....
END
GO

--------------------------------------------------------

The stored procedure sets the parameters I want, then I execute the stored procedure, and to add the parameter values, I can do this.

Declare @Value

exec procedurename @Parameter1, @Value OUTPUT

Print @Value

When you're done with the above statement, you'll see the output value you want.

The purpose has been achieved, but some of the principles have not been thoroughly understood, these require me to take the time to find more information, this example is only to roughly write the function module, want to thoroughly grasp, but also need to know more SQL knowledge.

Stored procedures with parameters and scalar function