Passing N values from SQL table to SQL function and return back the results

Harreni
Harreni used Ask the Experts™
on
Hi Experts,

I have 10 values stored inside a SQL table column and want to pass them to my function below and return me the results. So, what the steps to achieve this?

ALTER FUNCTION [dbo].[PassVariableList] ( @ConsumerName nvarchar(255) )
RETURNS TABLE
AS
RETURN
(
SELECT C.ConsumerID, C.ConsumerName, C.CurrentlyConsumer, COUNT(distinct DSI.ServiceID) #OfServicesRelatedDatasets,COUNT(distinct DSI.DatasetID) #OfRequiredDatasest
FROM Consumers C
INNER JOIN Services S ON C.ConsumerID = S.ConsumerID
INNER JOIN Datasets_Services_Intersection DSI ON S.ServiceID = DSI.ServiceID
INNER JOIN Datasets DS ON DSI.DatasetID = DS.DatasetID 
where 
C.ConsumerName like N'%'+@ConsumerName+'%'
GROUP BY C.ConsumerID, C.ConsumerName, C.CurrentlyConsumer
order by #OfServicesRelatedDatasets desc
);

Open in new window


Thanks a lot.
Harreni
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior .Net Consultant
Top Expert 2016
Commented:
Probably the easiest way is to use a TVP (table value parameter). check http://emoreau.com/Entries/Articles/2010/11/SQL-Table-Value-parameters.aspx
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
Use a splitter function and compare the individual values.  DelimitedSplit8K is a well-known, highly efficient splitter; you can Google that name to get the source code for the function.

Btw, I removed the ORDER BY as it's not reliable in functions anyway:


ALTER FUNCTION [dbo].[PassVariableList] ( @ConsumerName nvarchar(255) )
RETURNS TABLE
AS
RETURN
(
SELECT C.ConsumerID, C.ConsumerName, C.CurrentlyConsumer, COUNT(distinct DSI.ServiceID) #OfServicesRelatedDatasets,COUNT(distinct DSI.DatasetID) #OfRequiredDatasest
FROM Consumers C
INNER JOIN Services S ON C.ConsumerID = S.ConsumerID
INNER JOIN Datasets_Services_Intersection DSI ON S.ServiceID = DSI.ServiceID
INNER JOIN Datasets DS ON DSI.DatasetID = DS.DatasetID

where EXISTS(SELECT 1 FROM dbo.DelimitedSplit8K(@ConsumerName, ',') ds WHERE C.ConsumerName = ds.Item)
GROUP BY C.ConsumerID, C.ConsumerName, C.CurrentlyConsumer
);

Author

Commented:
Thanks a lot Éric & Scott for your prompt help and explanations.

I tried one of Microsoft MSDN article as HERE  because I'm beginner and I tried the above solutions and I feel it's a bit complex.

Now, I want the SP to accepts the values passed to it and do the inquiry and display me the results, so please help me:

/* Create a table type. */
CREATE TYPE CusResults AS TABLE 
( 
  ConsumerID	NVARCHAR(50) NULL 
, ConsumerName	NVARCHAR(255) NULL 
, CurrentlyConsumer	bit
, NumberOfService	int 
, NumberOfDatasets  int
);
GO

/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE dbo. SP_RecivedDataForTBV @TVP CusResults READONLY
AS 
SET NOCOUNT ON

SELECT C.ConsumerID, C.ConsumerName, C.CurrentlyConsumer, COUNT(distinct DSI.ServiceID) #OfServicesRelatedDatasets,COUNT(distinct DSI.DatasetID) #OfRequiredDatasest
FROM Consumers C
	INNER JOIN Services S ON C.ConsumerID = S.ConsumerID
	INNER JOIN Datasets_Services_Intersection DSI ON S.ServiceID = DSI.ServiceID
	INNER JOIN Datasets DS ON DSI.DatasetID = DS.DatasetID 
Where C.ConsumerID = @Passed list Of Values
GROUP BY C.ConsumerID, C.ConsumerName, C.CurrentlyConsumer
        SELECT *
        FROM  @TVP;
        GO

/* Declare a variable that references the type. */
DECLARE @CusResults AS CusResults;

/* Add data to the table variable. */
INSERT INTO @CusResults (ConsumerID,ConsumerName, CurrentlyConsumer, NumberOfService, NumberOfDatasets)
SELECT top 10 C.ConsumerID
FROM Consumers C

/* Pass the table variable data to a stored procedure. */
EXEC SP_RecivedDataForTBV @CusResults;
GO

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Éric MoreauSenior .Net Consultant
Top Expert 2016
Commented:
If at least we could understand what you are trying to achieve! It is not clear to me.

From what I see, your SP has 2 select statements. Right? When you execute line 28 to 38, do you see your 2 resultsets?

Now you want to join the 2 resultsets together? Change the query in your SP for:
SELECT C.ConsumerID, C.ConsumerName, C.CurrentlyConsumer, COUNT(distinct DSI.ServiceID) #OfServicesRelatedDatasets,COUNT(distinct DSI.DatasetID) #OfRequiredDatasest
FROM Consumers C
	INNER JOIN Services S ON C.ConsumerID = S.ConsumerID
	INNER JOIN Datasets_Services_Intersection DSI ON S.ServiceID = DSI.ServiceID
	INNER JOIN Datasets DS ON DSI.DatasetID = DS.DatasetID 
Where C.ConsumerID in (select ConsumerID from @TVP)
GROUP BY C.ConsumerID, C.ConsumerName, C.CurrentlyConsumer

Open in new window

Author

Commented:
Thank you very much Mr.Éric, you know what I need better than my self and It works 100%.

Last question:
usually, I got this message: How can I overcome it permanently:
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@CusResults".
Éric MoreauSenior .Net Consultant
Top Expert 2016
Commented:
Where/when do you see it?

@CusResults is a variable. The scope of the variable is the time of the query execution. So when you run a query that uses it you need to declare it within the same execution.

In other words, these lines must always be executed together:
/* Declare a variable that references the type. */
DECLARE @CusResults AS CusResults;

/* Add data to the table variable. */
INSERT INTO @CusResults (ConsumerID,ConsumerName, CurrentlyConsumer, NumberOfService, NumberOfDatasets)
SELECT top 10 C.ConsumerID
FROM Consumers C

/* Pass the table variable data to a stored procedure. */
EXEC SP_RecivedDataForTBV @CusResults;

Open in new window

Author

Commented:
Thanks a lot Eric.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial