Passing table-type values into Stored Procedure

Hi
I have a SP which expects as it's input, a table-type set of values, and outputs three columns of info.

My issue is finding the simplest way to pass the table-type set of values to this SP.
The code below provides me with the expect output from the SP:

DECLARE @InputList VARCHAR(MAX)
SET @InputList = '30,4,6,8,50,13,1,10'
DECLARE @SiteTable AS [dbo].[ty_ste]
INSERT INTO @SiteTable
SELECT item AS ste_id FROM fn_rpt_str_to_tbl_int (@InputList, 500 )
SELECT * FROM @SiteTable
EXEC rpt_sp_TEST_SP @pm_ty_ste_id = @SiteTable

Open in new window


The Function "fn_rpt_str_to_tbl_int" converts the comma-delimited input string to a table-like series of values.
However, if I were to try something like the following:

EXEC rpt_sp_TEST_SP  (SELECT item AS ste_id FROM fn_rpt_str_to_tbl_int ('30,4,6,8,50,13,1,10', 500 ))

Open in new window

..this does not work.

Ideally, I would like to execute the SP in a format such as this:

EXEC rpt_sp_TEST_SP  <input variable here>

..but am unsure how to do this - should I build another SP to assist with this? or should I somehow modify the function to output the values in a different way?

For reference, below is the Function and the SP:

CREATE FUNCTION [dbo].[fn_rpt_str_to_tbl_int] (@strList varchar(max), @maxItems int = null)  
RETURNS   
@tableOfItems table(item int)  
AS  
BEGIN  
  DECLARE @strElement varchar(max), @position int, @noInserted int  
  set @strList = @strList + ','  
  set @position = charindex(',', @strList)  
  set @noInserted = 0
  WHILE ((isnull(@position, 0) > 0) and (@noInserted < @maxItems or @maxItems is null))  
  BEGIN  
     SET @strElement = LEFT(@strList, @position - 1)  
     INSERT INTO @tableOfItems values ( RTRIM(LTRIM(@strElement)))   
     SET @strList = RIGHT(@strList, DATALENGTH(@strList) - @position)  
     set @position = charindex(',', @strList)  
     set @noInserted = @noInserted + 1
  END  
  IF NOT EXISTS (SELECT * FROM @tableOfItems)
              INSERT @tableOfItems VALUES (null)
              
RETURN  
END

CREATE PROCEDURE [dbo].[rpt_sp_TEST_SP]
--(
	@pm_ty_ste_id AS dbo.ty_ste READONLY 
--)
AS
BEGIN

		DECLARE @dtm DATETIME = CURRENT_TIMESTAMP
		SELECT iste.ste_nm, iste.ste_ty_id, @dtm as DTM
		FROM dbo.ste iste 
		INNER JOIN @pm_ty_ste_id tste
			ON iste.ste_id = tste.ste_id
	
END

Open in new window

fjkilkenAsked:
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.

PortletPaulfreelancerCommented:
why not pass the comma separated string of values and break it apart inside the SP?
0
fjkilkenAuthor Commented:
because I need to re-use the StringToTable Function many times
0
PortletPaulfreelancerCommented:
so, use the function many times, including in the SP ...
0
fjkilkenAuthor Commented:
right - but HOW to use the function within the SP is my issue
0
PortletPaulfreelancerCommented:
you mean something like this?

-- MS SQL Server
CREATE PROCEDURE get_employees_from_ssn_list @ssn_list VARCHAR(2000) 
AS SELECT * 
     FROM tbl_employees 
    WHERE ssn IN (SELECT VALUE FROM dbo.ParmsToList(@ssn_list,',') );

Open in new window


Do have a look at this article by angeliii (where that snippet is from)
Delimited list as parameter: what are the options?

also Jeff Moden:  Tally OH! An Improved SQL 8K “CSV Splitter” Function
0

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
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
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.