March 28, 2008 at 1:43 pm
Hello everyone, i m using SQL Server 2005, i need help for designing my query. I have 5 checkboxes with different fields in my front end application (C#.net) If user select the first checkbex which have textboxes e.g. DistrictID and TehsilID and click on serach button:My grid add these columns in my gridview and display the DistrictID,TehsilID in my Gridview and also filter my data on these two by using the values which i passed in the textboxex, and so on if user select another chkbox column added in the gridview. For this i have to buld Dynamic Query but i m a bit confused how to check all these as this passed in the select stmt+ in where clause also.
Here are the structure that i make, plz tell me is this a good way to accompish this or suggest me and help me to design this query.
CREATE PROCEDURE [dbo].[SearchSP_Ext]
@DistrictID int = null,
@TehsilID int = null,
@RangeFrom int = null,
AS
DECLARE @sql nvarchar(4000)
IF @DistrictID IS NOT NULL
SELECT @sql = N'' SELECT dbo.TvsRecords.TvsRecordID, DistrictID,DistrictName
FROM dbo.TvsRecords INNER JOIN
dbo.Districts ON dbo.TvsRecords.DistrictID = dbo.Districts.DistrictID
WHERE DistrictID =@xdistrictid ''
IF @RangeFrom IS NOT NULL
SELECT @sql = @sql + '' ( I m confused at this point, how to add this column in my Select list + where list)(if i have only wnt to add where clause its easy for me to add but i also add this column in my select list and for this may be i also used join to another table (in case)..
SELECT @paramlist = N''@xdistrictid int,
@Tehsilid int,
@xrangefrom int,
EXEC sp_executesql @sql, @paramlist, @DistrictID,@TehsilID,@RangeFrom
Plz tell me how can i make this query ,plz reply me asap, I m very greatfull to all of you.Thanx in Advance.
March 28, 2008 at 2:13 pm
why would you even think of using dynamic sql for this ??????? :sick:
If you know it's only the predicates that differ, why not have a bunch of if statements and yes hardcopy the queries and just foorsee the where-clause combinations you actualy need ?
Oldfashioned ? Maybe :doze:
performant ? why not ?
tunable ? way better.
Did you read the everlasting reffered "curse and blessings of dynamic sql".:cool:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 28, 2008 at 2:33 pm
While I agree with ALZDBA that actually writing the sql for each combination is probably better I will attempt to answer your question.
I would write the code something like this:
CREATE PROCEDURE [dbo].[SearchSP_Ext]
@DistrictID int = null,
@TehsilID int = null,
@RangeFrom int = null,
AS
DECLARE @select nvarchar(1000),
@from nvarchar(1000),
@where nvarchar(1000),
@sql nvarchar(4000)
Set @select = 'Select dbo.TvsRecords.TvsRecordID, DistrictID,DistrictName ' -- base select list
-- base from list
Set @from = 'From dbo.TvsRecords INNER JOIN
dbo.Districts ON dbo.TvsRecords.DistrictID = dbo.Districts.DistrictID '
IF @DistrictID IS NOT NULL
Begin
Set @where = ' Where DistrictID = @xdistictid '
End
IF @RangeFrom IS NOT NULL
Begin
-- I am assuming @RangeFrom contains a valid column name, if it does not then relace @RangeFrom with the column name you want
Set @select = @select + ', ' + @RangeFrom
Select @where = Case
When @where is null then 'Where ' -- add columns for where
Else @where + ' And '-- add columns for where
End
End
SELECT @paramlist = N''@xdistrictid int,
@Tehsilid int,
@xrangefrom int,
Set @sql = @select + ' ' + @from + ' ' + Coalesce(@where, '') -- coalesces returns the first non-null value
EXEC sp_executesql @sql, @paramlist, @DistrictID,@TehsilID,@RangeFrom
As you can see I build each piece of the Sql statement and then concatenate them when done evaluating all the criteria. I would be concerned about returning different select lists as this could mess with your UI code.
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
March 29, 2008 at 5:35 am
Thanx Jack your testing query really helping me a lot . Thankyou so much .
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply