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

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
HarreniAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
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
);
HarreniAuthor 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

Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Download eBook now!
Éric MoreauSenior .Net ConsultantCommented:
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

HarreniAuthor 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 ConsultantCommented:
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

HarreniAuthor Commented:
Thanks a lot Eric.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.