Stored Procedure Not Working [Resolved]

Posted by Sharpcnet under Sql Server on 11/30/2013 | Points: 10 | Views : 1321 | Status : [Member] | Replies : 4

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Here's a trimmed down version of the original query. Its not giving any output. I am expecting two result sets
1. All records from table
2. Count Of all records from table

declare @rows nvarchar(50), @query nvarchar(max)
set query = 'select @rows+' = count(*) from tblBranch; select * from tblBranch;'
execute sp_executesql @query

The two result sets will later be used in C#.

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Bandi on: 11/30/2013 [Member] [MVP] Platinum | Points: 50

0	DECLARE @Query nvarchar(max) select @Query = 'with temp as (select row_number() over (order by '+ @colname+' '+@OrderByClause+') as row, * from '+@tablename+') select * from temp where row between '+@startIndex+' and '+@EndIndex execute sp_executesql @Query; DECLARE @TotalCountQry NVARCHAR(500); set @TotalCountQry = 'select count() from '+@TableName+' where deleted=0' EXEC(@TotalCountQry) END --execution part execute GetRecordsByPage 'tblBranch','BranchName', '2', '10' NOTE: Here no need to declare OUTPUT param in the parameters list in CREATE PROCEDURE Mark This Response as Answer* -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Sharpcnet, if this helps please login to Mark As Answer. \| Alert Moderator

DECLARE @Query nvarchar(max)



  select @Query = 'with temp as (select row_number() over (order by '+



                   @colname+' '+@OrderByClause+') as row, * from '+@tablename+')



                   select * from temp where row between '+@startIndex+' and '+@EndIndex



   execute sp_executesql @Query;



DECLARE @TotalCountQry NVARCHAR(500);

set @TotalCountQry = 'select count(*) from '+@TableName+' where deleted=0'

EXEC(@TotalCountQry) 

END



--execution part

execute GetRecordsByPage 'tblBranch','BranchName', '2', '10'

NOTE: Here no need to declare OUTPUT param in the parameters list in CREATE PROCEDURE

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 11/30/2013 [Member] [MVP] Platinum | Points: 25

0	Try this too... ? BEGIN DECLARE @Query nvarchar(max) select @Query = 'with temp as (select row_number() over (order by '+ @colname+' '+@OrderByClause+') as row, * from '+@tablename+') select * from temp where row between '+@startIndex+' and '+@EndIndex execute sp_executesql @Query; SELECT @@ROWCOUNT as TotalCount END --execution part execute GetRecordsByPage 'tblBranch','BranchName', '2', '10' Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Sharpcnet, if this helps please login to Mark As Answer. \| Alert Moderator

Try this too... ?


BEGIN
DECLARE @Query nvarchar(max)

  select @Query = 'with temp as (select row_number() over (order by '+



                   @colname+' '+@OrderByClause+') as row, * from '+@tablename+')



                   select * from temp where row between '+@startIndex+' and '+@EndIndex

   execute sp_executesql @Query;

SELECT @@ROWCOUNT as TotalCount 
END

--execution part

execute GetRecordsByPage 'tblBranch','BranchName', '2', '10'

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 11/30/2013 [Member] [MVP] Platinum | Points: 25

0	"Mark as Answer " Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Sharpcnet, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sharpcnet on: 12/1/2013 [Member] Starter | Points: 25

0	@Bandi. Thank You Very Much. Your Solution worked like a charm. When I execute I now see, two result sets - One with required records, another a count of total rows. Sharpcnet, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts