Reposted - Troubles setting variables

Yes. I need to set some variables from other queries in my SP that will be
used in the final Select statement. This is the only one I need returned.
I have modified the script as per other posts, but it still brings back 3
recordsets.
Here is the current version..
CREATE PROCEDURE dbo.Property_DisplayResults
(
@.ES varChar(10),
@.SS varChar(50),
@.PT varChar(10),
@.F varChar(10),
@.T varChar(10),
@.RF varChar(10),
@.RT varChar(10),
@.BM varChar(10),
@.BMX varChar(10),
@.CS varChar(10),
@.MLS varChar(10),
@.S varChar(10),
@.RM varChar(10),
@.MR varChar(10),
@.RSD varChar(50),
@.UserID varChar(10),
@.StateID varChar(10)
)
AS
SET NOCOUNT ON
DECLARE @.SQLWhere varChar(1000)
DECLARE @.SQLOrder varChar(200)
DECLARE @.PostcodeRange int
DECLARE @.ThisSuburbsPostcode int
EXEC sp_executesql
N'Select Top 1 PostcodeRange from dbo.SystemParameters',
N'@.PostcodeRange int OUTPUT',
@.PostcodeRange OUTPUT
SET NOCOUNT ON
DECLARE @.SQLCODE Nvarchar(500)
Set @.SQLCODE = 'Select SuburbPostcode From dbo.Suburbs where SuburbID = ' +
@.ES
If @.ES <> ''
EXEC sp_executesql
@.SQLCODE,
N'@.ThisSuburbsPostcode int OUTPUT',
@.ThisSuburbsPostcode OUTPUT
Set @.SQLWhere = '(dbo.viewDisplayPropertyResults.PropertyStatus = 4)'
IF @.ES <> ''
SET @.SQLWhere = @.SQLWhere + ' AND (dbo.viewDisplayPropertyResults.SuburbID =
' + @.ES + ')'
IF @.SS <> ''
SET @.SQLWhere = @.SQLWhere + ' AND (dbo.viewDisplayPropertyResults.StateID =
' + @.StateID + ' AND dbo.viewDisplayPropertyResults.SuburbID Between ' +
(@.ThisSuburbsPostcode - @.PostcodeRange) + ' AND ' + (@.ThisSuburbsPostcode +
@.PostcodeRange) + ')'
IF @.PT <> ''
SET @.SQLWhere = @.SQLWhere + ' AND
(dbo.viewDisplayPropertyResults.ListingTypeID = ''' + @.PT + ''')'
IF (@.F <> '' and @.T <> '')
SET @.SQLWhere = @.SQLWhere + ' AND (dbo.viewDisplayPropertyResults.SalePrice
Between ' + @.F + ' and ' + @.T + ')'
IF (@.RF <> '' and @.RT <> '')
SET @.SQLWhere = @.SQLWhere + ' AND (dbo.viewDisplayPropertyResults.WlyRent
Between ' + @.RF + ' and ' + @.RT + ')'
IF (@.BM <> '' and @.BMX <> '')
SET @.SQLWhere = @.SQLWhere + ' AND (dbo.viewDisplayPropertyResults.NoBedrooms
Between ' + @.BM + ' and ' + @.BMX + ')'
IF @.MR <> ''
SET @.SQLWhere = @.SQLWhere + ' AND
(dbo.viewDisplayPropertyResults.MinimumReturns => '+ @.MR + ')'
IF @.RM <> ''
SET @.SQLWhere = @.SQLWhere + ' AND (dbo.viewDisplayPropertyResults.RegionID =
'+ @.RM + ')'
IF @.RSD <> ''
SET @.SQLWhere = @.SQLWhere + ' AND
(dbo.viewDisplayPropertyResults.ForRentSaleOrDeveloper = ' + @.RSD + ')'
IF @.UserID <> ''
SET @.SQLWhere = @.SQLWhere + ' AND (dbo.viewDisplayPropertyResults.UserID =
'+ @.UserID + ')'
IF @.S <> ''
SET @.SQLOrder = 'ORDER BY ' + @.S
Print @.PostcodeRange
Print @.ThisSuburbsPostcode
Print @.SQLWhere
Print @.SQLOrder
EXECUTE ('SELECT * from viewDisplayPropertyResults WHERE ' + @.SQLWhere +
@.SQLOrder)
GO
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eIoWNt8%23FHA.1288@.TK2MSFTNGP09.phx.gbl...
> Daren
> Let me understand you
> you have a statement as
>
> Now you are saying , you'd like to have a variable @.PostcodeRange
> assigned to 'something' without running dbo.System_GetSystemParameter
> 'PostcodeRange' store procedure. Am I right?
>
>
> "Daren Hawes" <newsgroups@.webdesignmagic.com.au> wrote in message
> news:%23dAp8T8%23FHA.2812@.TK2MSFTNGP09.phx.gbl...
>On Fri, 9 Dec 2005 19:31:44 +1000, Daren Hawes wrote:

>Yes. I need to set some variables from other queries in my SP that will be
>used in the final Select statement. This is the only one I need returned.
>I have modified the script as per other posts, but it still brings back 3
>recordsets.
>Here is the current version..
(snip)
Hi Daren,
I just replied to this question in the original thread.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)