Table-valued function and passing parameters to it

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

SQL Server 2005 Forums

Transact-SQL (2005)

Table-valued function and passing parameters to it

Author

Topic

Wodzu
Yak Posting Veteran

58 Posts

Posted - 2009-08-18 : 06:44:35

Hi.

I have a table-valued function which looks like this:

CREATE FUNCTION dbo.OrderItems(@OrderID)
RETURNS @Items TABLE
	(
		ItemID int,
		ItemName varchar(40),
		ItemQuanity decimal(15,2)
	)
AS
BEGIN
	INSERT @Items
	SELECT Item_ID, Item_Name, Item_Quanity
	FROM dbo.Items
	WHERE Item_OrderID = @OrderID
RETURN
END

Now, I would like to use this function in a query and select Items from multiple Orders. In other words, I would like to pass multiple @OrderID into this function from other table "at once". I would like to do this in one query.

Is this possible?

Thanks for your time.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-18 : 06:48:58

Yes. Use CROSS APPLY.

SELECT t1.*, f.*
FROM Table1 AS t1
CROSS APPLY dbo.OrderItems(t1.OrderID) AS f

N 56°04'39.26"
E 12°55'05.63"

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-18 : 06:52:03

YES,
IN WHERE CONDITION USE LIKE OR PATINDEX KEYWORDS

WHERE PATINDEX( '%,'+CAST(Item_OrderID AS VARCHAR(MAX))+',%' ,'%,'+@OrderID +',%')>0
(OR)
WHERE '%,'+@OrderID +',%' like '%,'+CAST(Item_OrderID AS VARCHAR(MAX))+',%'

Wodzu
Yak Posting Veteran

58 Posts

Posted - 2009-08-18 : 07:03:00

quote:
Originally posted by Peso

Yes. Use CROSS APPLY.

SELECT t1.*, f.*
FROM Table1 AS t1
CROSS APPLY dbo.OrderItems(t1.OrderID) AS f

N 56°04'39.26"
E 12°55'05.63"

Thank you Peso I've found the same solution. Is there a way to do this in SQL 2000?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources