Help building dynamic stored procedure

[ossie]

Hi, I'm in the fianl stages of building a dating app for a client and need a
little help constructing a stored procedure for an advanced search.

I've been googling for hours to try and find the right answer but seem to be
more frustrated than when I started, hers's my scenario

Here's what I have so far

ALTER PROCEDURE dbo.TEST_ADVANCED_SEARCH
(@categoryID int = 0,
@Age varchar(100) = NULL,
@locationID int = 0)
AS
DECLARE @SQL Varchar (4000)
SELECT @SQL = 'dbo.user_accounts.profileComplete,
dbo.user_accounts.categoryID, dbo.user_profiles.locationID,
dbo.user_profiles.Age
FROM dbo.user_accounts INNER JOIN
dbo.user_profiles ON dbo.user_accounts.userID =
dbo.user_profiles.userID
WHERE (dbo.user_accounts.profileComplete = 1)'

IF @categoryID <> 0
SELECT @SQL = @SQL + ' AND (dbo.user_accounts.categoryID = @categoryID)'

IF @MAXAGE IS NOT NULL
SELECT @SQL = @SQL + ' AND (dbo.user_profiles.Age <= @MAXAge) '

IF @LocationID <> 0
SELECT @SQL = @SQL + ' AND (dbo.user_profiles.locationID = @locationID)'

EXEC(@SQL)
Once I have this running I have a few more fields to add

Running this query from access .adp file produces an error "Invalid SQL
Statement - Check the filter on the form record source"
I think I'm nearly there but just need a not so gentle push in the right
direction,
Thanks

[ossie]

Ignore the @Age / @MAXAGE Discrepancy,



"ossie" <fdsg@sdgf.com> wrote in message
news:d1q9q0$2vm$1@forums.macromedia.com...

> Hi, I'm in the fianl stages of building a dating app for a client and need
> a little help constructing a stored procedure for an advanced search.
>
> I've been googling for hours to try and find the right answer but seem to
> be more frustrated than when I started, hers's my scenario
>
> Here's what I have so far
>
> ALTER PROCEDURE dbo.TEST_ADVANCED_SEARCH
> (@categoryID int = 0,
> @Age varchar(100) = NULL,
> @locationID int = 0)
> AS
> DECLARE @SQL Varchar (4000)
> SELECT @SQL = 'dbo.user_accounts.profileComplete,
> dbo.user_accounts.categoryID, dbo.user_profiles.locationID,
> dbo.user_profiles.Age
> FROM dbo.user_accounts INNER JOIN
> dbo.user_profiles ON dbo.user_accounts.userID =
> dbo.user_profiles.userID
> WHERE (dbo.user_accounts.profileComplete = 1)'
>
> IF @categoryID <> 0
> SELECT @SQL = @SQL + ' AND (dbo.user_accounts.categoryID = @categoryID)'
>
> IF @MAXAGE IS NOT NULL
> SELECT @SQL = @SQL + ' AND (dbo.user_profiles.Age <= @MAXAge) '
>
> IF @LocationID <> 0
> SELECT @SQL = @SQL + ' AND (dbo.user_profiles.locationID = @locationID)'
>
> EXEC(@SQL)
> Once I have this running I have a few more fields to add
>
> Running this query from access .adp file produces an error "Invalid SQL
> Statement - Check the filter on the form record source"
> I think I'm nearly there but just need a not so gentle push in the right
> direction,
> Thanks
>

[BillB]

Is this for CF??

if so try this little toy. [url]http://www.switch-box.org/[/url]


--
Bill
Real Estate Web Sites Made Easy
[url]www.DataPacks.com[/url]


"ossie" <fdsg@sdgf.com> wrote in message
news:d1q9q0$2vm$1@forums.macromedia.com...

> Hi, I'm in the fianl stages of building a dating app for a client and need
> a little help constructing a stored procedure for an advanced search.
>
> I've been googling for hours to try and find the right answer but seem to
> be more frustrated than when I started, hers's my scenario
>
> Here's what I have so far
>
> ALTER PROCEDURE dbo.TEST_ADVANCED_SEARCH
> (@categoryID int = 0,
> @Age varchar(100) = NULL,
> @locationID int = 0)
> AS
> DECLARE @SQL Varchar (4000)
> SELECT @SQL = 'dbo.user_accounts.profileComplete,
> dbo.user_accounts.categoryID, dbo.user_profiles.locationID,
> dbo.user_profiles.Age
> FROM dbo.user_accounts INNER JOIN
> dbo.user_profiles ON dbo.user_accounts.userID =
> dbo.user_profiles.userID
> WHERE (dbo.user_accounts.profileComplete = 1)'
>
> IF @categoryID <> 0
> SELECT @SQL = @SQL + ' AND (dbo.user_accounts.categoryID = @categoryID)'
>
> IF @MAXAGE IS NOT NULL
> SELECT @SQL = @SQL + ' AND (dbo.user_profiles.Age <= @MAXAge) '
>
> IF @LocationID <> 0
> SELECT @SQL = @SQL + ' AND (dbo.user_profiles.locationID = @locationID)'
>
> EXEC(@SQL)
> Once I have this running I have a few more fields to add
>
> Running this query from access .adp file produces an error "Invalid SQL
> Statement - Check the filter on the form record source"
> I think I'm nearly there but just need a not so gentle push in the right
> direction,
> Thanks
>

[Julian Roberts]

Try logic like

IF @categoryID <> 0
SELECT @SQL = @SQL + ' AND (dbo.user_accounts.categoryID =' +
cast(@categoryID as nvarchar) + ')'


--
Jules
[url]http://www.charon.co.uk/charoncart[/url]
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004

[ossie]

Thanks for the replys
I seem to be getting closer, now I get a message saying I must declare the
variable @categoryID - only when I enter something in the categoryID box, it
asks for age if there is something in the age box
fyi site is asp.net VB
Any more pointers?











"Julian Roberts" <newsg@charon.co.uk> wrote in message
news:d1qe1r$8ln$1@forums.macromedia.com...

> Try logic like
>
> IF @categoryID <> 0
> SELECT @SQL = @SQL + ' AND (dbo.user_accounts.categoryID =' +
> cast(@categoryID as nvarchar) + ')'
>
>
> --
> Jules
> [url]http://www.charon.co.uk/charoncart[/url]
> Charon Cart 3
> Shopping Cart Extension for Dreamweaver MX/MX 2004
>
>
>
>