Solved

Table Valued Parameter

Posted on 2014-12-24
4
66 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

820 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