MBR IT/.NET 247 : Dynamic WHERE Clause on ASPFriends.com 'ngfx-sqlclient' list

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...

Matt 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...

Matt Serdar

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

matt

-----Original Message-----
From: Matt 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...

MBR BootFX
Best-of-breed application framework for .NET projects, developed by Matthew Baxter-Reynolds and MBR IT

	Copyright © Matthew Baxter-Reynolds 2001-2008. '.NET 247 Software Development Services' is a trading style of MBR IT Solutions Ltd.
	Contact Us - Terms of Use - Privacy Policy - www.dotnet247.com