Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
petebob796
Starting Member
35 Posts |
 Posted - 2008-04-05 : 11:30:19
|
| Before I start a small project I am interested in the best way to do it. I work for a college doing management information and generally finding problems with our data. A regular thing I end up with is a set of student ID's which I need to lookup. Through the front end this takes a while as I have to look them up individually and I often need to compare ID's.What I have thought about making is a system where I can select a set of ID's and search for all of them. I will probably make this through C# pasting the set of id's into a datagridview and providing the results in another one. The problem I have is I don't know how to send a set of ID's (so I would probably be using where IN (SET OF ID's). I read briefly a while back about passing a type table but am unfamiliar with how to use it. This is sql 2000 server. |
|
|
petebob796
Starting Member
35 Posts |
Posted - 2008-04-05 : 11:31:56
|
| Forgot to put I know how to do it with dynamic sql but would prefer to wrap it into a procedure. |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-04-05 : 12:22:36
|
| A typical way is to pass a delimited string of IDs in a varchar parameter then within your SP you can JOIN to a table valued user defined parsing function. Search for "parsing routines" or CSV and you will see a lot examples.select...from dbo.fn_Parse(@StringOfIDs) as IDsJOIN <yourOtherTable> ot on IDs.OutPutInt = ot.StudentIDBe One with the OptimizerTG |
|
|
|
petebob796
Starting Member
35 Posts |
Posted - 2008-04-05 : 19:37:31
|
| Thanks for the help I went with an xml approach I found in the meantime. So for anyone reading this it passes in a type text which doesn't have the varchar size limit (it has some limit don't know what) turns it into a table for joining in memory. I think the below shows how it works make sure to exec sp_xml_removedocument at the end to avoid a memory leak.CREATE PROCEDURE get_product_names_xml @ids text AS DECLARE @idoc int, @err int EXEC @err = sp_xml_preparedocument @idoc OUTPUT, @ids SELECT @err = @@error + coalesce(@err, 4711) IF @err <> 0 RETURN @err SELECT Marks.student_id FROM dbo.[Student Marks] as Marks JOIN OPENXML(@idoc, '/Root/ID', 1) WITH (student_id char(12)) AS ID_LIST ON Marks.student_id = ID_LIST.student_id EXEC sp_xml_removedocument @idoc GO EXEC get_product_names_xml '<Root><ID student_id="ABB06034245"/><ID student_id="ADA03016178"/> <ID student_id="ADA04025911"/><ID student_id="AIS02014914"/></Root>' |
|
|
|
|
|
|
stored procedure IN set of values (or alternative)