Passing more than one parameter

Question

Passing more than one parameter

eyejay

Mr or Mrs. 500

Points: 542
More actions
December 8, 2008 at 2:38 pm

#203885

I work in a hospital and during this last year we have been collecting patient data in a SQL server 2005 db. As I have an interest in db's and some reasonable experience with MS Access I have become involved with running this db. I am desperately trying to get upto speed with T-SQL. I am now getting our doctors asking me to get data for them out of the database. I can do simple stuff but one question that comes up frequently and I don't have a clue where to start is this: "If I give you a list of patient ID numbers can you give me data values x, y, z ....." At present all I can do is to do this one patient at a time which is fine if it's only a small number of patients but when the request is for a few hundred patients I'm lost.
Could some kind soul please take pity on my ignorance and point me in the right direction of either some articles, downloadable examples or just a general starting point so I can go and read a book chapter.
Many thanks, Ian.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

When given a list of ID's that you want to search for, I would create a table to hold the ID's and use that in my query. Using a temporary table:

SELECT ...

FROM dbo.Patients p

JOIN #patient_ids p2 ON p2.ID = p.ID

You can easily load a temporary table using cut & paste, or import/export, or by using openquery. If you need additional help, post here and somebody will be able to help you out.

Please read the article:

Best Practices: http://www.sqlservercentral.com/articles/Best+Practices/61537/

This article will show you how to post your questions in a way that will get you better and faster results.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

This is one of the "failings" in SQL Server. If you want a report that provides data based on a list of items you have a couple of options. You can pass them in as a delimited list which you then will need to parse or you can pass in an XML document and convert that to a table.

Here are a couple of links:

http://www.sqlservercentral.com/articles/T-SQL/63003/

http://www.sommarskog.se/arrays-in-sql.html

And here is a link to the results of a google search for "Arrays in SQL Server":

http://www.google.com/search?q=Arrays+in+SQL+Server&sourceid=navclient-ff&ie=UTF-8&rlz=1B5GGGL_enUS302US302

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

eyejay Mr or Mrs. 500 Points: 542 More actions · Answer 3

Thanks Jeffrey and Jack, I shall follow the leads you gave me.

Ian.