Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Table Valued Parameter

Posted on 2014-12-24
4
Medium Priority
?
69 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

721 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