• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

best way to proceduralize this?

i was going to use a TVP, but I am not writing the @deliverables to a table, i am merely using them to perform a join, and retrieve the dataset.

the given @delivarables are unknown.  maybe one, maybe twenty.... but it will always be no,name,qty


declare @deliverables table (num int,name char(10),qty decimal(18,8))
insert @deliverables values
(1,'ABC',50 ),(2,'DEF',2.52),(3,'GHI',20),(4,'JKL',20),(5,'MNO',20),(6,'PQRS',3);
select ug.tableA
from tableA ug join tableB s on s.idfield = ug.idfield
      join @deliverables d
      on ug.num = d.num
                     and ug.idfield = s.idfield
                     and ug.qty = d.qty


advice?
0
dbaSQL
Asked:
dbaSQL
  • 16
  • 8
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Yes.. That should work without any issues unless otherwise you load too many records / rows into @deliverables..

If you would be having many records in @deliverables, then request you to create a temp table, Index it out ( this is the difference) and then insert records into the temp table and join with it..

Hope this clarifies.
0
 
dbaSQLAuthor Commented:
you mean just do this with the TVP?  is that what you're saying?
0
 
dbaSQLAuthor Commented:
i'm just not sure how to define that such that the end-user can pass in the comma separated list of @deliverables.  i've ample other functions on passing csv's into procs.  surely i've got what i need.  hmmm....
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> you mean just do this with the TVP?  is that what you're saying?

Yes, I hope you are using SQL Server 2008..
SQL Server 2008 allows this kind of insert which would be easier in your scenario..

insert @deliverables values
(1,'ABC',50 ),(2,'DEF',2.52),(3,'GHI',20),(4,'JKL',20),(5,'MNO',20),(6,'PQRS',3);

>> i'm just not sure how to define that such that the end-user can pass in the comma separated list of @deliverables.

So, where / how do you get the inputs so that an easier way can be found out for this..
I thought you have that kind of input and hence suggested to use TVP which would be easier...
0
 
dbaSQLAuthor Commented:
Yes, I am using v2008.  I have several TVP insert procs in place elsewhere, but they are for actuall inserts.  This one is simply a working table, per se.  as i said, the @deliverables are unknown.  I need to code it such that end-user can perform that JOINed retrieval i posted up there, based on the given @deliverables.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> I need to code it such that end-user can perform that JOINed retrieval i posted up there, based on the given @deliverables.

Yes.. That sounds good..
And make sure if you have huge no. of records in the variable, then you need to pass it to a temp table and index it for better performance..
0
 
dbaSQLAuthor Commented:
I hear what you're saying, rrjegan, but I'm still not there on the construct.  
i've created my table type, and i'm trying to construct the procedure now, to allow joe-user to pass N list of @deliverables, upon which the JOIN will be performed
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> I have several TVP insert procs in place elsewhere

Ok..This statement made me think that you have the TVP in place..
Just use the same kind of construct to insert records into your temp table to make things simple..
Then join this with your other table and that should do right..

Kindly let me know where I should help you out if I haven't understood you better..
0
 
dbaSQLAuthor Commented:
give me a few to review the examples in BOL, and my other code.  i know i've got it all in front of me... I just need to get it toegher.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Ok.. Some links to help you better:

http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters

DECLARE @MyTable MyType

INSERT INTO @MyTable(col1,col2,col3)
VALUES (1,'abc','1/1/2000'),
      (2,'def','1/1/2001'),
      (3,'ghi','1/1/2002'),
      (4,'jkl','1/1/2003'),
      (5,'mno','1/1/2004')
      
SELECT * FROM @MyTable

Check "Choosing Between Temporary Tables and Table Variables" in the link below:

http://odetocode.com/articles/365.aspx

And hope you are aware of how to encapsulate these logic into your procedure..
0
 
dbaSQLAuthor Commented:
yep.  i have all of those.  but again, i have only used the TVP's for insertions.  this is not an insert.  i just want to reference @deliverables to retrieve data from a JOIN.  possibly a TVP is not the way to approach this.  still working....
0
 
dbaSQLAuthor Commented:
basically, forget the tvp. what is the ideal means of passing this into the proc:

insert @deliverables values
(1,'ABC',50 ),(2,'DEF',2.52),(3,'GHI',20),(4,'JKL',20),(5,'MNO',20),(6,'PQRS',3);
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> this is not an insert.  i just want to reference @deliverables to retrieve data from a JOIN.  possibly a TVP is not the way to approach this.

To make my statements clear:

1. In addition to the INSERT via Table Valued Parameters, you can do JOIN's too as example provided in the link

SELECT ProductName, Revenue
FROM Products P
INNER JOIN @ProductTotals PT ON P.ProductID = PT.ProductID

2. You can get better performance with respect to Temp table in case of lesser records because temp table needs to create a table ( DDL activity) and then INSERT records into it which would be costlier compared to TVP's
3. TVP's would be costlier when you have huge no. of records in it.

Have used it on my own without any issues for nearly 100 records using TVP without any issues for JOIN's.
Kindly let me know if I have to clarify it in more detail..
0
 
dbaSQLAuthor Commented:
yes, i understand all of the above.  i simply do not know yet how best to define the procedure.  that's all i'm struggling with
0
 
dbaSQLAuthor Commented:
SELECT ProductName, Revenue
FROM Products P
INNER JOIN @ProductTotals PT ON P.ProductID = PT.ProductID


great.  if the user is putting in one value.  i am trying to code it for N number of @deliverables, where this is a @deliverable:  (1,'ABC',50 )

and they could be passing it in like this:
(1,'ABC',50 ),(2,'DEF',2.52),(3,'GHI',20),(4,'JKL',20),(5,'MNO',20),(6,'PQRS',3);
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
So trying to explain with your code below:

-- TVP Declaration
declare @deliverables table (num int,name char(10),qty decimal(18,8))

-- Inserting values into your TVP
insert @deliverables values
(1,'ABC',50 ),(2,'DEF',2.52),(3,'GHI',20),(4,'JKL',20),(5,'MNO',20),(6,'PQRS',3);

-- JOIN to your TVP
select ug.tableA
from tableA ug join tableB s on s.idfield = ug.idfield
      join @deliverables d
      on ug.num = d.num
                     and ug.idfield = s.idfield
                     and ug.qty = d.qty

And I believe the place where you are struggling is the INSERT statement and if I am correct you are forming those comma separated values using some other function..
Just pass it out or post it so that I can provide the sample procedure..
0
 
dbaSQLAuthor Commented:
again, i understand all of that.  BUT, the user isn't going to pass this in on every execution:

(1,'ABC',50 ),(2,'DEF',2.52),(3,'GHI',20),(4,'JKL',20),(5,'MNO',20),(6,'PQRS',3);

i am simply trying to construct the proc such that the @deliverables are passable

almost there.  i think.  will know shortly
0
 
dbaSQLAuthor Commented:

>>And I believe the place where you are struggling is the INSERT statement and if I am correct you are

no.  i am struggling with exactly how to define @deliverables, such that the end-user can pass X number of them into the procedure

this data, of course, would then be used to perform the JOINed retrieval
0
 
dbaSQLAuthor Commented:
ok.  i've got one of my udf's --- bascially,

select * from udf ('1,ABC,50|2,DEF,2.52|3,GHI,20|4,JKL,20|5,MNOP,20|6,QRS,3','|')

equates to this:
1,ABC,50
2,DEF,2.52
3,GHI,20
4,JKL,20
5,MNOP,20
6,QRS,3

how can i use this as the input for @deliverables?

0
 
dbaSQLAuthor Commented:
any suggestions?
0
 
dbaSQLAuthor Commented:
forget all previous posts.  i just am interested in  the best way to using my udf output on the given @string, as input parameters into the procedure

1,ABC,50
2,DEF,2.52
3,GHI,20
4,JKL,20
5,MNOP,20
6,QRS,3


that would be my @deliverables
0
 
dbaSQLAuthor Commented:
i appreciate all of the input, rrjegan17.  i am going to award and close.  the problem is becoming quite pressing, and i think it's not really about the TVP at this point.  my bad for the premature post.
thank you very much for looking this way.
0
 
dbaSQLAuthor Commented:
definitely viable, and appreciated.  but my construct has changed.  thank you for the input
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Welcome..
And glad you resolved it out..
( Bit out for my dinner)..

Let me know in case of any guidance on this later on..
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 16
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now