Click here to Skip to main content
15,886,110 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
My Stored Procedure,
alter procedure usp_temp_tutor_details
(
	@page		int,
	@pagesize	int
)
as
begin

create table #temp_tutor_details
(	
	id				int		IDENTITY(1,1),
	FacultyId		int,
	FacultyName		varchar(50),
	FacultyAddress	varchar(250),
	FacultyCity		varchar(50),
	FacultyMobile	varchar(30),
	FacultyExp		varchar(300)
)
	
		set identity_insert #temp_tutor_details on
				
			insert into #temp_tutor_details(FacultyId, FacultyName, FacultyAddress, FacultyCity, FacultyMobile, FacultyExp)
			
			select distinct tlm.Std_Fac_Id as FacultyId,
			ttm.Fname as FacultyName, ttm.Faddress as FacultyAddress, ttm.Fcity as FacultyCity, ttm.Fmobile as FacultyMobile, 
			ttm.Fexp as FacultyExp
			from tbl_loginMaster3 tlm full join tbl_tutorMastering ttm on
			ttm.FacultyId = tlm.Std_Fac_Id where tlm.Role = 'faculty'
			
		set	identity_insert #temp_tutor_details on	
		
		declare @from int
		declare	@to	  int
		set		@to		=	@page - @pagesize
		set		@from	=	@page * @pagesize - @pagesize
	
		select * from #temp_tutor_details where id > @from and id < = @to
		select COUNT(*) from #temp_tutor_details
		drop table #temp_tutor_details
		
end


Error:
SQL
Msg 545, Level 16, State 1, Procedure usp_temp_tutor_details, Line 22
Explicit value must be specified for identity column in table '#temp_tutor_details_________________________________________________________________________________________________000000000025' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

(0 row(s) affected)

(1 row(s) affected)
Posted
Comments
chaau 12-Sep-13 17:58pm    
Mike is right. Remove "set identity_insert #temp_tutor_details on" lines
Also, @to should be initialised like this: "set @to = @page * @pagesize"

Remove these lines:

set identity_insert #temp_tutor_details on
 
Share this answer
 
v2
Comments
riodejenris14 12-Sep-13 8:33am    
Its returning empty table Mike!
Mike Meinz 12-Sep-13 8:37am    
Experiment with the Select part of your Stored Procedure in a SQL Management Studio Query window to be sure it is retrieving rows.

select distinct tlm.Std_Fac_Id as FacultyId,
ttm.Fname as FacultyName, ttm.Faddress as FacultyAddress, ttm.Fcity as FacultyCity, ttm.Fmobile as FacultyMobile,
ttm.Fexp as FacultyExp
from tbl_loginMaster3 tlm full join tbl_tutorMastering ttm on
ttm.FacultyId = tlm.Std_Fac_Id where tlm.Role = 'faculty'

If it is, then the problem is most likely in these statements:
set @to = @page - @pagesize
set @from = @page * @pagesize - @pagesize

select * from #temp_tutor_details where id > @from and id < = @to
select COUNT(*) from #temp_tutor_details
riodejenris14 13-Sep-13 6:09am    
exactly mike thanks for ur help!
SQL
set         @from    =    ((@page-1) * @pagesize)+1
    set         @to        =    @from+(@pagesize-1)
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900