July 20, 2008 at 9:48 pm
Hi People. I have a Stored Procedure which for some reason,(probably obvious to all except me) doesn't work!
I have included the text of the SPROC for your perusal.
It's purpose is to return to the front-end app, the number of rows in a SQL2005 table or view, as supplied in the @TableName input parameter. It executes, but only ever returns the value 1 (ie 1 row).
Any help would be much appreciated.
Here's the code: -
ALTER PROCEDURE proc_GetRowCount_Dynamic
(
@TableName varchar(60)='',
@FieldName varchar(50)='',
@WHEREClause varchar(200)='',
@Rows int = NULL output
)
/*Uses parameters to get name of table, name of field to use for counting, and WHERE Clause if applicable.
The Query is assembled from these parameters and the result is returned in output variable, @Rows
!!! IF I CAN EVER GET THIS THING TO WORK !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
*/
AS
DECLARE @Query varchar(300)
IF LEN(@TableName)>0
BEGIN
SET @Query='SELECT COUNT('
IF LEN(@FieldName)>0
BEGIN
SET @Query=@Query+@FieldName+') FROM '+@TableName
END
ELSE
BEGIN
SET @Query=@Query+'*) FROM '+@TableName
END
IF LEN(@WHEREClause)>0
SET @Query=@Query+' WHERE '+@WHEREClause
END
PRINT @Query
EXEC (@Query)
SELECT @Rows=@@ROWCOUNT--NBNBNB This returns wrong number of rows.
--The query executes, but the value of @Rows is only ever = 1
--PRINT 'Number of Rows=='+CAST(@Rows as varchar)
IF @@ERROR=0
RETURN 0
ELSE
RETURN 1
------------------------------------------------------
Regards,
Lester Vincent
Sydney
July 20, 2008 at 9:58 pm
July 20, 2008 at 10:56 pm
Hi Lester Vincent,
Its very simple..
After Executing the stored proc by below parameter:
Exec proc_GetRowCount_Dynamic 'Temp', 'status' , 'status = 1'
your temp query will be like this:
SELECT COUNT(status) FROM Temp WHERE status = 1, which returns the total count of records in one rows. so if you apply "select @@rowcount", the result will become "1" because its returns only one records in count() function. but if you give "Select * from Temp WHERE status = 1" and if it returns 2 records, then @@rowcount will be 2.
its very simple @@rowcount will work on the number of records return on the resultset after executing the query.
hope this helps you.
Cheers!
Sandy.
--
July 20, 2008 at 11:07 pm
Thank you Sandy.
I'll give it a go tonight.
Regards,
Lester Vincent
July 20, 2008 at 11:45 pm
I think you should use sp_executesql as you want to return a parameter from your executed string. Get your @Row (@Row = COUNT(@FieldName)) variable return from the sp_executesql.
Atif Sheikh
July 20, 2008 at 11:54 pm
Thanks Atif. Reckon I'll be able to sort it out from here. Your advice, plus what the other guys have said is most helpful and extremely prompt. I appreciate that.
Kind Regards,
Lester Vincent
Sydney
July 21, 2008 at 12:06 am
try it, and let me know if any concern..
Cheers!
Sandy.
--
July 21, 2008 at 12:19 am
Will do, tonight, Sandy. Again thanks.
Lester Vincent
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply