I am looking to create a function that given a custID, returns a table of all the different shipping times they have used and how many times they have used each shipping time.
I have the following tables
Customer (custID)
Order (custID, shipID)
Shipping (shipID, time)
The only code I have is the outline:
Create Function ShippingOptions(@int AS int)
Returns @table table (columnName nvarchar(20))
As
Begin
return
End
Using your provided code, your function should look something like this.
CREATE FUNCTION dbo.fn_ShippingOptions(@custId AS int)
Returns TABLE
As
RETURN
SELECT s.time, COUNT(*) as [nr of times used]
FROM dbo.Order o
INNER JOIN dbo.Shipping s ON s.shipID = o.shipID
WHERE o.custId = @custId
GROUP BY s.time
After that, you can call it by using:
SELECT * FROM dbo.fn_ShippingOptions(1)
fn_ShippingOptions(1) work fine or need dbo.fn_ShippingOptions(1)?
CREATE ShippingOptions ShippingOptions (
@InputParam VARCHAR(20)
)
RETURNS TABLE
AS
RETURN
SELECT a.time, COUNT(*) as RecordCount,--All Column that you want to retrun..
FROM CustomerTable a
LEFT OUTER JOIN OrderTable b
ON a.custID=b.custID
LEFT OUTER JOIN ShippingTable c
ON a.custID=c.custID
WHERE a.ColumnName=@InputParam ---you can also put where cond..
Group by a.time
SELECT
*
FROM
ShippingOptions('InputValue'); ---You can call this way to User Defined Function...
Here is the code:
CREATE FUNCTION fn_ShippingOptions(@custId AS int)
Returns TABLE
As
BEGIN
SELECT s.time, COUNT(*) as [nr of times used]
FROM dbo."Order" o
INNER JOIN dbo.Shipping s ON s.shipID = o.shipID
WHERE o.custId = @custId
GROUP BY s.time
RETURN
END
I am unsure how to return all the times used by each custId. Also, I am getting the error "Select statements included within a function cannot return data to a client"
CREATE FUNCTION fn_ShippingOptions(@custId AS int) Returns TABLE As RETURN SELECT s.time, COUNT(*) as [nr of times used] FROM dbo."Order" o INNER JOIN dbo.Shipping s ON s.shipID = o.shipID WHERE o.custId = @custId GROUP BY s.time 1. Remove the BEGIN and END Tag. 2. Move the RETURN right before the SELECT