SQL to solve-CodePudding

CodePudding user response:

Sure you want to function (function) is used to implement?

CodePudding user response:

 

The CREATE FUNCTION [dbo] fn_Shortage () 

RETURNS the @ tblShortage TABLE (so varchar (20), 
The name nvarchar (20), 
Fqty int, 
Owe int) 
The BEGIN 

; With cte_Inventory as (
Select 'TS - 1' as the so, N 'relay' as the name, 100 as fqty union 
TX - SELECT '0' as so, N 'hard' as the name, 2 as fqty), 
Cte_IssueMaterial as (
Select 'TS - 1' as the so, N 'relay' as the name, 200 as fqty union 
SELECT 'TF - 2' as so, N 'relay' as the name, 300 as fqty) 

INSERT INTO @ tblShortage 
The Select a.s o, a.n ame, a.f qty, (isnull (b. qty, 0) - a.f qty) as' owe materials' 
The From cte_IssueMaterial a 
Left the Join cte_Inventory b on a.s o=b.s o 


RETURN 

END 

GO 

Select * From [dbo] fn_Shortage ()

CodePudding user response:

This is only an example, the actual database data quantity is very large