0

How to pass TEXT(the list of id's as a string as I have long list of id's) to sybase ase stored procedure call?

If I want to pass TEXT to stored procedure call then how can I use same TEXT in where IN query of sybase?

CREATE PROCEDURE Collect_List( @ids_list TEXT )

Rahul Mankar
  • 910
  • 9
  • 17
  • I'm pretty sure you don't mean to use text but rather varchar() because text columns are by definition BLOBs and somewhat different. If you pass it in as a string of already processed values i.e. @ids_list = "('A','B',C')" then you can easily build the query. If you want to parse the list that's a different matter entirely. – Rich Campbell May 02 '22 at 13:00
  • Thanks @RichCampbell !! Yes actually that's the target of this issue. I can't pass VARCHAR in stored procedure calling as I am having large collection of INT id's to send and finally to use those list of INT id's in the Sybase WHERE IN query like - where id in (10,15,20,25,30,35,40,45,50) – Rahul Mankar May 02 '22 at 17:28
  • You can pass in literally thousands of chars into a varchar column so you still shouldn't need a text column unless you have thousands of values. If you do have thousands, instead consider whether that's actually the best idea to begin with, given that using an in clause will at best usually do an index scan/lookup per value (assume that column is indexed and it definitely should be or it'll tablescan), so passing large amounts of values could be inefficient. Instead inserting the ID values as rows into a temp table and joining to it is liable to be more optimal (check the query plan). – Rich Campbell May 04 '22 at 06:30

1 Answers1

0

Looks like this is not an efficient way to pass dynamic IDs in the query. Why don't you use temp table to do it?

SeanH
  • 538
  • 2
  • 8
  • sorry but I am new in stored procedures. I have to send list of large id's from nodejs to sybase stored procedure. How can I store this list before passing it to sybase stored procedure? – Rahul Mankar Jun 06 '22 at 17:11
  • Can you paste the content of the Sybase stored procedure? Just curious it needs a big list of IDs as the parameter. – SeanH Jun 06 '22 at 20:58
  • It's simple select query which will use list of ids in where in condition like select * from employee where empid in (1,2,3,4...). The list is too large in condition. – Rahul Mankar Jun 07 '22 at 14:12