Stored Procedure with array of values

I have the query below in a stored procedure in MS SQL 2005 server.  My 
problem is that I would like to be able to pass in multiple questionID values. 
  So I would like to replace
    A.QuestionID = @QuestionNumber
with a method where I can pass in a list of question numbers.  For example 
instead of passing in 286 I would like to pass in 286, 289, 412, and 513.  The 
number of values passed in would vary from 1 to 15 or 20.

I imagine the line
    A.QuestionID = @QuestionNumber
would be edited to something along the lines of
     A.QuestionID IN ( @QuestionNumber )

SELECT Count(T.TestID) as TheCount
	,IsNull(A.Answer,'Not Indicated') as Response
FROM tblTests As T
	INNER JOIN tblSessions as S
		ON T.SessionID = S.SessionID
	INNER JOIN Interventions as I
		ON I.InterventionID = S.InterventionID
	INNER JOIN tblAnswerResponses as R
		ON T.TestID = R.TestID
	INNER JOIN tblPossibleAnswers as A
		ON R.AnswerID = A.AnswerID
	INNER JOIN tblQuestionList as Q
		ON R.QuestionID = Q.QuestionID
	INNER JOIN Interventions as I
		ON S.InterventionID = I.InterventionID
WHERE T.TestType In ('PreTest')
AND S.InterventionType ='Group'
AND S.SessionDate Between @PeriodStart and @PeriodEnd
AND I.ProjectCode = @ProjCode
--  MODIFY the following
AND A.QuestionID = @QuestionNumber

GROUP BY A.Answer

Any help is appreciated.

-- 

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
0
John
2/19/2010 7:11:22 PM
sqlserver.programming 1873 articles. 0 followers. Follow

4 Replies
607 Views

Similar Articles

[PageSpeed] 51

John Spencer wrote:
> I have the query below in a stored procedure in MS SQL 2005 server.
> My problem is that I would like to be able to pass in multiple
>   questionID values. So I would like to replace
>     A.QuestionID = @QuestionNumber

Hey John, check Erland's article on using arrays in procedures:
http://www.sommarskog.se/arrays-in-sql-2000.html
-- 
HTH,
Bob Barrows


0
Bob
2/19/2010 7:16:26 PM
John Spencer wrote:
> I have the query below in a stored procedure in MS SQL 2005 server.
> My problem is that I would like to be able to pass in multiple
>   questionID values. So I would like to replace
>     A.QuestionID = @QuestionNumber

oops, I meant to give you this link:
http://www.sommarskog.se/arrays-in-sql-2005.html
-- 
HTH,
Bob Barrows


0
Bob
2/19/2010 7:17:20 PM
Erland's article will show you plenty of methods:
http://www.sommarskog.se/arrays-in-sql.html

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
2/19/2010 7:18:06 PM
Bob Barrows and Plamen Ratchev,

Thanks to both of you.  The first solution in Sommarskog's article works very 
well for me.  I intend to read the rest of the article to increase my 
knowledge, but this will solve a nagging problem and allow me to create some 
more generalized stored procedures.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Plamen Ratchev wrote:
> Erland's article will show you plenty of methods:
> http://www.sommarskog.se/arrays-in-sql.html
> 
0
John
2/19/2010 9:49:32 PM
Reply:

Similar Artilces:

How to open RecordSet with Stored Procedures (ODBC, VC++)
Hi I'm using ODBC and VC++. My stored procedure has parameters like @pstr VarChar(100), @pVal1 numeric(9), @pVal2 numeric(9) and it returns a recordset. In my code CString strSP; strSP.Format("{call MySp('%s','%d','%d')}", str1, (long)Val1, (long)Val2); CMyRecorSet rs(m_db); try { rs.Open(CRecordset::snapshot,strSP); } catch(CDBException * pDB) { } I'm getting exception. Cannot covert datatype varchar to numeric. Why is this happening? Do i need to call SQLBindParameters? If yes, how to call it. Any sample code? Thanks in advance. Remove the apos...

Recovering Private information store
I had a problem with the Private information store and restored an earlier version from a hard drive backup. I did all the procedures necessary to restore and enable the information store and all my Exchange 5.5 services are started without any error. The problem however is that I am not getting any mail to my users. I run a POP3 connector and I use the viewer to check the POP3 activities. I see the the connection to the mail server and the acknowledgements that it is retrieving E-Mail messages. I cannot figure out where the messages are going on aour server and why are they not bei...

Overlapping of values in different tables.
I am wondering if there is a way to create a pivot table - or some other way - that will show the relationships of rows between tables. I have attached data from potentially 4 different tables and the business is wanting to see the number of times that a paticular ID shows up in different table join combinations. For example, we want to know the count of unique IDs will show in the NPS, AAC and ECM tables. Is it possible to create a pivot table that will dynamically adjust based on a user choosing the column combinations that they want to see without having to create a separate pivot...

find location max value in column
I need to find the cell location of the max value in a column. If paste the following [from =(cell onwards] into a cell I get the righ result. Having a hgard time getting the right VBA code to make thi work in an unattended macro. Probably some bonehead mistake on my part but I cannot find it. Suggestions? GOAL is to make this formula work. Range("z2").Formula "=(CELL(e1,OFFSET(e1,MATCH(MAX(e1:e100),e1:100,0)-1,0)))" TIA, Ro -- rroac ----------------------------------------------------------------------- rroach's Profile: http://www.excelforum.com/member.php?act...

Array formula #2
Hi, I have a list of numbers and I want the average not counting the 0.00 in the range. a 12 15 19 0 101 150 0 52 67 122 Now my formula is {=average(if($a$1:$a$10<>0,$a$1:$a$10,0))} This will sum to 538 and average to 53.8 The result I am looking for is sum 538/8 or 67.25 Where is my error?? TIA ray Ray, Leave off that last ",0": array enter (using Ctrl-Shift-Enter) =AVERAGE(IF(A1:A10<>0,A1:A10)) HTH, Bernie MS Excel MVP "Ray" <anonymous@discussions.microsoft.com> wrote in message news:1757401c448bf$32508e30$a101280a@phx.gbl... > Hi, > I have ...

Retrieving default values specified from XML Schema file
Hi All, I have an VS.NET 2003 app which will be processing XML files, some of which will have empty elements (for example, <Item />). I have an XSD file which should supply default values, but I am not able to read these plugged in value using an XMLDocument object. The validation is working fine--it's just not providing any default values. Has anybody tried this? Am I must be missing something? This is one of those that "should be simple..." Here's a simplifed version of the code and XML files: XmlDocument xmldoc = new XmlDocument(); XmlSchemaCollection my...

controling excel from vba access and using sub procedures
Hi All, I'm having trouble getting my code to work using sub procedures in my vba code, this only happens when i am interacting with excel. I've tried to copy all the dims and defs from the calling procedure but to no avail at this time. sample code below. Dim strPath As String Dim rst As DAO.Recordset Dim Excel_Application As Excel.Application Dim Excel_Workbook As Excel.Workbook Dim Current_Worksheet As Excel.Worksheet Dim Data_Range Dim Worksheet_Name Dim db As Database Dim rs As Recordset D_now = Format(Date, "dd-mm-yy") ' Formated to use as part of fi...

multi-value field
I'm still learning Access, so I must apologize in advance for my question. I have four tables, tblBrochure, tblStaff, tblOrganization, tblResourceDistribution. tblBrochure has two fields -BrochureName -Active (y/n) tblStaff has two fields -StaffName -Active (y/n) tblOrganization has two fields -OrgName -textbox (0 and 1, explained below) tblResourceDistribution has 5 fields -DistribDate -StaffName (lookup from tblStaff ->Staffname -Organization (lookup from tblOrganization ->OrgName) ...

Lookup value selection
Is there any way to select lookup value by pressing "Enter" key on keyboard and avoid mouse click? -- romeo!! Please clarify. I could use the up and down arrow to select the values and then press "Enter" key to select the lookup value - works for me in v4.0. Frank Lee, Microsoft Dynamics CRM MVP http://www.workopia.com/Links.htm http://microsoft-crm.spaces.live.com "romeo" wrote: > Is there any way to select lookup value by pressing "Enter" key on keyboard > and avoid mouse click? > > -- > romeo!! ...

Rank Query With Groups (Array?)
Hello, I have a table of data with grades which are grouped by study. I need to rank the grades relative to all the other grades in the same study. Below is an example the study and grade fields with the desired outcome for the rank. Study Grade Rank 11111 89 1 11111 75 2 11111 65 3 22222 99 1 22222 87 2 Hopefully this can be done with simple SQL in a query and not with code but I am open to anything that works. Many thanks in advance for your time and expertise! Try this -- SELECT Study, Grade, (SELECT Count(*) FROM YourTable AS [XX] WHERE You...

Vlookup
Dear all, For vlookup, it will return the value of the destination cell. Is it possible to return the row no. instead of value of the destination cell?? Your help is highly appreciated. Thanks & regards, Automne Look at the MATCH function in Help. On Sat, 5 Mar 2005 01:41:39 +0800, "automne" <hersbt_no_spam@yahoo.com.hk> wrote: >Dear all, > >For vlookup, it will return the value of the destination cell. Is it >possible to return the row no. instead of value of the destination cell?? > >Your help is highly appreciated. > >Thanks & regards,...

How to ignore records with a duplicate ID based on a value
I hae searched the forum tono avail, so I'll ask for help. I have a table [Scope Event Table] which records events with status changes. The table uses an auto numbered primary key (not shown in the example below). It has data like this: DR_ID Event_Date Reason 12556 01/03/2008 Added 12556 01/24/2008 Closed 12874 01/05/2008 Added 14128 02/09/2008 Added I am a novice at SQL. I am trying to build a query that would pull only the DR_ID’s where the most recent Reason = “Added”. So I would want results to look like this: DR_ID Event_Date Reason 12874 01/05/2008 Added 14128 02/09/2008...

Set default values
I've set up a template where I have headers and cell validations. I want to be able to have some of the fileds in the row autopopulate once the first cell in the row is entered. Do you know of a way that I can code this using VBA. For example, A3 is populated with a value, I then want G3 to be updated with a "1". -- Message posted via http://www.officekb.com In G3: =IF(A3="","",1) -- Gary''s Student - gsnu2007k In G3 use the formula =IF(A3<>"",1,"") The "" is a pair of double-quotes with no space between be...

Sending value to a cell
Hi there, I want to dump a computed value into a cell that isn't the one the calculation was done in. Can it be done from the original cell? Hi no, not with a formula -- Regards Frank Kabel Frankfurt, Germany "CM32134" <CM32134@discussions.microsoft.com> schrieb im Newsbeitrag news:0768C79B-586C-42CB-A7AE-1A817C4CE5F5@microsoft.com... > Hi there, > I want to dump a computed value into a cell that isn't the one the > calculation was done in. Can it be done from the original cell? A worksheet formula returns a value and can do nothing else. Think...

I am making a charts with weird X-values for my Ecology cl\
i am making a chart with weird X-values. Instead of single, whole numbers, I need each value to be as follows: 1-2, 3-5, 6-10, 11-15, etc. Please help me!! My chart keeps going to whole numbers instead and its been driving me crazy for days! What chart type are you using? If you want your X-axis to be category labels, rather than actual numbers, you may find that a line chart (or one of the other chart types) is more suitable than an XY (scatter) chart, if the latter is what you are currently trying. -- David Biddulph "Holly" <Holly@discussions.microsoft.com> wro...

Suggestions On Storing External Data
Hello, I was wondering what the best way to store, Word, Excel, pdf,and dwg documents against a Record in my database. My Database is a Enquiry Management Database, and when projects come in they are logged, which when an enquiry is received they have associated documents with them. So what the plan is that the User who inputs projects can select the assign the associated to the record, and then when some else looks up the project that can see all related documents and also open them from my database. 1. Do i Store External Documents in the database or 2, Do I Link to the documents, I w...

Exchange 2k7 powershell truncates multi-value properties
Greetings, I'm running the following command: Get-SendConnector -Identity "Send Connector Name" | ft -autosize -wrap -property AddressSpaces We have over 20 domains in this list. When I run this command it lists 16 of these domains and then just sticks ... at the end. If I leave out the -wrap switch the output lists only 3 domains. How can I get this command to list ALL the contents of the AddressSpaces property? Thanks Try to pipe to format-list instead. --- Shay Levy Windows PowerShell MVP http://blogs.microsoft.co.il/blogs/ScriptFanatic...

How to round the numeric value?
Kindly tell me that how can we round the numeric value? Example: 1.2 = 1.5, 1.7 = 2.0 "Salman Saeed" <Salman Saeed@discussions.microsoft.com> wrote in message news:F5460D85-6DEE-4EC5-8E83-A1A5E158966A@microsoft.com... > Kindly tell me that how can we round the numeric value? > Example: 1.2 = 1.5, 1.7 = 2.0 Use the RND function..... =CEILING(A1,0.5) -- Regards Dave Hawley www.ozgrid.com "Salman Saeed" <Salman Saeed@discussions.microsoft.com> wrote in message news:F5460D85-6DEE-4EC5-8E83-A1A5E158966A@microsoft.com... > Kindly...

vlookup-Closest value
Dear Friends, How can I use Vlookup to give me the closest value greather than or equal to vlookup value.For example if my lookup value is 5 and I have 4.9 and 5.1,5.2 It chooses 5.1 and give me the corresponding value. Thank you, atatari wrote: > Dear Friends, > > How can I use Vlookup to give me the closest value greather than or equal to > vlookup value.For example if my lookup value is 5 and I have 4.9 and 5.1,5.2 > It chooses 5.1 and give me the corresponding value. > > Thank you, VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) The default behavi...

The most occurence of a value in a column
I have 1 to 5 as values in a column. How can I find the number with the most occurrence in that column? Thanks, cpliu Hi =MODE(A1:A100) -- Regards Frank Kabel Frankfurt, Germany "cpliu" <chanciusliuDeleteThis@yahoo.com> schrieb im Newsbeitrag news:Xns959C8C47C309BchanciusliuDeleteThi@130.133.1.4... > I have 1 to 5 as values in a column. How can I find the number with the > most occurrence in that column? > > Thanks, > > cpliu Try: =INDEX(A1:A5,MATCH(MAX(COUNTIF(A1:A5,A1:A5)),COUNTIF (A1:A5,A1:A5),0)) Array-entered, meaning press ctrl/shift/enter. H...

merging a value in a text field with each line in a memo field
I am trying to merge a value in a text field with the information in a memo field using a query to create a report. This is the query I am using: SELECT AVRelay+","+IPRange FROM T_Sites AVRelay is the text field and IPRange is the Memo. IPRange has multiple lines, each of which I need appended with the value in AVRelay for the report. It should look like. 2,192.168.1.0/24 2,192.168.0.0/24 2,192.168.2.0/24 Instead it looks like 2,192.168.1.0/24 192.168.0.0/24 192.168.2.0/24 I have searched far and wide but has come up empty. Please help! I would gues...

reformulating question regarding maximum value
I had an hour ago put a question up named "find maximum", but I had not formulated my query fully, hence this query. In each row, I want to find the second highest value in that row, and then as output give the value of that cell in the first row. For example: 1 2 6 8 6 5 4 3 2 4 5 6 3 2 3 8 6 4 5 3 7 2 3 1 The second largest value in the second row is nr 6. It is in the fourht cell of that row. The fourth cell of the first row is the value 8. This should be the output for the second row. The second largest value in the third row is the number 6. It is in the first cell of th...

Automatic formatting of minimum/maximum value in a range.
I want that if I select a range of numbers the minimum or maximum should be selected by a single command How about cond. formatting? 1. Select the range. 2. Go to Format > Conditional Formatting 3. Select "Formula Is" and copy in this: =OR(INDIRECT("rc",0)=MAX(rng),INDIRECT("rc",0)=MIN(rng)) where "rng" is a defined name for your range. HTH Jason Atlanta, GA >-----Original Message----- >I want that if I select a range of numbers the minimum or maximum should be >selected by a single command >. > What do you want to do with t...

Crystal XI will not show all possible values in a parameter field
Hello: We have a Crystal XI report that has one parameter value that is a "static" rather than "dynamic". It works fine and, when you type in a value upon refreshing the report, the data is correct. We made a copy of the report, because we want a second report where the parameter value is dynamic rather than static. We do not want a range, but we do want a parameter that will show all possible values on the left and allow you to pick and choose one or more of those values. For some reason, not all of the possible values are showing in this dynamic parameter field...

Inserting arrays as variables
How can I insert an array or range of variables (e.g. .042 to .168) into a formula? Alternatively, can I define variables (e.g. x, y) without having to use Visual Basic? I'm trying to create a chart which outputs values based on constants and a function involving the range of variables. Thanks for your help! {0.042,0.07,0.1,0.11,0.123,0.13,0.168} as an example -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "anthonyd" <anthonyd@discussions.microsoft.com> wrote in message news:404CE381-5890-458B-9A43-6EA3CB894591@micros...