Use a Select Statement for Variable Values

Question

In SQL, I have a table function created which, in its definition, includes a variable called @customerid. Therefore, select statements need to include (@customerid).

When performing a select statement on the table function, I typically declare the variable value as shown below. However, this limits me to one customer. Whereas, I would like to return data on the table function for ALL customers.

DECLARE @customerid INT = 123456
SELECT * FROM custom.tfnCustomerRatings(@customerid)

I have attempted to set the value of the variable as a select statement. However, this throws an error as follows. Do I need to perform this differently? "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

DECLARE @customerid INT
SET @customerid = (SELECT distinct Customerid FROM compliance.CustomerTable WHERE CountryID = 1)
SELECT * FROM custom.tfnCustomerRatings(@customerid)

Thanks in advance.

Answer 1

Using cross apply returns results in 1 min. Using the below returns results in 4 seconds.

SELECT * FROM  custom.tfnCustomerRatings(null)
WHERE CustomerID IN (SELECT DISTINCT CustomerID FROM Compliance.Customer WHERE CountryID = 1)