SQL server storage problem-CodePudding

Create a stored procedure, called proc_3 request implementation according to the order of members nickname query the information, if there is no order books, then output "so-and-so members without ordering books

This is my homework, I also want to do, but want to for a long time, tried a lot of kinds of method or do not come out, can only to inquire of leaders of all stripes

CodePudding user response:

If only you have any purchase member information query, I would, is not the second order books, print "membership number + order book" this won't get, the less than go together

CodePudding user response:

Do not know to have bosses to help save the children

CodePudding user response:

Stored procedure is usually to the program call, will not give users a manager to run, can determine whether records in the program, according to the condition to display the data, can also get an output parameter, no eligible records returned to this sentence

CodePudding user response:

Hello World reference 3 floor, response:

stored procedure is usually to the program call, will not give users a manager to run, can determine whether in the program recording, according to the condition to display the data, can also get an output parameter, no qualified record return

The answer, can you help me, made a record hasn't learned, to find the information didn't understand, the output has been trying not to come out

CodePudding user response:

Give it a try, I built a temporary table, the table in the pro it ourselves under

 
If object_id (' tempdb for.. '# member table) is not null drop table # member table 
The create table # member list (membership number char (4), members nickname varchar (20)) 
Insert into # member table 
Select '0001', 'outstanding students' union all 
Select '0002', 'learning god' 

If object_id (' tempdb for.. Order table # ') is not null drop table # order table 
The create table # order list (book number int, membership number char (4), order quantity int) 
Insert into order # table 
Select 1, '0001', 1 union all 
Select 1, '0001', 2 union all 
Select 2, '0002', 0 union all 
Select 2, '0002', 0 

- member for order quantity 
The set quoted_identifier on 
Go 
Create procedure XXXXXXXXXXXXXX 
@ membership number varchar (4), 
@ MSG varchar (500)='output 
As 
The begin 
Declare @ members nickname varchar (20) 
Declare @ order quantities int 

Select @ members nickname=b. members nickname, @ order number=sum (a. order quantity) 

 the from order # list aLeft the join # member table b on a. membership number=b. membership number 
Where a. Membership number=@ membership number 
Group by a. b. the number of members nickname 

If (isnull (@ order number, 0) & gt; 0) 
The begin 
Select @ MSG='membership number: + @ membership number +'; Members nickname: '+ @ members nickname +'; Order quantity: '+ cast (sum (@ order quantity) as varchar (10)) 
End 
The else 
The begin 
Select @ MSG=@ members nickname + 'members without ordering books' 
End 
- print @ MSG 
End

CodePudding user response:

reference 5 floor _Asura reply:

try, I built a temporary table, the table in the pro it ourselves under

 
If object_id (' tempdb for.. '# member table) is not null drop table # member table 
The create table # member list (membership number char (4), members nickname varchar (20)) 
Insert into # member table 
Select '0001', 'outstanding students' union all 
Select '0002', 'learning god' 

If object_id (' tempdb for.. Order table # ') is not null drop table # order table 
The create table # order list (book number int, membership number char (4), order quantity int) 
Insert into order # table 
Select 1, '0001', 1 union all 
Select 1, '0001', 2 union all 
Select 2, '0002', 0 union all 
Select 2, '0002', 0 

- member for order quantity 
The set quoted_identifier on 
Go 
Create procedure XXXXXXXXXXXXXX 
@ membership number varchar (4), 
@ MSG varchar (500)='output 
As 
The begin 
Declare @ members nickname varchar (20) 
Declare @ order quantities int 

Select @ members nickname=b. members nickname, @ order number=sum (a. order quantity) 

 the from order # list aLeft the join # member table b on a. membership number=b. membership number 
Where a. Membership number=@ membership number 
Group by a. b. the number of members nickname 

If (isnull (@ order number, 0) & gt; 0) 
The begin 
Select @ MSG='membership number: + @ membership number +'; Members nickname: '+ @ members nickname +'; Order quantity: '+ cast (sum (@ order quantity) as varchar (10)) 
End 
The else 
The begin 
Select @ MSG=@ members nickname + 'members without ordering books' 
End 
- print @ MSG 
End

Could you tell me the front two the if statement is what meaning

CodePudding user response:

Borrow the code above, slightly change:

 
If object_id (' tempdb for.. '# member table) is not null drop table # member table 
The create table # member list (membership number char (4), members nickname varchar (20)) 
Insert into # member table 
Select '0001', 'outstanding students' union all 
Select '0002', 'learning god' 

If object_id (' tempdb for.. Order table # ') is not null drop table # order table 
The create table # order list (book number int, membership number char (4), order quantity int) 
Insert into order # table 
Select 1, '0001', 1 union all 
Select 1, '0001', 2 union all 
Select 2, '0002', 0 union all 
Select 2, '0002', 0 

- member for order quantity 
The set quoted_identifier on 
Go 
Create procedure XXXXXXXXXXXXXX 
@ membership number varchar (4) 
As 
The begin 
Declare @ members nickname varchar (20) 
Declare @ order quantities int 
Declare @ MSG varchar (200) 
Select @ members nickname=b. members nickname, @ order number=sum (a. order quantity) 

 the from order # list aLeft the join # member table b on a. membership number=b. membership number 
Where a. Membership number=@ membership number 
Group by a. b. the number of members nickname 

If (isnull (@ order number, 0) & gt; 0) 
The begin 
Select @ MSG='membership number: + @ membership number +'; Members nickname: '+ @ members nickname +'; Order quantity: '+ cast (sum (@ order quantity) as varchar (10)) 

End 
The else 
The begin 
Select @ MSG=@ members nickname + 'members without ordering books' 
Raiserror (@ MSG, 16, 1) 
End 



End

CodePudding user response:

refer to 6th floor bright moon don't shoot jing crane response:

Quote: refer to the fifth floor _Asura reply:

try, I built a temporary table, the table in the pro it ourselves under

 
If object_id (' tempdb for.. '# member table) is not null drop table # member table 
The create table # member list (membership number char (4), members nickname varchar (20)) 
Insert into # member table 
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull