ExamGuru IT/.Net Guru : Dynamic WHERE Clause on ASPFriends.com 'ngfx-sqlclient' list

.NETGURU

Dynamic WHERE Clause

Messages

Related Types

This message was discovered on ASPFriends.com 'ngfx-sqlclient' list.

Administrator

Hello All,

I am trying to develop a stored procedure that has a dynamic WHERE =
clause. Here is what I have currently, but it isn't working. The =
@Thing1 variable is a WHERE String that I develop on the fly on the =
website. The error I am getting is that the is a problem near the 'as' =
by @Thing1

CREATE PROCEDURE sp_GetAsianMatches
@Thing1 as VarChar(2000)

AS
Declare @Stuff2 VarChar(2000)

Set @Stuff2 =3D @Thing1

SELECT tbl_CustomerInfo.CustomerID, (ROUND(DATEDIFF(dd, =
tbl_CustomerAnswers.BirthDate, GETDATE()) / 365, 0) as Age, =
tbl_CustomerInfo.CustomerFirstName, tbl_CustomerInfo.MainImage, =
tbl_CustomerInfo.SubscriptionTypeID=20
FROM tbl_CustomerInfo LEFT OUTER JOIN tbl_State ON =
tbl_CustomerInfo.State =3D tbl_State.StateID LEFT OUTER JOIN =
tbl_Countries ON tbl_CustomerInfo.Country =3D tbl_Countries.CountryID =
LEFT OUTER JOIN tbl_CustomerSpokenLanguageAnswers RIGHT OUTER JOIN =
tbl_CustomerAnswers ON =
tbl_CustomerSpokenLanguageAnswers.CustomerAnswerID =3D =
tbl_CustomerAnswers.tbl_CustomerAnswersID LEFT OUTER JOIN =
tbl_CustomerLookingForChoices ON =
tbl_CustomerAnswers.tbl_CustomerAnswersID =3D =
tbl_CustomerLookingForChoices.CustomerAnswersID ON =
tbl_CustomerInfo.CustomerID =3D tbl_CustomerAnswers.CustomerID=20
WHERE tbl_CustomerInfo.Approved =3D '1' AND @Stuff2
Order By tbl_CustomerInfo.SubScriptionTypeID, BirthDate DESC

Any help would be greatly appreciated.

Thank you,

John

Reply to this message...

Dot Net Guru Serdar

WHERE tbl_CustomerInfo.Approved = '1' AND tbl_CustomerInfo.Approved =
@Stuff2

-----Original Message-----
From: Administrator [mailto:Click here to reveal e-mail address]
Sent: Wednesday, July 24, 2002 5:53 AM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] Dynamic WHERE Clause

Hello All,

I am trying to develop a stored procedure that has a dynamic WHERE clause.
Here is what I have currently, but it isn't working. The @Thing1 variable
is a WHERE String that I develop on the fly on the website. The error I am
getting is that the is a problem near the 'as' by @Thing1

CREATE PROCEDURE sp_GetAsianMatches
@Thing1 as VarChar(2000)

AS
Declare @Stuff2 VarChar(2000)

Set @Stuff2 = @Thing1

SELECT tbl_CustomerInfo.CustomerID, (ROUND(DATEDIFF(dd,
tbl_CustomerAnswers.BirthDate, GETDATE()) / 365, 0) as Age,
tbl_CustomerInfo.CustomerFirstName, tbl_CustomerInfo.MainImage,
tbl_CustomerInfo.SubscriptionTypeID
FROM tbl_CustomerInfo LEFT OUTER JOIN tbl_State ON tbl_CustomerInfo.State =
tbl_State.StateID LEFT OUTER JOIN tbl_Countries ON tbl_CustomerInfo.Country
= tbl_Countries.CountryID LEFT OUTER JOIN tbl_CustomerSpokenLanguageAnswers
RIGHT OUTER JOIN tbl_CustomerAnswers ON
tbl_CustomerSpokenLanguageAnswers.CustomerAnswerID =
tbl_CustomerAnswers.tbl_CustomerAnswersID LEFT OUTER JOIN
tbl_CustomerLookingForChoices ON tbl_CustomerAnswers.tbl_CustomerAnswersID =
tbl_CustomerLookingForChoices.CustomerAnswersID ON
tbl_CustomerInfo.CustomerID = tbl_CustomerAnswers.CustomerID
WHERE tbl_CustomerInfo.Approved = '1' AND @Stuff2
Order By tbl_CustomerInfo.SubScriptionTypeID, BirthDate DESC

Any help would be greatly appreciated.

Thank you,

John

| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

Reply to this message...

Dot Net Guru Serdar

actually that should be
WHERE tbl_CustomerInfo.Approved = '1' OR tbl_CustomerInfo.Approved =
@Stuff2

matt

-----Original Message-----
From: Dot Net Guru Serdar
Sent: Wednesday, July 24, 2002 12:11 PM
To: 'ngfx-sqlclient'
Subject: RE: [ngfx-sqlclient] Dynamic WHERE Clause

WHERE tbl_CustomerInfo.Approved = '1' AND tbl_CustomerInfo.Approved =
@Stuff2

-----Original Message-----
From: Administrator [mailto:Click here to reveal e-mail address]
Sent: Wednesday, July 24, 2002 5:53 AM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] Dynamic WHERE Clause

Hello All,

I am trying to develop a stored procedure that has a dynamic WHERE clause.
Here is what I have currently, but it isn't working. The @Thing1 variable
is a WHERE String that I develop on the fly on the website. The error I am
getting is that the is a problem near the 'as' by @Thing1

CREATE PROCEDURE sp_GetAsianMatches
@Thing1 as VarChar(2000)

AS
Declare @Stuff2 VarChar(2000)

Set @Stuff2 = @Thing1

SELECT tbl_CustomerInfo.CustomerID, (ROUND(DATEDIFF(dd,
tbl_CustomerAnswers.BirthDate, GETDATE()) / 365, 0) as Age,
tbl_CustomerInfo.CustomerFirstName, tbl_CustomerInfo.MainImage,
tbl_CustomerInfo.SubscriptionTypeID
FROM tbl_CustomerInfo LEFT OUTER JOIN tbl_State ON tbl_CustomerInfo.State =
tbl_State.StateID LEFT OUTER JOIN tbl_Countries ON tbl_CustomerInfo.Country
= tbl_Countries.CountryID LEFT OUTER JOIN tbl_CustomerSpokenLanguageAnswers
RIGHT OUTER JOIN tbl_CustomerAnswers ON
tbl_CustomerSpokenLanguageAnswers.CustomerAnswerID =
tbl_CustomerAnswers.tbl_CustomerAnswersID LEFT OUTER JOIN
tbl_CustomerLookingForChoices ON tbl_CustomerAnswers.tbl_CustomerAnswersID =
tbl_CustomerLookingForChoices.CustomerAnswersID ON
tbl_CustomerInfo.CustomerID = tbl_CustomerAnswers.CustomerID
WHERE tbl_CustomerInfo.Approved = '1' AND @Stuff2
Order By tbl_CustomerInfo.SubScriptionTypeID, BirthDate DESC

Any help would be greatly appreciated.

Thank you,

John

| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

Reply to this message...

James Avery

In Stored Procedures you do not use as after the variable. It should be
like this: @Thing1 VarChar(200)

-----Original Message-----
From: Administrator [mailto:Click here to reveal e-mail address]
Sent: Wednesday, July 24, 2002 8:53 AM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] Dynamic WHERE Clause

Hello All,

I am trying to develop a stored procedure that has a dynamic WHERE
clause. Here is what I have currently, but it isn't working. The
@Thing1 variable is a WHERE String that I develop on the fly on the
website. The error I am getting is that the is a problem near the 'as'
by @Thing1

CREATE PROCEDURE sp_GetAsianMatches
@Thing1 as VarChar(2000)

AS
Declare @Stuff2 VarChar(2000)

Set @Stuff2 = @Thing1

SELECT tbl_CustomerInfo.CustomerID, (ROUND(DATEDIFF(dd,
tbl_CustomerAnswers.BirthDate, GETDATE()) / 365, 0) as Age,
tbl_CustomerInfo.CustomerFirstName, tbl_CustomerInfo.MainImage,
tbl_CustomerInfo.SubscriptionTypeID
FROM tbl_CustomerInfo LEFT OUTER JOIN tbl_State ON
tbl_CustomerInfo.State = tbl_State.StateID LEFT OUTER JOIN tbl_Countries
ON tbl_CustomerInfo.Country = tbl_Countries.CountryID LEFT OUTER JOIN
tbl_CustomerSpokenLanguageAnswers RIGHT OUTER JOIN tbl_CustomerAnswers
ON tbl_CustomerSpokenLanguageAnswers.CustomerAnswerID =
tbl_CustomerAnswers.tbl_CustomerAnswersID LEFT OUTER JOIN
tbl_CustomerLookingForChoices ON
tbl_CustomerAnswers.tbl_CustomerAnswersID =
tbl_CustomerLookingForChoices.CustomerAnswersID ON
tbl_CustomerInfo.CustomerID = tbl_CustomerAnswers.CustomerID
WHERE tbl_CustomerInfo.Approved = '1' AND @Stuff2
Order By tbl_CustomerInfo.SubScriptionTypeID, BirthDate DESC

Any help would be greatly appreciated.

Thank you,

John

| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

Reply to this message...

Minh Truong

[Original message clipped]

variable is a WHERE String that I develop on the fly on the website. The
error I am getting is that the is a problem near the 'as' by @Thing1
[Original message clipped]

Don't you need parentheses around parameters? ... ie..

CREATE PROCEDURE sp_Whatever
(
@P1 AS int
)

AS

...

Reply to this message...

Administrator

Hello All,

After a lot of research and trial and error, I found the solution:

CREATE PROCEDURE sp_GetAsianMatches
@Thing1 as VarChar(8000)

AS
Declare @Query Varchar(8000)

Select @Query =3D 'SELECT tbl_CustomerInfo.CustomerFirstName, =
tbl_CustomerInfo.City, tbl_State.State, tbl_CustomerInfo.Province, =
tbl_Countries.Country,=20
tbl_CustomerInfo.Approved, =
tbl_CustomerInfo.MainImage, tbl_CustomerInfo.UserLevel, =
tbl_CustomerAnswers.BirthDate,=20
tbl_CustomerInfo.CustomerID, ROUND(DATEDIFF(dd, =
tbl_CustomerAnswers.BirthDate, GETDATE()) / 365, 0) AS Age
FROM tbl_CustomerInfo LEFT OUTER JOIN
tbl_CustomerAnswers ON tbl_CustomerInfo.CustomerID =
=3D tbl_CustomerAnswers.CustomerID LEFT OUTER JOIN
tbl_Countries ON tbl_CustomerInfo.Country =3D =
tbl_Countries.CountryID LEFT OUTER JOIN
tbl_State ON tbl_CustomerInfo.State =3D =
tbl_State.StateID
WHERE tbl_CustomerInfo.Approved =3D 1 AND ' + @Thing1=20
+ 'Order By tbl_CustomerAnswers.BirthDate DESC'

Exec(@Query)
GO

This gives me the ability to pass in any WHERE clause I wish. I hope =
this helps others.

John

-----Original Message-----
From: James Avery [mailto:Click here to reveal e-mail address]
Sent: Wednesday, July 24, 2002 12:15 PM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] RE: Dynamic WHERE Clause

In Stored Procedures you do not use as after the variable. It should be
like this: @Thing1 VarChar(200)

-----Original Message-----
From: Administrator [mailto:Click here to reveal e-mail address]=20
Sent: Wednesday, July 24, 2002 8:53 AM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] Dynamic WHERE Clause

Hello All,

I am trying to develop a stored procedure that has a dynamic WHERE
clause. Here is what I have currently, but it isn't working. The
@Thing1 variable is a WHERE String that I develop on the fly on the
website. The error I am getting is that the is a problem near the 'as'
by @Thing1

CREATE PROCEDURE sp_GetAsianMatches
@Thing1 as VarChar(2000)

AS
Declare @Stuff2 VarChar(2000)

Set @Stuff2 =3D @Thing1

SELECT tbl_CustomerInfo.CustomerID, (ROUND(DATEDIFF(dd,
tbl_CustomerAnswers.BirthDate, GETDATE()) / 365, 0) as Age,
tbl_CustomerInfo.CustomerFirstName, tbl_CustomerInfo.MainImage,
tbl_CustomerInfo.SubscriptionTypeID=20
FROM tbl_CustomerInfo LEFT OUTER JOIN tbl_State ON
tbl_CustomerInfo.State =3D tbl_State.StateID LEFT OUTER JOIN =
tbl_Countries
ON tbl_CustomerInfo.Country =3D tbl_Countries.CountryID LEFT OUTER JOIN
tbl_CustomerSpokenLanguageAnswers RIGHT OUTER JOIN tbl_CustomerAnswers
ON tbl_CustomerSpokenLanguageAnswers.CustomerAnswerID =3D
tbl_CustomerAnswers.tbl_CustomerAnswersID LEFT OUTER JOIN
tbl_CustomerLookingForChoices ON
tbl_CustomerAnswers.tbl_CustomerAnswersID =3D
tbl_CustomerLookingForChoices.CustomerAnswersID ON
tbl_CustomerInfo.CustomerID =3D tbl_CustomerAnswers.CustomerID=20
WHERE tbl_CustomerInfo.Approved =3D '1' AND @Stuff2
Order By tbl_CustomerInfo.SubScriptionTypeID, BirthDate DESC

Any help would be greatly appreciated.

Thank you,

John

| [ngfx-sqlclient] member Click here to reveal e-mail address =3D YOUR ID=20
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp =3D JOIN/QUIT

| [ngfx-sqlclient] member Click here to reveal e-mail address =3D YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp =3D JOIN/QUIT

Reply to this message...

David L. Penton

Couple of things to think about:

1) A varchar can only be 8000 characters. So, if @Thing1 was 8000
characters, you are going to get a right truncated query [because there
would be no where to store it]. Not too cool.

2) Sql Injection. What if @Thing1 was equal to:

0=1; DECLARE @d sysname,@q varchar(100); SELECT @d=db_name(), @q='USE
master; DROP DATABASE '+QUOTENAME(@q); EXEC(@q) --

and you happened to be using an account that had the privilieges to do this?
I can write a UNION ALL query easily on top of this. Only a matter of trial
and error.

David L. Penton, Microsoft MVP
JCPenney Technical Specialist / Lead
"Mathematics is music for the mind, and Music is Mathematics for the
Soul. - J.S. Bach"
Click here to reveal e-mail address

Do you have the VBScript Docs or SQL BOL installed? If not, why not?
VBScript Docs: http://www.davidpenton.com/vbscript
SQL BOL: http://www.davidpenton.com/sqlbol

-----Original Message-----
From: Administrator [mailto:Click here to reveal e-mail address]
Sent: Wednesday, July 24, 2002 7:18 PM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] RE: Dynamic WHERE Clause

Hello All,

After a lot of research and trial and error, I found the solution:

CREATE PROCEDURE sp_GetAsianMatches
@Thing1 as VarChar(8000)

AS
Declare @Query Varchar(8000)

Select @Query = 'SELECT tbl_CustomerInfo.CustomerFirstName,
tbl_CustomerInfo.City, tbl_State.State, tbl_CustomerInfo.Province,
tbl_Countries.Country,
tbl_CustomerInfo.Approved, tbl_CustomerInfo.MainImage,
tbl_CustomerInfo.UserLevel, tbl_CustomerAnswers.BirthDate,
tbl_CustomerInfo.CustomerID, ROUND(DATEDIFF(dd,
tbl_CustomerAnswers.BirthDate, GETDATE()) / 365, 0) AS Age
FROM tbl_CustomerInfo LEFT OUTER JOIN
tbl_CustomerAnswers ON tbl_CustomerInfo.CustomerID =
tbl_CustomerAnswers.CustomerID LEFT OUTER JOIN
tbl_Countries ON tbl_CustomerInfo.Country =
tbl_Countries.CountryID LEFT OUTER JOIN
tbl_State ON tbl_CustomerInfo.State =
tbl_State.StateID
WHERE tbl_CustomerInfo.Approved = 1 AND ' + @Thing1
+ 'Order By tbl_CustomerAnswers.BirthDate DESC'

Exec(@Query)
GO

This gives me the ability to pass in any WHERE clause I wish. I hope this
helps others.

John

-----Original Message-----
From: James Avery [mailto:Click here to reveal e-mail address]
Sent: Wednesday, July 24, 2002 12:15 PM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] RE: Dynamic WHERE Clause

In Stored Procedures you do not use as after the variable. It should be
like this: @Thing1 VarChar(200)

-----Original Message-----
From: Administrator [mailto:Click here to reveal e-mail address]
Sent: Wednesday, July 24, 2002 8:53 AM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] Dynamic WHERE Clause

Hello All,

I am trying to develop a stored procedure that has a dynamic WHERE
clause. Here is what I have currently, but it isn't working. The
@Thing1 variable is a WHERE String that I develop on the fly on the
website. The error I am getting is that the is a problem near the 'as'
by @Thing1

CREATE PROCEDURE sp_GetAsianMatches
@Thing1 as VarChar(2000)

AS
Declare @Stuff2 VarChar(2000)

Set @Stuff2 = @Thing1

SELECT tbl_CustomerInfo.CustomerID, (ROUND(DATEDIFF(dd,
tbl_CustomerAnswers.BirthDate, GETDATE()) / 365, 0) as Age,
tbl_CustomerInfo.CustomerFirstName, tbl_CustomerInfo.MainImage,
tbl_CustomerInfo.SubscriptionTypeID
FROM tbl_CustomerInfo LEFT OUTER JOIN tbl_State ON
tbl_CustomerInfo.State = tbl_State.StateID LEFT OUTER JOIN tbl_Countries
ON tbl_CustomerInfo.Country = tbl_Countries.CountryID LEFT OUTER JOIN
tbl_CustomerSpokenLanguageAnswers RIGHT OUTER JOIN tbl_CustomerAnswers
ON tbl_CustomerSpokenLanguageAnswers.CustomerAnswerID =
tbl_CustomerAnswers.tbl_CustomerAnswersID LEFT OUTER JOIN
tbl_CustomerLookingForChoices ON
tbl_CustomerAnswers.tbl_CustomerAnswersID =
tbl_CustomerLookingForChoices.CustomerAnswersID ON
tbl_CustomerInfo.CustomerID = tbl_CustomerAnswers.CustomerID
WHERE tbl_CustomerInfo.Approved = '1' AND @Stuff2
Order By tbl_CustomerInfo.SubScriptionTypeID, BirthDate DESC

Any help would be greatly appreciated.

Thank you,

John

Reply to this message...

ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification

Need Dot Net Interview Questions?
Ask ExamGuru, Inc. for advice and help on Passing .Net Interviews

.Net Projects
Best-of-breed application framework for .NET projects, developed by ExamGuru, Inc. and ExamGuru IT

Free .net Help
Commission ExamGuru, Inc. and his team for your next bespoke software project

FogBUGZ
The only bug tracking system carefully crafted with one goal in mind: helping teams create great software.

Awesome Tools
If you don't know about these, you're missing out... IT Certification Questions
IT Interview Questions
Free Oracle 10g Training
MCSE Boortcamp
Cisco Study Guides
Cheap Study Guides
Exact Questions
Dot Net Interview Questions
Oracle OCP
Cheap Travel
Designer Perfumes - Wholesale Prices
Free Programming Tutorials

	Copyright © ExamGuru, Inc. 2001-2006
	Contact Us - Terms of Use - Privacy Policy - www.dot-net-guru.com - www.examguru.net - www.oraclesource.net - www.itinterviews.net - www.examguru.net/ITCertification