Day 15

Quiz

1:What is the correct method to return a data value to a calling program?
A1: You should define a variable as output to return a data value to the calling program. Then you must set the variable to the value you want to pass.
create proc p1 (@x int output)
as
select @x = 1
return

2:I have the following procedure and batch, but when I run the batch, the value of @out_x is always null. What's wrong?
create procedure p1 (@x int output)
as
select @x = 1
returndeclare @out_x int
execute p1 @out_x
select @out_x

A2: The reason you are getting a null returned is that you must include the output keyword in the Execute statement as well as the procedure.
declare @out_x int
execute p1 @out_x output
select @out_x

3:In this procedure and batch, my return status is always null. Why?
create procedure p2
as
declare @x int
select @x = 1
if @x = 1
   return 1
else
   return 0

declare @retstat int
execute p2
select @retstat "return"

A3: You need to include a reference to @retstat in the EXECUTE statement as shown:
declare @retstat int
execute @retstat = p2
select @retstat "return"

4:How do I get a list of parameters for a procedure?
A4: Parameters are listed in the object browser. You can also use sp_help to see a list of parameters.

Exercises

1:Write a stored procedure to display the contents of the shopping cart for a customer. Call the prGetCustomerCart procedure in your procedure.
A1: 
create proc prShowItemsInCustomerCart (
    @CustomerID nchar(5)
) as
declare @ret int
declare @OrderID int

exec @ret = prGetCustomerCart
    @CustomerID,
    @OrderID output

if @ret != 0 begin
/* error getting an OrderID – quit now */
    return @ret
end

Select
    [OrderID],
    [ProductID],
    [UnitPrice],
    [Quantity],
    [Discount]
From
    [Northwind].[dbo].[Order Details]
Where
    [OrderID] = @OrderID
/* check for error in select */
if @@error != 0
    return 99
else
    return 0

2:Write a stored procedure to delete the current shopping cart for a customer. Delete both the order details and the order itself.
A2: 
create proc prDeleteCustomerCart (
    @CustomerID nchar(5)
) as

declare @ret int
declare @OrderID int

exec @ret = prGetCustomerCart
    @CustomerID,
    @OrderID output

if @ret != 0 begin
/* error getting an OrderID – quit now */
    return @ret
end

Delete
    [Northwind].[dbo].[Order Details]
Where
    [OrderID] = @OrderID
/* check for error in select */
if @@error != 0
    return 99
else
    return 0

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset