Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Table Valued Parameter

Posted on 2014-12-24
4
Medium Priority
?
71 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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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 70

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Integration Management Part 2
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

782 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