Table tableone
Value |
Catalog |
1 |
Cataloga |
2 |
Catalogb |
3 |
Catalogb |
3 |
Cataloga |
4 |
Cataloga |
6 |
Catalogb |
Requirement: Summarize the values according to the catalog.Product
Only scalar values can be used for input parameters in user-defined functions. Table cannot be used as a parameter input!
The input parameters of the stored procedure are different:
Data type. All data types (includingText,NtextAndImage. However,CursorThe data type can only be used for output parameters. If the specified data type isCursorThe varying and output keywords must also be specified.
Because a user-defined function can return a table.
- Functions can be used in functions.
- Stored procedures cannot be used in functions.
- Functions can be used in stored procedures.
- However, stored procedures cannot be used in stored procedures.
- In fact, stored procedures can use stored procedures. For example, exec procedure1 can be used in procedure2, but it does not make a lot of sense.
Create a UDF
Used to calculate the product of a catalog
Alter function DBO. gettimebycatalog
(
@ Catvarchar (100)
)
Returns real
As
Begin
Declare @ timeall2 real
Set @ timeall2 = 1
Select @ timeall2 = @ timeall2 * cast (ID as float) from tableone
Where catalog = @ catalog
Return @ timeall2
End
Create a stored procedure
Alter procedure DBO. rocktest2
As
Begin
Create Table # temp2
(
Catalog varchar (100)
)
Insert into # temp2 (Catalog) Select distinct catalog from tableone
Select catalog, DBO. gettimebycatalog (Catalog)
From # temp2
End
In this way, we can get a Summary of the product data.
Of course, if you want to add restrictions,You can replace tableone in a function and stored procedure with a user-defined function with a table value.
If you still have a good solution, please advise 12.