Passing A Single String to a Stored Procedure

[bundyld]

Hello all,

I have been reading through many posts looking for a solution to something that I want to try at work! I have seen many posts about sending a array list of strings with values to a Stored Procedure to make a select statment on a database.

My goal is to have a sql statements where clause, and anything else past the were clause built in a servlet like you would for a select call in the servlet, but take the where clause and pass it to a stored procdure as a string.

So in the stored procedure I would like it to build the where clause based on the string passed to the SP. Is this possible? I know how to declare and concat in SP's and have the select be something of this nature: SELECT TABLE.COLUMN,TABLE.COLUMN FROM TABLE "AND HERE IS WHERE I WANT TO CONCAT THE WHERE CLAUSE THAT IS PREBUILT AND PASSED TO THE SP!" So I know the '"+@passedparam
Can I do this?

I know that the execution times of SELECTS in SP's are greatly improved over views, which we are using now, because the Statment is precompiled! So this is why I am trying to do this for selects that have multiple where criteria or a WHERE and ORDER BY, something to that nature.

HERE IS A Stored procdure that I am currently using that only has one WHERE criteria so I am just passing the numeric value of the key. I want to pass the entire where clause and anything after it though!

CREATE PROCEDURE AUDITINFO @audit_no as numeric
AS
SELECT
dbo.CUSTOMER.CUSTOMER_NAME,
dbo.CUSTOMER.CUSTOMER_ADDRESS1, dbo.CUSTOMER.CUSTOMER_ADDRESS2,
dbo.CUSTOMERGROUPCUSTOMER.CUSTOMERGROUP_NO, dbo.CUSTOMERGROUP.CUSTOMERGROUP_NAME,
dbo.PATIENT.PATIENT_ESSN,
dbo.PATIENT.PATIENT_DSSN,
dbo.PATIENT.PATIENT_FIRST_NAME, dbo.PATIENT.PATIENT_LAST_NAME, dbo.PATIENT.PATIENT_ADDRESS1,
dbo.PATIENT.PATIENT_ADDRESS2,
dbo.PATIENT.PATIENT_CITY,
dbo.PATIENT.PATIENT_STATE,
dbo.PATIENT.PATIENT_ZIPCODE
FROM
dbo.AUDIT INNER JOIN
dbo.AUDITAPPEALMASTER ON
dbo.AUDIT.AUDITAPPEALMASTER_NO = dbo.AUDITAPPEALMASTER.AUDITAPPEALMASTER_NO INNER JOIN
dbo.CUSTOMERPATIENT ON dbo.AUDITAPPEALMASTER.CUSTOMERPATIENT_NO = dbo.CUSTOMERPATIENT.CUSTOMERPATIENT_NO
INNER JOIN
dbo.PATIENT ON
dbo.CUSTOMERPATIENT.PATIENT_NO = dbo.PATIENT.PATIENT_NO
INNER JOIN
dbo.CUSTOMERGROUPCUSTOMER INNER JOIN
dbo.CUSTOMER ON
dbo.CUSTOMERGROUPCUSTOMER.CUSTOMER_NO = dbo.CUSTOMER.CUSTOMER_NO
INNER JOIN
dbo.CUSTOMERGROUP ON dbo.CUSTOMERGROUPCUSTOMER.CUSTOMERGROUP_NO = dbo.CUSTOMERGROUP.CUSTOMERGROUP_NO ON
dbo.CUSTOMERPATIENT.CUSTOMERGROUPCUSTOMER_NO = dbo.CUSTOMERGROUPCUSTOMER.CUSTOMERGROUPCUSTOMER_NO
LEFT OUTER JOIN
dbo.AUDITDETAIL ON dbo.AUDIT.AUDIT_NO = dbo.AUDITDETAIL.AUDIT_NO

// This is where I would like to have the string param start, and replace the entire where clause and anything else from here out!

WHERE
AUDIT.AUDIT_NO=@audit_no
GROUP BY dbo.CUSTOMER.CUSTOMER_NAME, dbo.CUSTOMER.CUSTOMER_ADDRESS1, dbo.CUSTOMER.CUSTOMER_ADDRESS2,
dbo.CUSTOMERGROUPCUSTOMER.CUSTOMERGROUPCUSTOMER_NO, dbo.CUSTOMERGROUPCUSTOMER.CUSTOMER_NO, dbo.CUSTOMERGROUPCUSTOMER.CUSTOMERGROUP_NO, dbo.CUSTOMERGROUP.CUSTOMERGROUP_NAME, dbo.CUSTOMERGROUP.CUSTOMERGROUP_ADDRESS1, dbo.CUSTOMERGROUP.CUSTOMERGROUP_ADDRESS2,
dbo.CUSTOMERPATIENT.CUSTOMERPATIENT_NO, dbo.CUSTOMERPATIENT.PATIENT_NO, dbo.AUDITAPPEALMASTER.AUDITAPPEALMASTER_NO,
dbo.AUDITAPPEALMASTER.SERVICE_FROM_DATE, dbo.AUDITAPPEALMASTER.SERVICE_THRU_DATE, dbo.PATIENT.PATIENT_ESSN,
dbo.PATIENT.PATIENT_DSSN,
dbo.PATIENT.PATIENT_FIRST_NAME, dbo.PATIENT.PATIENT_LAST_NAME, dbo.PATIENT.PATIENT_ADDRESS1,
dbo.PATIENT.PATIENT_ADDRESS2,
dbo.PATIENT.PATIENT_CITY,
dbo.PATIENT.PATIENT_STATE,
dbo.PATIENT.PATIENT_ZIPCODE,
dbo.PATIENT.PATIENT_BIRTH_DATE

Thanks in advance for any help or direction you can provide.
Lance

 

[cheyney]

in this case you would have to use dynamic SQL which isn't pre-compiled. The code to construct the dynamic SQL statement would be pre-compiled, but the execution of what you create would be parsed for the first time when you run it.

You can run dynamic SQL using exec or sp_executesql

check Books Online for syntax

Cheyney

 

[cheyney]

pardon me, you can't use exec[cute]

cheyney

 

[bundyld]

cool, thank you. I did some research and found it. We have all of our views replaced with stored procedure calls now! SQL Statment execution times are about 20 times faster, from 130ms to 15ms on average.

Thanks again,
Lance