August 20, 2008 at 1:04 pm
I need to create a windows form that dynamically searchs the DB. After trying a few different way of doing this, I have determined that a SP would be the best way. The code is as follows:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[DynamicSQLSearch]
@PetroNo nvarchar(8) = NULL,
@FieldNo nvarchar(15) = NULL,
@SampleNo nvarchar(12) = NULL,
@ReportTo nvarchar(50) = NULL,
@investigator nvarchar(50) = NULL,
@debug bit = 0 AS
DECLARE @sql nvarchar(4000),
@paramlist nvarchar(4000)
SELECT @sql =
'SELECT
Identification.RefNo, Identification.PetroNo, Identification.SampleNo,Identification.DateSubmitted, Identification.DateExamined, Identification.ReportTo,
Identification.Investigator, Identification.FieldNo, Location.PropID, Location.BHNo, Location.BHDepthofSample, Location.RefNo AS LocationRefno, Description.Description, lstProperty.PropertyName, lstProperty.Township, lstProperty.ProvState, lstProperty.Country,
SampleInfo.SampleType, SampleInfo.SampleID, SampleInfo.PetroTreatment, SampleInfo.GeoChemistry, SampleInfo.MetamorphicGrade,
Lithology.RockAbbr, Lithology.RockName, Lithology.Protolith, Location.GridName, Location.CoordCode, Location.MineLevel, Location.GridEW,
Location.GridNS, Location.GridElevation, Location.Units, Location.UTMZone, Location.UTMX, Location.UTMY, Location.Altitude, Location.AltitudeUnits,
Location.NTS, Location.AreaZone
FROM
Identification Left OUTER JOIN
Lithology ON Identification.RefNo = Lithology.RefNo Left OUTER JOIN
SampleInfo ON SampleInfo.RefNo = Identification.RefNo Left OUTER JOIN
Description ON Identification.RefNo = Description.RefNo Left OUTER JOIN
lstProperty Full OUTER JOIN
Location ON lstProperty.PropID = Location.PropID ON Identification.RefNo = Location.RefNo
WHERE 1 = 1'
IF @PetroNo IS NOT NULL
SELECT @sql = @sql + ' AND PetroNo LIKE @xPetroNo + ''%'''
IF @FieldNo IS NOT NULL
SELECT @sql = @sql + ' AND FieldNo LIKE @xFieldNo + ''%'''
IF @SampleNo IS NOT NULL
SELECT @sql = @sql + ' AND SampleNo LIKE @xSampleNo + ''%'''
IF @ReportTo IS NOT NULL
SELECT @sql = @sql + ' AND ReportTo LIKE @xReportTo + ''%'''
IF @investigator IS NOT NULL
SELECT @sql = @sql + ' AND Investigator LIKE @xInvestigator + ''%'''
SELECT @sql = @sql + ' ORDER BY Refno'
IF @debug = 1
PRINT @sql
SELECT @paramlist = '@xPetroNo nvarchar(8),
@xFieldNo nvarchar(15),
@xSampleNo nvarchar(12),
@xReportTo nvarchar(50),
@xInvestigator nvarchar(50)'
EXEC sp_executesql @sql, @paramlist,
@PetroNo, @FieldNo, @SampleNo,
@ReportTo, @investigator
This SP work great within the SQL management studio but when I test it in VB 2005 the search results return an inner join on the seach query and not a left outer join as specified in the SP code. It seems VB 2005 is overridding the SP, any ideas on how to fix this would be great!!
Steve
August 20, 2008 at 1:23 pm
If you are just calling the SP in VB 2005 using a command object it does not change the query within the SP. Can you post your VB Code?
The other thing you can do is run Profiler while debugging the VB app and verify that what you think is being done is really what IS being done.
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
August 20, 2008 at 2:10 pm
Here it is.
Private Sub FillToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FillToolStripButton.Click
Try
Me.DynamicSQLSearchTableAdapter.Fill(Me.PetrographyDataSet.DynamicSQLSearch, PetroNoToolStripTextBox.Text, FieldNoToolStripTextBox.Text, SampleNoToolStripTextBox.Text, ReportToToolStripTextBox.Text, InvestigatorToolStripTextBox.Text, New System.Nullable(Of Boolean)(CType(DebugToolStripTextBox.Text, Boolean)))
Me.LocationTableAdapter.Fill(Me.PetrographyDataSet.Location)
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
End Sub
All I am doing is passing argument(s) to the SP.
August 20, 2008 at 2:32 pm
When you reference a column in one of the unpreserved tables of an outer join, that join will in effect be turned into an inner join.
Example:
SELECT [columns]
FROM table1 t1
LEFT JOIN table2 ON t1.key = t2.key
WHERE t1.col1 = somevalue;
The above uses an outer join, and since we have not used a column from table2 in the where clause - it works as expected.
SELECT [columns]
FROM table1 t1
LEFT JOIN table2 ON t1.key = t2.key
WHERE t2.col1 = somevalue;
This one will not work as expected and is the same as performing an inner join. The reason is quite simple actually, and that is because null's propagate. Null is not equal to null, nor is it equal to anything else, so when you have a null value in t2.col1, which you will because of the outer join, and it is compared to 'somevalue' the answer is unknown and the row is eliminated from the results.
SELECT [columns]
FROM table1 t1
LEFT JOIN table2 ON t1.key = t2.key
WHERE (t2.col1 = somevalue OR t2.col1 IS NULL);
This one works because we are explicitly checking for the NULL value.
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
August 20, 2008 at 2:33 pm
Were you aware that the "WHERE" conditions are applied after the "OUTER JOINS" are performed, so should any of the columns be in the outer joined tables, this change the joins into innser joins?
To retain the left outer join, the restriction condition must be included in the ON clause. The SQL needs to be like:
LEFT OUTER JOIN
SampleInfo
ON SampleInfo.RefNo = Identification.RefNo
AND SampleNo LIKE @xSampleNo + ''%'''
LEFT OUTER JOIN
Description ....
SQL = Scarcely Qualifies as a Language
August 20, 2008 at 2:49 pm
Carl Federl (8/20/2008)
Were you aware that the "WHERE" conditions are applied after the "OUTER JOINS" are performed, so should any of the columns be in the outer joined tables, this change the joins into innser joins?To retain the left outer join, the restriction condition must be included in the ON clause. The SQL needs to be like:
LEFT OUTER JOIN
SampleInfo
ON SampleInfo.RefNo = Identification.RefNo
AND SampleNo LIKE @xSampleNo + ''%'''
LEFT OUTER JOIN
Description ....
Nice catch to you and Jeffrey. I did not examine the code being built carefully enough.
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
August 21, 2008 at 12:39 pm
Thanks for all the replies.:)
While doing some testing with the Profiler I noticed that when I executed the SP in the SQL studio it returned this:
EXEC@return_value = [dbo].[DynamicSQLSearch]
@PetroNo = N'c93'
Only the parameter supplied was populated into the where clause
WHERE 1 = 1 AND PetroNo LIKE @xPetroNo + '%' ORDER BY Refno
When I ran the same Query in my VB form it returned this:
EXEC@return_value = [dbo].[DynamicSQLSearch]
@PetroNo = N'c93',
@FieldNo = N'',
@SampleNo = N'',
@ReportTo = N'',
@investigator = N''
The Where clause was populated with all the parameters.
WHERE 1 = 1 AND PetroNo LIKE @xPetroNo + '%' AND FieldNo LIKE @xFieldNo + '%' AND SampleNo LIKE @xSampleNo + '%' AND ReportTo LIKE @xReportTo + '%' AND Investigator LIKE
@xInvestigator + '%' ORDER BY Refno
So to handle this problem I changed my If Statements to If...Else Statements
IF @PetroNo IS NULL OR @PetroNo = N''
Else
SELECT @sql = @sql + ' AND PetroNo LIKE @xPetroNo + ''%'''
Thanks for all the help.
August 21, 2008 at 12:52 pm
You can also change the VB code to pass in nulls for those parameters, but what you are doing works.
You don't mention if you corrected the issue(s) in the WHERE clause that cause an OUTER JOIN to act like an INNER JOIN as mentioned by Jeffrey and Carl. If you don't fix those you could still face the same problem.
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
August 22, 2008 at 6:03 am
The If...Else statement fixed that issue. I'm getting the same results from the vb form as I am from running the SP in the SQL studio.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply