SQL Server 2008使用sproc中的函数

I am using this function:

我正在使用这个功能：

ALTER FUNCTION [dbo].[fncGetCustInfo] (@IShopID int)
RETURNS

@CustomerInfo table (ShopID int, CustID int)

AS
BEGIN

INSERT INTO @CustomerInfo
SELECT  ShopID,
CustomerID
FROM dbo.ShopTbl
WHERE ShopID= @IShopID  
RETURN
END

and this is is my stored procedure:

这是我的存储过程：

ALTER PROC [dbo].[GetCustomerInfo]

@IShopID int 
as
BEGIN

declare @CustomerName varchar(50), @ShopD int, @CustomerID int, @Date date

--Return the customer ids using the function I have created
SELECT @CustomerID = fncGetCustInfo.CustomerID FROM dbo.fncGetCustInfo(@IShopID)
--Set the customer names column using the customer ids I retrived using my function
SELECT @CustomerName = CustomerName
from dbo.CustomerTbl
where CustomerID = @CustomerID

Select @Date = GETDATE()

END

My question is about this line in the sproc:

我的问题是关于sproc中的这一行：

SELECT @CustomerID = fncGetCustInfo.CustomerID FROM dbo.fncGetCustInfo(@IShopID)

Will this return me the CustomerIDs from the table I create in the function called CustomerInfo by calling the function I created earlier

这会通过调用我之前创建的函数从我在名为CustomerInfo的函数中创建的表中返回CustomerID

1 个解决方案

#1

The way I do see things my answer will be yes but instead of creating a function to return that result I will embed the function within the proc this way:

我看到的方式我的回答是肯定的，但是我没有创建一个函数来返回结果，而是将这个函数嵌入proc中：

ALTER PROC [dbo].[GetCustomerInfo]
(

@IShopID int 
)
as
BEGIN

declare @CustomerName varchar(50),  @CustomerID int, @Date date   

SET @CustomerID = (SELECT TOP 1 ShopID,CustomerID FROM dbo.ShopTbl WHERE ShopID= @IShopID)


--Set the customer names column using the customer ids I retrived using my     
SELECT @CustomerName = CustomerName
from dbo.CustomerTbl
where CustomerID = @CustomerID

Select @Date = GETDATE()

END

Unless you are going to use the function in a different place but also keep in mind that function can create performance issues.

除非您打算在不同的地方使用该功能，但请记住，该功能可能会产生性能问题。

1 个解决方案

#1

更多相关文章

随机推荐