Link to home
Start Free TrialLog in
Avatar of Harreni
HarreniFlag for Saudi Arabia

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Harreni

ASKER

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

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Harreni

ASKER

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".
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Harreni

ASKER

Thanks a lot Eric.