Click here to Skip to main content
14,326,294 members
Rate this:
Please Sign up or sign in to vote.
i try to implement filter, i am passing parameter from from my code
following string i am sending to store procedure

and tsn.SurveyorName=''Keval Kakdiya''


following is my store procedure

procedure
(filterText varchar(8000)


SELECT DISTINCT tsd.State AS State, IFNULL(tsn.SurveyorName,0) SurveyorName, 
		 IFNULL(tni.NoOfInt, 0) NoOfInt, IFNULL(tsr.SampleReceived, 0) SampleReceived, 
         IFNULL(tce.CancelledEntries, 0) CancelledEntries,  
         IFNULL(ta.Approved, 0) Approved, IFNULL(td.DisApproved, 0) DisApproved
	 FROM tblsurveyordistrict tsd 
	 LEFT JOIN tblNoOfInt tni ON tsd.State = tni.State
	 LEFT JOIN tblSampleReceived tsr ON tsd.State = tsr.State
	 LEFT JOIN tblSurveyorName tsn ON tsd.State = tsn.State
	 LEFT JOIN tblCancelledEntries tce ON tsd.State = tce.State
	 LEFT JOIN tblApproved ta ON tsd.State = ta.State
	 LEFT JOIN tblDisapproved td ON tsd.State = td.State 
	 where 1=1 ' , filterText, '       // i am passing filter text here i dont know what this statement is doing
	 ORDER BY tsd.State;



i am not getting any record same above string i am pasing like this it working

SELECT DISTINCT tsd.State AS State, IFNULL(tsn.SurveyorName,0) SurveyorName, 
		 IFNULL(tni.NoOfInt, 0) NoOfInt, IFNULL(tsr.SampleReceived, 0) SampleReceived, 
         IFNULL(tce.CancelledEntries, 0) CancelledEntries,  
         IFNULL(ta.Approved, 0) Approved, IFNULL(td.DisApproved, 0) DisApproved
	 FROM tblsurveyordistrict tsd 
	 LEFT JOIN tblNoOfInt tni ON tsd.State = tni.State
	 LEFT JOIN tblSampleReceived tsr ON tsd.State = tsr.State
	 LEFT JOIN tblSurveyorName tsn ON tsd.State = tsn.State
	 LEFT JOIN tblCancelledEntries tce ON tsd.State = tce.State
	 LEFT JOIN tblApproved ta ON tsd.State = ta.State
	 LEFT JOIN tblDisapproved td ON tsd.State = td.State 
	 where 1=1 and tsn.SurveyorName=''Keval Kakdiya''    // this is working
	 ORDER BY tsd.State;


can anyone help me

What I have tried:

i tried everything but its not working
Posted
Updated 1-Oct-19 4:07am
Comments
F-ES Sitecore 1-Oct-19 9:52am
   
You can't dynamically add where clauses to a statement like that. Either build the entire SELECT as a string and use EXEC to execute it, or use a switch\case\or statement to cater for a list of known possible fields in the where clause. If you google "t-sql dynamic where clause" you'll find various ways of doing this.
ZurdoDev 1-Oct-19 9:53am
   
WHERE 1 = 1 means all records will be returned.
You have 2 single quotes. Why? This tells me we aren't seeing your exact code but instead are building a sql string and executing it.

Instead make a stored procedure and use parameters.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Hello Akshay,

Please go through below code, Hope it helps

CREATE PROCEDURE spGetSurveyOrDistrict 
	@SurveryorName VARCHAR(8000)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	SELECT 
		 DISTINCT tsd.State AS State, IFNULL(tsn.SurveyorName,0) SurveyorName, 
		 IFNULL(tni.NoOfInt, 0) NoOfInt, IFNULL(tsr.SampleReceived, 0) SampleReceived, 
         IFNULL(tce.CancelledEntries, 0) CancelledEntries,  
         IFNULL(ta.Approved, 0) Approved, IFNULL(td.DisApproved, 0) DisApproved
	FROM tblsurveyordistrict tsd 
	LEFT JOIN tblNoOfInt tni ON tsd.State = tni.State
	LEFT JOIN tblSampleReceived tsr ON tsd.State = tsr.State
	LEFT JOIN tblSurveyorName tsn ON tsd.State = tsn.State
	LEFT JOIN tblCancelledEntries tce ON tsd.State = tce.State
	LEFT JOIN tblApproved ta ON tsd.State = ta.State
	LEFT JOIN tblDisapproved td ON tsd.State = td.State 
	where /*1=1 ---NOTE: this is not needed it's always gonna be true*/
		tsn.SurveyorName = @SurveryorName --THERE YOU GO..................
	ORDER BY tsd.State;
END
GO
/* 
To test above sp you can select below line and execute it, after creating the sp.
EXEC spGetSurveyOrDistrict @SurveryorName = 'Keval Kakdiya'    
*/


Note: if you want to write dynamic query inside sp then you can do that referring Dynamic SQL Tutorial - Introduction, Examples and Query - DataFlair[^] but I will recommend to avoid dynamic queries for avoiding compilation, performance and SQL Injection issues.

Happy Coding! :)
   
Comments
Akshay malvankar 1-Oct-19 21:40pm
   
hey Aarti Meswania thanks for your reply, see above code is in mysql ok,
where /*1=1 ---NOTE: this is not needed it's always gonna be true*/
tsn.SurveyorName = @SurveryorName --THERE YOU GO..................
above line is not always going to surveyorname is gona be any 'search stirng'

and tsn.SurveyorName=''Keval Kakdiya'' // this line is just example
it could another string could be append on this for e.g

and tsn.SurveyorName=''Keval Kakdiya'' and state = 'abc' // this could be another string

my question is when i passing static paramter
like this where 1=1 and tsn.SurveyorName=''Keval Kakdiya'' // its working

but when i paasing this paramter through my code to stored procedure
and tsn.SurveyorName=''Keval Kakdiya'' // it not working


there is any way i can debugg stroed procedure what actully end query is create in mysql workbench
Aarti Meswania 4-Oct-19 1:15am
   
Hi Akshay,

That is called "dynamic query" for which you can refer link I have mentioned above. but I am trying to say that it is not safe as well as it might give you results slower. in above link see example of dynamic query
DECLARE @SQLString AS NVARCHAR(MAX);
SET @SQLString = N'SELECT ' +@columnnames+N' FROM ' + @tablename;
EXEC(@SQLString);


Let me suggest another approach, You have to find data in table so that columns are fixed. you want to filter 10 columns - 15 columns or 100 columns no matter, it's fine...
you can do that easily without dynamic query.
How???
WHERE
(tsn.SurveyorName= @SurveyorName OR @SurveyorName IS NULL)
AND (state = @state OR @state IS NULL)

kind of this you can write as many as you want.
and pass value to particular parameter(s) which you want, and avoid passing value to parameter(s) which you don't want, and see the magic :) It will give you exact output you seek. moreover, it's good and maintainable approach to write code:)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100