November 14, 2007 at 12:10 pm
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
November 14, 2007 at 12:39 pm
Here is an example of what you need to do:
declare @sql nvarchar(400)
declare @test-2 int
set @sql = 'select @test-2 = count(*) from sysobjects'
exec sp_executesql @sql, N'@test int output', @test-2 output
select @test-2
You declare your variable outside of your dynamic SQL, then you have to include the variable in your parameter list.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
November 14, 2007 at 10:34 pm
Still not working. @NewClassifiedsCount is 0 always
..............................................................
ALTER PROCEDURE dbo.SP_NewClassifiedsMain
@PageIndex INT,
@NumRows INT,
@NewClassifiedsCount INT OUTPUT,
@WhereConditions varchar(200)
AS
BEGIN
declare @sql nvarchar(400);
declare @test-2 int;
set @sql = 'Select @test-2 = Count(*) From classifieds_Ads
Where AdStatus=100 ' + @WhereConditions
exec sp_executesql @sql, N'@test int output', @test-2 output
select @test-2;
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
..............................................................
November 14, 2007 at 11:54 pm
Faraz Shuja (11/14/2007)
Still not working. @NewClassifiedsCount is 0 always
Try this
declare @sql nvarchar(400);
declare @test-2 int;
set @sql = 'Select @count = Count(*) From classifieds_Ads
Where AdStatus=100 ' + @WhereConditions
exec sp_executesql @sql, N'@count int output', @count = @test-2 output
select @test-2;
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 15, 2007 at 12:09 am
still not working. return @NewClassifiedsCount = 0
November 15, 2007 at 12:19 am
Edit: Ah, silly me. The output variable's not getting assigned.
ALTER PROCEDURE dbo.SP_NewClassifiedsMain
@PageIndex INT,
@NumRows INT,
@NewClassifiedsCount INT OUTPUT,
@WhereConditions varchar(200)
AS
BEGIN
declare @sql nvarchar(400);
set @sql = 'Select @count = Count(*) From classifieds_Ads
Where AdStatus=100 ' + @WhereConditions
exec sp_executesql @sql, N'@count int output', @count = @NewClassifiedsCount output
....
Edit again: Third time's the charm.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 15, 2007 at 12:32 am
yes u r right its working but when i add @WhereConditions then it does not work.
.............................
declare @sql nvarchar(400);
declare @test-2 int;
set @sql = 'Select @count = Count(*) From classifieds_Ads
Where AdStatus=100 ' + @WhereConditions;
exec sp_executesql @sql, N'@count int output', @count = @test-2 output
select @test-2
select @NewClassifiedsCount = @test-2
....................................................
November 15, 2007 at 12:40 am
See my revised reply. Also, make sure that the where conditions you're specifying don't filter out all records.
Can you give an example of how you're calling the proc please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 15, 2007 at 12:41 am
i am just passing null to @WhereConditions.
i am executing the Sp just right-clicking the procedure and execute inside VS2005.
November 15, 2007 at 12:48 am
Faraz Shuja (11/15/2007)
i am just passing null to @WhereConditions.
Yup. That'll do it. If @WhereCondition is NULL, then when concatenated to the rest of the @sql string, that also becomes null, hence the sp_executesql executes nothing.
Change
set @sql = 'Select @count = Count(*) From classifieds_Ads
Where AdStatus=100 ' + @WhereConditions;
to
SET @sql = 'Select @count = Count(*) From classifieds_Ads
Where AdStatus=100 ' + ISNULL (@WhereConditions,'')
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 15, 2007 at 2:36 am
thanks a lot. its working now.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply