"Uri Dimant" <u***@iscar.co.il> wrote in message
news:eIoWNt8%23FHA.1288@TK2MSFTNGP09.phx.gbl...
> Daren
> Let me understand you
> you have a statement as
>
>> EXEC @PostcodeRange = dbo.System_GetSystemParameter 'PostcodeRange'
>
> 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" <newsgro***@webdesignmagic.com.au> wrote in message
> news:%23dAp8T8%23FHA.2812@TK2MSFTNGP09.phx.gbl...
>> Cheers,
>> But  I still get back 3 recordset results.  I just need to set a variable
>> without the aditional recordsets
>>
>> Thx
>>
>> "Uri Dimant" <u***@iscar.co.il> wrote in message
>> news:u7XR8A8%23FHA.1312@TK2MSFTNGP09.phx.gbl...
>>> Daren
>>> Take a look at sp_executesql stored procedure in the BOL rather than
>>> using your SP's
>>>
>>>
>>> USE pubs
>>>
>>> DECLARE @RowCount int
>>>
>>> EXEC sp_executesql
>>>    N'SELECT @RowCount = COUNT(*) FROM authors',
>>>     N'@RowCount int OUTPUT',
>>>     @RowCount OUTPUT
>>>
>>> SELECT @RowCount
>>>
>>>
>>>
>>>
>>>
>>> "Daren Hawes" <newsgro***@webdesignmagic.com.au> wrote in message
>>> news:OslpEz7%23FHA.2524@TK2MSFTNGP10.phx.gbl...
>>>> Hi ,
>>>>
>>>> I have a Stored procedure that dynamically creates a select statement.
>>>>
>>>> I keep getting 3 result sets and I only want one from the SELECT.
>>>>
>>>> The 2 unwanted ones come from 2 stored procedures that set variables
>>>> that are required in the SELECT.
>>>>
>>>> They are
>>>>
>>>> EXEC @PostcodeRange = dbo.System_GetSystemParameter 'PostcodeRange'
>>>> EXEC @ThisSuburbsPostcode = dbo.Regional_GetPostcodeFromSuburb @ES
>>>>
>>>> How can I stop these 2 SP from outputting to the result set but still
>>>> populate @PostcodeRange and @ThisSuburbsPostcode?
>>>>
>>>> All I want is one result set from the SELECT.  Below is a copy of all
>>>> the SP included.
>>>>
>>>> Any help would be aprechiated.
>>>>
>>>> Thanks Daren
>>>>
>>>> -------------------------------------------------------------------------
>>>>
>>>> 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
>>>>
>>>> DECLARE @SQLWhere varChar(1000)
>>>> DECLARE @SQLOrder varChar(200)
>>>> DECLARE @PostcodeRange int
>>>> DECLARE @ThisSuburbsPostcode int
>>>>
>>>> EXEC @PostcodeRange = dbo.System_GetSystemParameter 'PostcodeRange'
>>>> EXEC @ThisSuburbsPostcode = dbo.Regional_GetPostcodeFromSuburb @ES
>>>>
>>>> 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.WeeklyRent 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 @SQLWhere
>>>> Print @SQLOrder
>>>>
>>>> EXECUTE ('SELECT * from viewDisplayPropertyResults WHERE ' + @SQLWhere
>>>> + @SQLOrder)
>>>> GO
>>>>
>>>>
>>>>
>>>> -------------------------------------------------------------------------
>>>> CREATE Procedure dbo.Regional_GetPostcodeFromSuburb
>>>>
>>>> @SuburbID int OUTPUT
>>>>
>>>> As
>>>>
>>>> Select SuburbPostcode From dbo.Suburbs where SuburbID = @SuburbID
>>>> GO
>>>> -------------------------------------------------------------------------
>>>>
>>>> Create Procedure dbo.System_GetSystemParameter
>>>>
>>>> @SystemParameterTitle VarChar(25)
>>>>
>>>> As
>>>>
>>>> DECLARE @SQL varchar(250)
>>>>
>>>> Set @SQL = 'Select Top 1 ' + @SystemParameterTitle + ' From
>>>> dbo.SystemParameters'
>>>>
>>>> EXEC (@SQL)
>>>>
>>>>
>>>> GO
>>>>
>>>>
>>>>
>>>> -------------------------------------------------------------------------
>>>>
>>>>
>>>
>>>
>>
>>
>
>