Ok-from what I have been reading about TVP its a way to send client data to server in one hit-but I want server data to my client
so how does this happen ?
1.the TVP gets emptied every time - from my application
2.the tvp gets populated with my parameter values - from my application
3.the stored proc creates a query using joins from actual data tables and my user defined table
4.the stored proc gets called from my app and displays any relevant data
am I even close ?
100% on point in theory then comes testing
Tvp gets "emptied" end of proc run
You dont need to destroy it in app.
Everytime you call search from app you just rehydrate it or refill it
this sounds exciting if I can get it going
I would imagine no sql injection is possible and data return across the network should be as fast as you
can possible get-given the parameters of the network set up
testing will no doubt be frustrating,but see how I go
edit
this way may make it possible to have dynamic indexes ?
Dynamic indexes? Curve ball.
About sql injection. Is your app internal or public facing?
is internal,not on a web site
runs on your typical on site server in your office storeroom ,with 10-15 users
indexes-was just thinking aloud
Is it a windows environment with user names and passwords?
Do they login using uname and password?
Jsp is just an alias for the tvp 
Let's say you are joining to a table with long name
Join long_table_name ltn
That way you dont have to repeat that long name when referring to a column in that table
Select long_table_name.firstname
From long_table_name
Becomes
Select ltn.firstname
From long_table_name ltn
yep-windows server 2018, all windows10 pc,office 365,all users log in,all have passwords
admin is an independent IT guy
it would seem I would need to use 2 stored proc
one to append the parameters into the TVP
and another to execute the resultant query
as far as I can tell,there is no way to add data directly to a TVP using ADO
what happens if 2 users are using the same search form-is there a write conflict trying to add parameters to the TVP ? or should I set the TVP up to accept UserId
DataTable myDataTable = new DataTable("MyDataType");
myDataTable.Columns.Add("Name", typeof(string));
myDataTable.Columns.Add("Id", typeof(Int32));
myDataTable.Rows.Add("XYZ", 1);
myDataTable.Rows.Add("ABC", 2);
No need for passing UserId. In modern web development each users interaction with the site is it's own session
This is beyond SQL server
Read up on that concept
I am using ADO from within access 2010,seems the only way I can pass anything to sql is by usong a pass through query
VBA doesn't expose any thing else as far as I can tell,but I will read up whatever I can find
before I get to deep into this ,will using this method allow for using "Like" as opposed to = when searching data
For the like comparison you can do it via
Select
@JobNumber = JobNumber,
@Custonername = Customername,
From tvp
Then in main query
From xyz
Where JobNumber like '%' + @JobNumber + '%'
ok-so I have created another stored proc to try and append data into the TVP,as I cannot find any other way to do this from an access database front end
I just used 2 parameters
ALTER PROCEDURE [dbo].[spSelectData]
@Job_Number VARCHAR(30),
@Customer_Name VARCHAR(30)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @firstStep AS dbo.jobscheduletype --my user defined table
Insert INTO @firstStep(JobNumber,Customername) -- use these columns
VALUES (@Job_Number,@Customer_Name) -- use these variable values
EXEC spSelectData;
END
when I execute this I input the 2 parameter values and get this error
Msg 201, Level 16, State 4, Procedure spSelectData, Line 0 [Batch Start Line 2]
Procedure or function 'spSelectData' expects parameter '@Job_Number', which was not supplied.
(1 row affected)
obviously I am missing something,and apoligies for being slow on the uptake
You defined your proc to take
2 parameters but when you are calling
That same proc yo are not providing those
Required parameters
good Morning yosiasz
when I execute the proc in SSMS a window pops up to provide the values for those 2 parameters
so I gave those 2 pars a text value of "test" then pressed execute
yosiasz - what part of the world are you in ?
I am not on planet earth. I am in Tatooine
ok so I tried fixed values
ALTER PROCEDURE [dbo].[spSelectData]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @firstStep AS dbo.jobscheduletype
Insert INTO @firstStep(JobNumber,Customername)
VALUES ('trst job number','test customer')
EXEC spSelectData;
END
now I get this error
Msg 217, Level 16, State 1, Procedure spSelectData, Line 14 [Batch Start Line 2]
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
I don't seem to be getting any closer
You are calling the proc from within the proc itself