Solved

Table Valued Parameter

Posted on 2014-12-24
4
65 Views
Last Modified: 2015-02-10
Hello,

I have table valued parameter query setup like this:

CREATE TYPE ListTableStock AS TABLE (item_number VARCHAR(10) NOT NULL, item_desc VARCHAR(50), stock INT NOT NULL)

Open in new window


I then pass this into this stored procedure

CREATE PROCEDURE [dbo].[get_item_id]
(
@item_number ListTableStock READONLY,
@item_desc ListTableStock READONLY,
@stock ListTableStock READONLY
)
AS
BEGIN

SELECT
items.item_id
FROM
items
WHERE
items.item_number + '-' + items.ver IN (SELECT item_number FROM @item_number )
END

Open in new window


Overall its runs like this:

DECLARE @ListTableStock AS ListTableStock;

INSERT INTO @ListTableStock(item_number, item_desc, stock)
 VALUES ('297824-1.0', 'desc1, 55),
 ('308227-1.0', 'desc2', 521);
 
 EXEC get_item_id @ListTableStock;
 GO

Open in new window


The results I get are this:

item_id
12153
13376

Open in new window


What I’d like to happen is for the stored procedure to pass back the stock value, so the end results would look something like this:

item_id	Stock
12153		55
13376		521

Open in new window


Any ideas how I can achieve this
0
Comment
Question by:andyw27
  • 2
  • 2
4 Comments
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40516344
Something doesn't look right... in the SP's parameters, you have all three parameters as type ListTableStock, which is your custom TYPE which itself contains these three separate fields.

At any rate, assuming that was just a typo and you are actually only passing in the one parameter of type ListTableStock, the only thing I can think of would be to join your items table to the parameter table (which I'm not sure you can do).

So, inside of the stored procedure, in the select portion:

CREATE PROCEDURE [dbo].[get_item_id]
    @ItemsList ListTableStock READONLY,
AS

BEGIN

SELECT
items.item_id, il.stock
FROM items INNER JOIN @ItemsList il on il.item_number = items.item_number + '-' + items.ver 

END

Open in new window


I will admit I'm a little rusty in this area, so I'm not guaranteeing that will work.  Something about the entire operation seems a little off to me.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40516701
You need to add "ver" to the table type rather than doing a concatenation on table columns in the WHERE clause.  Such mods to column values will prevent SQL from doing any index seeks, which could result in vastly worse response time from the queries.

Here's a running sample with an adjusted setup:


CREATE TABLE items (
    item_id int NOT NULL,
    item_number varchar(10) NOT NULL,
    ver varchar(10) NOT NULL
    )
INSERT INTO items VALUES( 1, '0001', '1' )
GO    
CREATE TYPE ListTableStock AS TABLE (item_number varchar(10) NOT NULL, ver varchar(10) NOT NULL, item_desc varchar(50), stock int NOT NULL)
GO


CREATE PROCEDURE [dbo].[get_item_id]
(
@table_stock ListTableStock READONLY
)
AS
BEGIN

SELECT
i.item_id,
ts.stock
FROM
items i
INNER JOIN
@table_stock ts ON
ts.item_number = i.item_number AND
ts.ver = i.ver
END

GO

DECLARE @table_stock ListTableStock
INSERT INTO @table_stock ( item_number, ver, stock ) VALUES( '0001', '1', 58 )

EXEC get_item_id @table_stock
0
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40517488
As Scott suggested, the concat will have an impact on performance.  If you were doing it that way due to the input coming from some other component or code that passes it in this way, you'd be better off handling that first by splitting the value into two fields before passing it in to your procedure.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40518587
You can use computed columns to split out the item parts if you need to, something like this:

CREATE TYPE ListTableStock AS TABLE (
    item_number_concat varchar(10) NOT NULL, --some new name not currently used in queries
    item_desc varchar(50),
    stock int NOT NULL,
    item_number AS CAST( LEFT(item_number_concat, CHARINDEX('-', item_number_concat + '-') - 1) AS varchar(10) ),  --split out the item_number
    ver AS CAST( SUBSTRING(item_number_concat,  CHARINDEX('-', item_number_concat + '-') + 1, 10) AS varchar(10) ) --split out the ver
    )
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question