May 29, 2003 at 10:05 pm
Hi guys,
I would really appreciate if you can help me out. My problem is that I have 2 tables named Case30 and Caseold. Each of the table has 10 fields. Case 30 contains cases that is 30 days old while caseold table contains records till 2 years. According to the entry of the user in Visual Basic form in any of the 10 fields I have to fetch the recordset from both of the tables. I want it to be done as stored procedure using dynamic queries. Efficiency of the query is also important. The thing is that how would i query those tables on the basis of any of those 10 inputs provided by the user. The user might enter just 1 of the field and leave other fields blank or enter all of them to query the resultset. How would you able to query against depending on the no. of inputs provided by user?
Hoping to have your reply as soon as possible.
May 29, 2003 at 10:50 pm
Give this a shot. I'm not sure what your datatypes are but notice param1 is an int and notice that you have to cast it to a varchar to concatenate it.
CREATE proc test_it @param1 int = null,
@param2 varchar(10) = null,
@param3 varchar(10) = null,
@param4 varchar(10) = null,
@param5 varchar(10) = null,
@param6 varchar(10) = null,
@param7 varchar(10) = null,
@param8 varchar(10) = null,
@param9 varchar(10) = null,
@param10 varchar(10) = null
AS
BEGIN
DECLARE @where_clause varchar(8000),
@sql varchar(8000),
@return varchar(2)
SELECT @where_clause = 'WHERE 1=1',
@return = char(13)+char(10)
SELECT @where_clause = @where_clause + CASE WHEN @param1 IS NOT NULL THEN @return+' AND col1 = '+CAST(@param1 AS varchar(10)) ELSE '' END+
CASE WHEN @param2 IS NOT NULL THEN @return+' AND col2 = '''+@param2+'''' ELSE '' END+
CASE WHEN @param3 IS NOT NULL THEN @return+' AND col3 = '''+@param3+'''' ELSE '' END+
CASE WHEN @param4 IS NOT NULL THEN @return+' AND col4 = '''+@param4+'''' ELSE '' END+
CASE WHEN @param5 IS NOT NULL THEN @return+' AND col5 = '''+@param5+'''' ELSE '' END+
CASE WHEN @param6 IS NOT NULL THEN @return+' AND col6 = '''+@param6+'''' ELSE '' END+
CASE WHEN @param7 IS NOT NULL THEN @return+' AND col7 = '''+@param7+'''' ELSE '' END+
CASE WHEN @param8 IS NOT NULL THEN @return+' AND col8 = '''+@param8+'''' ELSE '' END+
CASE WHEN @param9 IS NOT NULL THEN @return+' AND col9 = '''+@param9+'''' ELSE '' END+
CASE WHEN @param10 IS NOT NULL THEN @return+' AND col10 = '''+@param10+'''' ELSE '' END
SELECT @sql = 'SELECT * FROM Case30 '+@return+@where_clause+@return+
'UNION ALL'+@return+
'SELECT * FROM CaseOld '+@return+@where_clause
-- EXEC (@sql)
SELECT @sql
END
May 30, 2003 at 8:19 am
Hi brown,
Thanks for your reply. I will have to try this out. But the thing is that out of this 10 fields some have inputs as strings some have as integers and 4 of the fields have as dates. And the user should get the resultset of the join from both the tables Case30 and Caseold by entering any no. of parameters. I would appreciate if you can suggest something for that too.
May 30, 2003 at 8:26 am
Al of the parameters are optional. You could call the proc like this for the parameters you do have.
exec test_it @param1=10, @param5 = 'a', @param8 = 'b'
or you could call it like this
exec test_it 10,null,null,null,'a',null,null,'b',null,null
either way it would produce the sql only filtering on what you passed in. As for dates, you wouldn't need to do anything special for them other than to declare the paramaters as datatimes and cast it to a varchar like the example for the int.
May 31, 2003 at 2:57 pm
Thanks for it. Do u think using a temp table in this example would be helpful? If yes then how to do it. I would really appreciate if you can suggest something for it.
June 1, 2003 at 5:35 pm
I'm not sure if I understand all the issues, but since you have 2 tables that are basically the same, but with just different aged data, I would first make a view that unioned them together.
When I need to make stored procedures that have parameters that may or may not be present, I will always do this:
Make sure all the parameters have defaults (even if it's NULL, which it usually is)
Then you can phrase your stored procedure something like this:
CREATE my_procedure
(
@param1 int = NULL,
@param2 varchar(50) = NULL,
@param3 datetime = '1/1/1900')
AS
SET NOCOUNT ON
SELECT *
FROM your_view
WHERE (@param1 = NULL OR @param1 = field1)
AND (@param2 = NULL OR @param2 = field2)
AND (@param3 = '1/1/1900' OR @param3 = field3)
David
June 1, 2003 at 7:38 pm
HI!
what you can do in visula basic only save the field name in tag property of object. use a loop to check the value has been filled by the user in each object and dynamically build the where clause and attached to the statement and execute it with the help of recordset. now if table changes in future you have to edit the visual basic code.
June 1, 2003 at 10:44 pm
3 additional suggestions.
1. Add a check that prohibits injected SQL from being run in you where clause. (It's actually a standard in our office now thanks to the advice here)
2. Don't use a union query, it will kill your performance. Use two sp's, one to return the records from each table.
3. Primary Keys and Indexes! Be sure you have them!
Peace Out!
Regards,
Matt
June 1, 2003 at 11:45 pm
quote:
3 additional suggestions.1. Add a check that prohibits injected SQL from being run in you where clause. (It's actually a standard in our office now thanks to the advice here)
2. Don't use a union query, it will kill your performance. Use two sp's, one to return the records from each table.
3. Primary Keys and Indexes! Be sure you have them!
Peace Out!
1. Definitely a good idea (especially with procedures such as this one)
2. Not necessarily. See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_06_17zr.asp
3. Definitely!
David
June 2, 2003 at 5:41 am
quote:
According to the entry of the user in Visual Basic form in any of the 10 fields I have to fetch the recordset from both of the tables. I want it to be done as stored procedure using dynamic queries. Efficiency of the query is also important.
Are the multiple criteria an OR condition or an AND condition? For instance if Param1 = 'ABC' and Param2 = 'XYZ', do you include all records that have 'ABC' *OR* 'XYZ', or do you include include all entries that have 'ABC' *AND* 'XYZ'?
Rather than a dynamic query, how about setting default values for your inputs parameters that will cause all data to be returned. For character matches, you can use a LIKE clause and set the default of the character parameter to '%'. For numeric matches, my experience is that numbers are included to establish minimum and/or maximums. My experience is also that dates are typically used to define ranges. You can use this knowledge to construct valid defaults.
For example, if a numeric parameter is @BillableTime and it is supposed to limit records to those with more billable time, then you could set that parameter to default to 0 (presuming you can't have negative billable time).
You can default dates to '1900-01-01' and '9999-12-31' for lower and upper ranges, respectively.
A simple UNION will return your results. As long as you have covering indexes, the results should be efficient.
June 2, 2003 at 10:37 pm
Thanks for all your help man. But I think u guys have still not understood my problem properly. Read the solution given by Brown. Its preety much what i want but the thing is that the stored procedure given by him doesnot get executed when i enter parameters in the procedure.
I would really appreciate if someone can create a stored procedure in which I can get the result from Case30 and caseold table by either entering just 1 field leaving other fields blank or by entering any of the combination of 10 fields.
Sample:-
1)fname -
2)lname -
3)caseno. -
4)ctype -
5)sender -
6)Date -
7)Due date-
8)final date-
9)Hold -
10)mdate -
Now this is an asp page and on this form if user enters value in any of the fileds leaving other blank he should get the result.
So I wanted a stored procedure for that.
I would appreciate if somebody can do it.
Brown ur stored procedure' s idea is right but it doesnot get executed when i enter the parameters. Just check it out.
Once again thanks to all for all the pain.
June 2, 2003 at 11:52 pm
Have a look at this, may help a bit!
Create Procedure Test(@fname varchar(100)=NULL,
@lname varchar(100)=NULL,
@caseno INT=NULL,
@ctype INT=NULL,
@sender varchar(100)=NULL,
@sDate datetime=NULL,
@Due datetime=NULL,
@final datetime=NULL,
@Hold varchar(100)=NULL,
@mdate datetime=NULL) AS
Select @fname as fname,@lname as lname,@caseno as caseno,
@ctype as ctype,@sender as sender,@sDate as sDate,@Due as Due,@final as final,@Hold as Hold,@mdate as mdate
/*
(1) Expand this to suite the rest of your requirements
(2) Just get it going for the one table first.
(3) 'exec test ' will be the initialize string in the asp side, let say strExec="exec test"
then loop through the parameter controls with
"if len(sender.text)>0 then strExec=strExec & "@sender='" & send.text & "'". Reminder remove a comma if string end s with a comma after accumulating the parameters.
Example:
Exec test @Lname='Lname',@Fname='FNAME',@caseno=2,@sDate='1 Jan 2003'
Select *
From SomeTable
Where
Case When @Fname is Null Then '1'
Else Case When @Fname=Fname Then '1' Else '0' End = '1' And
Case When @sDate Null Then '1'
Else Case When @sDate=sDate Then '1' Else '0' End = '1' And
Case When @caseno Null Then '1'
Else Case When @caseno=caseno Then '1' Else '0' End = '1'
*/
Return
Exec test @Lname='Lname',@Fname='FNAME',@caseno=2,@sDate='1 Jan 2003'
June 3, 2003 at 11:39 am
I have the exec commented out in my example. It produces a query that has an accurate where clause without all the other extraneous stuff. All you have to do to get it to run is uncomment out the exec and change it to match your structure.
June 3, 2003 at 5:48 pm
Boy, you guys are brave!
I NEVER use dynamic SQL EVER in ANY Production system.
It does come in handy for 1-off utilities that I write and use myself.
For Queries that return the same result set and the predicate varies (the WHERE Clause) I use a stored procedure that accepts all of the search attributes (and defaults them to NULL if not passed). The sp has a single hard coded query with any join logic required to return the attributes in the result set. The key is that it also contains a join to a User Defined Function that accepts the search criteria as input parameters. This UDF returns a single column table containing the PK values that meet the search criteria and restricts rows returned to only those matching the search criteria.
Here is a simple example:
SELECT c.FirstName
,c.LastName
,c.Address1
,ca.Address2
,ca.City
,ca.State
,ca.Zip
,HomePhone = cp.PhoneNumber
FROM dbo.Customer c
WITH (NOLOCK)
JOIN dbo.CustomerAddress ca
WITH (NOLOCK)
ON c.CustomerID = ca.CustomerID
LEFT OUTER
JOIN CustomerPhone cp
WITH (NOLOCK)
ON c.CustomerID = cp.CustomerID
AND cp.PhoneType = 'Home'
-- This join is used instead of a WHERE clause to limit results based on input parameters
JOIN dbo.udf_CustomerSearch(@Lastname
,@City
,@PhoneNumber) k
ON c.CustomerID = k.CustomerID
Be careful out there.
June 3, 2003 at 6:48 pm
PCNutall, can you give us an idea of what your udf looks like? It sounds to me like all you've done is move the dynamic aspect of the sql to your udf?
David
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply