Solved

Table Valued Parameter

Posted on 2014-12-24
4
62 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:
ScottPletcher 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:ScottPletcher
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now