Table -Valued Parameters

Question

Hi Folks

I am struggling to get a Table Value Parameter (TVP)  to work. 

This is the table containing the parameters

CREATE TABLE [dbo].[Odo_vs_GPS_Exceptions](
	[machine_id] [bigint] NULL,
	[Entry_Time_stamp] [datetime] NULL,
	[Exit_Time_stamp] [datetime] NULL,
	[Entry Timestamp + 20 Minutes] [datetime] NULL,
	[Entry Timestamp + 80 Minutes] [datetime] NULL,
	[Est_odo_journey] [float] NULL,
	[Deviation_from_Google_maps (47.5 km)] [float] NULL,
	[% Deviation from Google_maps] [float] NULL,
	[Minutes_transit] [int] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[Odo_vs_GPS_Exceptions] ([machine_id], [Entry_Time_stamp], [Exit_Time_stamp], [Entry Timestamp + 20 Minutes], [Entry Timestamp + 80 Minutes], [Est_odo_journey], [Deviation_from_Google_maps (47.5 km)], [% Deviation from Google_maps], [Minutes_transit]) VALUES (13079, CAST(0x0000A13B0133A2B0 AS DateTime), CAST(0x0000A13B014752EC AS DateTime), CAST(0x0000A13B013920F0 AS DateTime), CAST(0x0000A13B01499BB0 AS DateTime), 53.45223814086421, 5.95223814086421, -12.531027664977291, 71)
GO
INSERT [dbo].[Odo_vs_GPS_Exceptions] ([machine_id], [Entry_Time_stamp], [Exit_Time_stamp], [Entry Timestamp + 20 Minutes], [Entry Timestamp + 80 Minutes], [Est_odo_journey], [Deviation_from_Google_maps (47.5 km)], [% Deviation from Google_maps], [Minutes_transit]) VALUES (6641, CAST(0x0000A13C012969E4 AS DateTime), CAST(0x0000A13C01370964 AS DateTime), CAST(0x0000A13C012EE824 AS DateTime), CAST(0x0000A13C013F62E4 AS DateTime), 52.492025544837816, 4.9920255448378157, -10.509527462816459, 50)
GO
INSERT [dbo].[Odo_vs_GPS_Exceptions] ([machine_id], [Entry_Time_stamp], [Exit_Time_stamp], [Entry Timestamp + 20 Minutes], [Entry Timestamp + 80 Minutes], [Est_odo_journey], [Deviation_from_Google_maps (47.5 km)], [% Deviation from Google_maps], [Minutes_transit]) VALUES (6641, CAST(0x0000A13D012AA3F4 AS DateTime), CAST(0x0000A13D013793AC AS DateTime), CAST(0x0000A13D01302234 AS DateTime), CAST(0x0000A13D01409CF4 AS DateTime), 52.469037276954623, 4.9690372769546229, -10.461131109378163, 47)
GO

I need to pass three parameters 

1) Parameter 1 = [machine_id]
2) Parameter 2 = [Entry_Time_stamp]
3) Parameter 3 =  [Exit_Time_stamp]

My T-SQL statement is 

SELECT machine_id, offroad, event_timestamp, ebox_event_type_id
FROM central.ebox_event_201301
WHERE machine_id = Parameter 1
AND event_timestamp BETWEEN 'Parameter 2' AND 'Parameter 3'

Can someone help me out?

Regards

Steve 

Answer 1

Follow the example:

http://www.sqlusa.com/bestpractices2008/table-valued-parameter/

---

Kalman Toth SQL 2008 GRAND SLAM
New Book: SQL Server 2012 Programming

Answer 2

Hi Kalman

Thanks for the link.  I have searched for, and  found your and a  few other examples as well but have struggled to translate them. Hence the request.

Regards

Steve 

Answer 3

I am not sure I understand you. Do you want static parameters? Where TVP comes into play?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

Hi Naomi

Thanks for your reply. The  table [Odo_vs_GPS_Exceptions] gets truncated and repopulated with different values each night.  So each day it will contain different machine_Ids, and associated Entry and Exit time stamps etc 

Its these [Machine_ids],  [Entry_Time_stamp] and  [Exit_Time_stamp] values that I want to use in the SELECT statement above. From my reading, this is what I understood TVP to do.  

Hope this helps clarify the question- let me know if my questions requires further elaboration. 

Any help would be greatly appreciated. 

Regards

Steve

Answer 5

You don't need to use TVP. Looks like you just need the following

SELECT machine_id, offroad, event_timestamp, ebox_event_type_id
FROM central.ebox_event_201301 EE

WHERE exists (select 1 from 
ODO_vs_GPS_Exceptions O
where O.machine_id = EE.machine_id
AND EE.event_timestamp BETWEEN O.Entry_Time_Stamp AND O.Exit_Time_Stamp)

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 6

select a.machine_id,a.offroad,a.event_timestamp,a.ebox_event_type_id
from central.ebox_event_201301 a inner join Odo_vs_GPS_Exceptions b on a.machine_id=b.machine_id
	and a.event_timestamp BETWEEN b.Entry_Time_stamp AND b.Exit_Time_stamp

Answer 7

Hi Naomi

Thanks for the help. The additional hook is that I need to make a cross-server/database reference.

The first section of code (below)  that connects to a postgres database (via a T-SQL statement in a Sql Server Integration Services ODBC connection manager ) works fine

SELECT machine_id, offroad, event_timestamp, ebox_event_type_id
FROM central.ebox_event_201301 EE

I get an erro when I attempt to make a  Cross Server Reference to my local SQL server

The table [ODO_vs_GPS_Exceptions] sits on my local SQL 2012 machine. My attempt at the cross Server reference is

Localhost.Ebox_data_validation.Odo_vs_GPS_Exceptions

The full code is 

SELECT machine_id, offroad, event_timestamp, ebox_event_type_id
FROM central.ebox_event_201301 EE
WHERE exists (select 1 from 
Localhost.Ebox_data_validation.Odo_vs_GPS_Exceptions O
where O.machine_id = EE.machine_id
AND EE.event_timestamp BETWEEN O.Entry_Time_Stamp AND O.Exit_Time_Stamp)

Any ideas what might be going wrong?

Many thanks

Steve 

Answer 8

You need to set up a linked server in order to access that table or use openquery method to query this table. 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 9

So if I understand this correctly, your problem is how to query a Postgres database with input data taken from SQL Server? At some point SSIS is involved.

The answer that is on-topic for this forum is:

SELECT machine_id, offroad, event_timestamp, ebox_event_type_id
FROM POSTGRES...central.ebox_event_201301 EE
WHERE exists (select 1 from
dbo.Odo_vs_GPS_Exceptions O
where O.machine_id = EE.machine_id
AND EE.event_timestamp BETWEEN O.Entry_Time_Stamp AND O.Exit_Time_Stamp)

That is, this is a query that runs in SQL Server. With the caveat that I have no idea what to put into the dots in the Postgres reference.

If you want to run the query in Postgres, you will need to talk with the Postgres folks to do that. They may not even support remote data sources. And table-valued parameter is a proprietary feature in SQL Server, so that is a completely dead end.

---

Erland Sommarskog, SQL Server MVP, [e-mail]