How to pass Where Clause as a parameter to the stored procedure?

Question

4.00/5 (1 vote)

See more:

Hi,

SQL

I created one stored procedure

CREATE PROCEDURE DynamicWhereClause
(
   @input varchar(100)
)

AS

BEGIN

    DECLARE @sqlCommand varchar(1000)
    SET @sqlCommand = 'SELECT * FROM Employee ' + @input
    EXEC (@sqlCommand)

	
END

EX 1):

SQL

EXEC DynamicWhereClause @input = "WHERE FirstName = 'venky' AND LastName = 'palepu' AND Country = 'India' "

EX 2):

SQL

EXEC DynamicWhereClause @input = "WHERE FirstName = 'venky'  "

I am passing where condition to the stored procedure as a parameter
This is executing fine.

Could you please suggest me, How to create "DynamicWhereClause" stored procedure without EXEC in stored procedure? I need to Execute ("DynamicWhereClause" stored procedure without EXEC ) using above two examples.

Please guild me.
Thanks in advance.

Posted 30-Mar-11 20:24pm

venkatrao palepu

Updated 30-Mar-11 21:07pm

Sandeep Mewara

v3

Add a Solution

Comments

Member 9770473 23-Aug-13 9:26am

if column have integer datatype then your procedure not work

2 solutions

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)

Shingala Anil · Answer 1 · 2011-03-30T21:20:00

@AccountNumber As Varchar(50),
 @FirstName As Varchar(50),
 @LastName As Varchar(50),
@ActiveClinicID As Varchar(50),
 @ActivePhysicianID As Varchar(50),
 @ActivePON As Varchar(50),
 @TaxNumber As Varchar(50),
 @DateOfBirth As Datetime
 
AS  
BEGIN  
  Declare @Where AS Varchar(2000)
  declare @v_Query as varchar(3000)  
   Set @Where='Where 1=1'
	IF @AccountNumber <> ''
	BEGIN
	SET @Where =@Where + ' AND PatientMaster.AccountNumber = '''+@AccountNumber+''''
	END
	IF @FirstName <> ''
	BEGIN
	SET @Where =@Where + ' AND PatientMaster.FirstName LIKE ''%'+ @FirstName+'%'''
	END
	IF @LastName <> ''
	BEGIN
	SET @Where =@Where + ' AND PatientMaster.LastName LIKE ''%'+ @LastName+'%'''
	END
	IF @ActiveClinicID <> ''
	BEGIN
	SET @Where =@Where + ' AND PatientMaster.ActiveClinicID ='''+ @ActiveClinicID+''''
	END
	IF @ActivePhysicianID <> ''
	BEGIN
	SET @Where =@Where + ' AND PatientMaster.ActivePhysicianID ='''+ @ActivePhysicianID+''''
	END
	IF @ActivePON <> ''
	BEGIN
	SET @Where =@Where + ' AND PatientMaster.ActivePON ='''+ @ActivePON+''''
	END
	IF @DateOfBirth <> ''
	BEGIN
	--SET @Where =@Where + ' AND  PatientMaster.DateOfBirth='''+ CONVERt(CHAR(10),@DateOfBirth,101)+''''
	SET @Where =@Where + ' AND  CAST (CONVERt(CHAR(50),PatientMaster.DateOfBirth,101)AS DATETIME)='''+ CONVERt(CHAR(10),@DateOfBirth,101)+''''
	END
	
    
        set @v_Query='SELECT  PatientMaster.id, PatientMaster.Fullname, PatientMaster.DateOfBirth, PatientMaster.ActivePON,(staffMaster.FirstName +''  ''+staffMaster.LastName) As Physician, clinicMaster.Abbreviation as Clinic
					  FROM    PatientMaster INNER JOIN
                      clinicMaster ON PatientMaster.ActiveClinicID = clinicMaster.clinicid INNER JOIN
                      staffMaster ON PatientMaster.ActivePhysicianID = staffMaster.staffid  ' + @Where
        exec(@v_Query)

Shingala Anil · Answer 2 · 2011-04-04T20:00:00

SQL

SELECT  PatientMaster.id, PatientMaster.Fullname, PatientMaster.DateOfBirth, PatientMaster.ActivePON,(staffMaster.FirstName +''  ''+staffMaster.LastName) As Physician, clinicMaster.Abbreviation as Clinic
                      FROM    PatientMaster INNER JOIN
                      clinicMaster ON PatientMaster.ActiveClinicID = clinicMaster.clinicid INNER JOIN
                      staffMaster ON PatientMaster.ActivePhysicianID = staffMaster.staffid

+ @Where

How to pass Where Clause as a parameter to the stored procedure?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0