We help IT Professionals succeed at work.
Get Started

Passing table-type values into Stored Procedure

fjkilken
fjkilken asked
on
181 Views
Last Modified: 2014-08-25
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

Comment
Watch Question
EE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE