Stored Procedure Not Working [Resolved]

Posted by Sharpcnet under Sql Server on 11/30/2013 | Points: 10 | Views : 672 | Status : [Member] | Replies : 4
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#.




Responses

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

Up
0
Down

Resolved
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

Up
0
Down
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

Up
0
Down
"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

Up
0
Down
@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

Login to post response