Inline-table-valued functions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Inline-table-valued functions

Author

Topic

saltwater
Starting Member

4 Posts

Posted - 2007-05-01 : 00:56:16

Help! Been doing the box step with BOL for several hours , Using tables in Adventureworks to create inline-table-valued function to provide a parameterized view of three JOINS - Have sucessfully created the function but can't figure out where to 'Declare' my variable "@SalesAgentID" need to be able to invoke the function with a particular ID - If you can help me cut this dance short I would REALLY Appreciate it.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-01 : 00:57:20

Please post your code.

saltwater
Starting Member

4 Posts

Posted - 2007-05-01 : 01:10:05

Wow I thought I was the only guy awake. Here is the code

Use Adventureworks
go
CREATE FUNCTION dbo.udf_SalesInStore2
(@StoreID Int)
RETURNS table
AS

RETURN

(
SELECT Production.Product.ProductID, Name, Sales.SalesOrderDetail.SalesOrderID
FROM Production.Product
JOIN Sales.SalesOrderDetail
ON Production.Product.ProductID = Sales.SalesOrderDetail.ProductID
JOIN Sales.SalesOrderHeader
ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID

WHERE CustomerID = @StoreID)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-01 : 01:19:05

"but can't figure out where to 'Declare' my variable "@SalesAgentID" need to be able to invoke the function with a particular ID"
You mean how to invoke the function ?


declare @SalesAgentID varchar(10)
select  @SalesAgentID = <somevalue>
select *
from   dbo.udf_SalesInStore2(@SalesAgentID)

saltwater
Starting Member

4 Posts

Posted - 2007-05-01 : 01:43:03

KH,
Thanks but I had tried that it stills yields all the rowqs in the table it should be less than a dozen. The variable is actually @StoreID instead of @SalesAgent but same princible I want to set @StoreID to 11006 to list that particular stores sales. Sounds easy enough but I have been going in circles with it

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-01 : 02:06:38

[code]
SELECT Production.Product.ProductID, Name, Sales.SalesOrderDetail.SalesOrderID
FROM Production.Product
JOIN Sales.SalesOrderDetail
ON Production.Product.ProductID = Sales.SalesOrderDetail.ProductID
JOIN Sales.SalesOrderHeader
ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
WHERE CustomerID = 11006

or

SELECT *
FROM dbo.udf_SalesInStore2(11006)
[/code]

Both will give 5 rows

saltwater
Starting Member

4 Posts

Posted - 2007-05-01 : 02:40:10

KH,
THANKS! That works. I know there was supposed to be another soution using a DECLARE but I have two solutions that work so I am going to call it a night. Thanks again for your quick and expert assistance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-01 : 02:53:37

"I know there was supposed to be another soution using a DECLARE"
Sorry, still don't quite understand about your question on the DECLARE part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources