You are here:

MS SQL Server/SQL Server 2012 stored procedures

Advertisement


Question
Hello,

I have been tasked with creating a search function using Microsoft's K2 environment along with SQL Server 2012. I have created a database and a bunch of tables in SQL Server and am now trying to create a Search form.
What I have to do is to create a search form for hospital researchers to view blood samples. I have a table called SAMPLE, which contains sampleId, location, sampletype. I also have a table called PATIENT that contains things like patientId, dateOfBirth, ethnicity, diagnosis, there is also a table called STUDY, that contains studyCode and studyDesc. Finally there is a linking table called PATIENT_SAMPLE which contains studyCode, patientId, sampleId.
What I would like to do is to create a form that allows people to search for samples based on sample information, patient information or study information.
So a scientist might want to search for "All samples where the patient is born after 01/01/2000, is of african origin and is registered on Psoriasis XA study".
How would I create a stored procedure that could do something like this?

Thanks

Answer
I am assuming that you know how to build a query for any specific, given criteria, such as your example. The inference is that you want to create a stored procedure to accept parameters from the form and dynamically query the database.  

There are 2 general methods for doing that. Which you choose depends on several factors, including performance and complexity of your parameters (and, frankly, personal preference).  

Method 1 : Include a parameter for each criteria your form might pass, then build an appropriate SQL statement in a VARCHAR variable, then execute that using sp_executesql.  Simple example:
CREATE PROCEDURE MyProc (@DOB_Param CHAR(10) NULL, @Ethnicity_Param VARCHAR(50) NULL, @Study_Param VARCHAR(50) NULL )
--pass all parameters as strings.  
--Date params passed as a string in yyyy-mm-dd format
BEGIN
DECLARE @SQL VARCHAR(MAX)

SET @SQL = 'SELECT [blahblahblah]  
FROM PATIENT_SAMPLE PS
LEFT JOIN PATIENT PAT ON PAT.PatientID = PS.PatientID
LEFT JOIN SAMPLE SAM ON SAM.SampleID = PS.SampleID
LEFT JOIN STUDY STU ON STU.StudyCode = PS.StudyCode  
WHERE 1 = 1 ' --This is just so additional criteria can be added using "AND ..."

--Check each parameter one at a time:
IF @DOB_Param IS NOT NULL  
 SET @SQL = @SQL + ' AND PAT.DataOfBirth = {' + @DOB_Param + '} '

IF @Ethnicity_Param IS NOT NULL
 SET @SQL = @SQL + ' AND PAT.Ethnicity = ''' + @Ethnicity_Param + ''' '

IF @Study_Param IS NOT NULL  
 SET @SQL = @SQL + ' AND STU.StudyDesc = ''' + @Study_Param + ''' '

And so on. Once you've examined each parameter and added to the SQL statement variable as appropriate, you can execute it using:
sp_executesql @SQL

Some notes on this method:
A) You might want an order by clause. Actually, you might want to "build" the ORDER BY the same way as you built the WHERE, using options available on the form.
B) You will probably want to add range and/or list capabilities as search criteria. For example, "patients born in May of 1958" or "patients in study groups ABC and DEF and XYZ" or "patients in study groups "ABC or DEF or XYZ". That will require some straightforward modification of the sample code above. Like:
CREATE PROCEDURE MyProc (@DOB_Start CHAR(10) NULL, @DOB_End CHAR(10) NULL.....
then, when building the WHERE clause:
IF @DOB_Start IS NOT NULL
 IF @DOB_End IS NULL
   --If end date is null, start date is only criteria:
   SET @SQL = @SQL + ' AND PAT.DataOfBirth = {' + @DOB_Start + '} '
 ELSE
   SET @SQL = @SQL + ' AND PAT.DataOfBirth BETWEEN {' + @DOB_Start + '} AND {' + @DOB_End + '} '


The list option gets more complicated, but since you're using SQL 2012 you can use TABLE-VALUED PARAMETERS. Not being a .Net programmer, I'm not sure how you code it on the front end. Sounds like some homework for you. :-)


Method 2 : Similar to Method 1, in that you have a parameter for each possible criteria (or 2 parameters for ranges). However, in this method your SELECT statement is hardocde (not built into a variable) using ALL parameters, checking for NULL "inline". Again, using the simple example, and assuming the same parameters as in Method 1 (except the parameters should all be of the correct type, rather than all strings, since you're not building a big string to execute):
SELECT [blahblahblah]
FROM PATIENT_SAMPLE PS
LEFT JOIN PATIENT PAT ON PAT.PatientID = PS.PatientID
LEFT JOIN SAMPLE SAM ON SAM.SampleID = PS.SampleID
LEFT JOIN STUDY STU ON STU.StudyCode = PS.StudyCode  
WHERE  
PAT.DateOfBirth BETWEEN ISNULL(@DOB_Start,{1900-01-01}) AND ISNULL(@DB_End,{2200-12-31})
AND
PAT.Ethnicity = ISNULL(@Ethnicity_Param,PAT.Ethnicity)
AND
STU.StudyDesc = ISNULL(@Study_Param, STU.StudyDesc)
...and so on, including an ORDER BY if needed.


Note that the above is all off the top of my head, so syntax will almost certainly need tweaking. And obviously [blahblahblah] is the list of columns you want to return.

One note regarding sp_executeSQL: It has the ability to have have parameters passed into it. You can research it and see if you'd rather use that. I suspect it will just make a complicated thing more complicated, but maybe not.

Also, you'll need to check the passed parameters for reasonableness and special characters (mainly if you use Method 1, since @SQL = @SQL + ' AND SomeColumn = ''' + @Param + ''' ' will break if @Param contains a single quote. And I'd be remiss if I didn't remind you that there are security risks in such activities (dynamically building SQL from user-provided input).

Finally, I mentioned performance. I don't have a gut feel over which of those would provide the best performance. Neither of them will be that great, however, as there's really no way for SQL to build a plan given all the dynamic stuff going on.

I've put a lot in here, so feel free to ask followups for clarification and extension.  

MS SQL Server

All Answers


Answers by Expert:


Ask Experts

Volunteer


David Vaughn

Expertise

Performance tuning; T-SQL syntax;

Experience

Over 25 years of IT experience, the last 15 as a SQL Server developer/DBA.

Education/Credentials
Truman State University

©2016 About.com. All rights reserved.