Return value using exec(@query)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Return value using exec(@query)

Author

Topic

fshuja
Starting Member

7 Posts

Posted - 2007-11-15 : 00:44:55

i have a stored procedure like this

.......................................................
ALTER PROCEDURE dbo.SP_NewClassifiedsMain

@PageIndex INT,
@NumRows INT,
@NewClassifiedsCount INT OUTPUT,
@WhereConditions varchar(200)

AS
BEGIN

declare @sql nvarchar(400);
select @sql = ' declare @abc int output
Select @abc = Select Count(*) From classifieds_Ads
Where AdStatus=100 ' + @WhereConditions

exec sp_executesql @sql, N'@NewClassifiedsCount output'

Declare @startRowIndex INT;
Set @startRowIndex = (@PageIndex * @NumRows) + 1;
Declare @endRowIndex INT;
Set @endRowIndex = @startRowIndex+@NumRows-1;

EXECUTE('
With NewClassifieds as (
Select ROW_NUMBER() OVER (Order By DateCreated DESC) as
Row, Id, PreviewImageId, Title, DateCreated
From
classifieds_Ads
Where
AdStatus=100 ' + @WhereConditions + ')

Select
Id, PreviewImageId, Title, DateCreated
From
NewClassifieds
Where
Row between ' + @startRowIndex + ' And ' + @endRowIndex
)

END
.......................................................

Everything is find but i am not able to get the NewClassifiedsCount. its always returning 0.
please help me out

thnx

georgev
Posting Yak Master

122 Posts

Posted - 2007-11-15 : 04:58:54

I don't like the look of the above code...
What are you specifically trying to achieve from using a Sproc?
Post an example of the SQL you would use to produce a single result...

fshuja
Starting Member

7 Posts

Posted - 2007-11-15 : 05:10:45

i just want to add a where condition at runtime. like it could be like that
Where a = 10
Where a = 10 AND b = 20
Where a = 10 AND b = 20 AND c = 30
and so on.
thats y i want dynamic where.

georgev
Posting Yak Master

122 Posts

Posted - 2007-11-15 : 05:17:45

And you want to return the count of the number of rows returned?

fshuja
Starting Member

7 Posts

Posted - 2007-11-15 : 07:53:58

i want count of returns based on that where condition.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources