I not not getting result from stored procedure

Question

1.00/5 (1 vote)

See more:

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 1-Oct-19 2:11am

Akshay malvankar

Updated 1-Oct-19 4:07am

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Aarti Meswania · Accepted Answer · 2019-10-01T04:07:00

Hello Akshay,

Please go through below code, Hope it helps

SQL

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! :)