[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 159
  • Last Modified:

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

0
fjkilken
Asked:
fjkilken
  • 3
  • 2
1 Solution
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now