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!
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?
Thanks a lot.
Harreni
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
);
Thanks a lot.
Harreni
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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
FROM Consumers C
INNER JOIN Services S ON C.ConsumerID = S.ConsumerID
INNER JOIN Datasets_Services_Intersec
INNER JOIN Datasets DS ON DSI.DatasetID = DS.DatasetID
where EXISTS(SELECT 1 FROM dbo.DelimitedSplit8K(@Cons
GROUP BY C.ConsumerID, C.ConsumerName, C.CurrentlyConsumer
);
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
FROM Consumers C
INNER JOIN Services S ON C.ConsumerID = S.ConsumerID
INNER JOIN Datasets_Services_Intersec
INNER JOIN Datasets DS ON DSI.DatasetID = DS.DatasetID
where EXISTS(SELECT 1 FROM dbo.DelimitedSplit8K(@Cons
GROUP BY C.ConsumerID, C.ConsumerName, C.CurrentlyConsumer
);
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:
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
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:
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
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".
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".
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. */
@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;
Premium Content
You need an Expert Office subscription to comment.Start Free Trial