if you execute Delete * from yourTbale then you will see what are seeing, Use Truncate instead of delete, eg, Truncate table yourTableName (this will delete all the rows from your table)
|||I reset the autoincrement with this but I've never tried running it from a stored procedure.
DBCC CHECKIDENT(MyTempTable1,RESEED, 0)
You might have to go this route. "EXEC ('DBCC CHECKIDENT(MyTempTable1,RESEED,0)')"
, cyrilic, hebrew, japanese, chinese...Is it possible to split the following value in sql server ?
I have the value like 25 Email Accounts,50 Email Accounts in my sqlserver database.
Here i need only the numeric value .ie 25,50.Is it possible? can any one give me the solution ..
I am using ASP.Net and C# backend is SQL Server 2000.
Thanks and Regards
ArulUse Regular expressions to remove all Non Numeric characters.
Hello everybody,
I wrote a stored procedure for SqlServer 2000 and i am using it for paging purpose.
The procedure is as follows :
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[OLAP_PagedRows]
(
@.SelectFields nVarchar(2000) =NULL,
@.GroupByFields nvarchar(1000) =NULL,
@.BaseTable varchar(100),
@.KeyColumn nvarchar(200)=NULL ,
@.JoinTables varchar(500) =NULL,
@.ConditionalClause varchar(1000) =NULL,
@.Pagesize int = 10,
@.PageNumber int =1,
@.SortExpression nvarchar(200)=NULL,
@.SearchText nvarchar(200)=NULL
)
AS
BEGIN
DECLARE @.SQLSTMT NVarchar(4000)
DECLARE @.SQLSTMT1 NVarchar(4000)
SET @.SQLSTMT1 = ''
--check whether page size is given null or not, if so set to default value
IF @.Pagesize IS NULL OR @.Pagesize = ''
BEGIN
SET @.Pagesize =10
END
--check whether page number is given null or not, if so set to default value
IF @.PageNumber IS NULL OR @.PageNumber = ''
BEGIN
SET @.PageNumber =1
END
--Start constructing the query --
SET @.SQLSTMT = 'SELECT '
SET @.SQLSTMT1 = 'DECLARE @.CountValue INT SELECT @.CountValue = count(*) From '+@.BaseTable
SET @.SQLSTMT = @.SQLSTMT + @.SelectFields + ' FROM '+@.BaseTable
If @.JoinTables Is Not Null
BEGIN
SET @.SQLSTMT = @.SQLSTMT + ' ' +@.JoinTables
SET @.SQLSTMT1 = @.SQLSTMT1 + ' ' +@.JoinTables
END
DECLARE @.StmtWhereClause nvarchar(500)
SET @.StmtWhereClause =''
Get where conditional clause
If (@.SearchText Is Not Null AND RTRIM(LTRIM(@.SearchText))<>'')
BEGIN
SET @.StmtWhereClause = @.StmtWhereClause + ' WHERE ' + @.SearchText
END
If @.ConditionalClause Is Not Null AND RTRIM(LTRIM(@.ConditionalClause))<>''
BEGIN
IF (@.StmtWhereClause <> '')
BEGIN
SET @.StmtWhereClause= @.StmtWhereClause + 'AND ' +@.ConditionalClause
END
ELSE
BEGIN
SET @.StmtWhereClause = @.StmtWhereClause + ' WHERE ' + @.ConditionalClause
END
END
SET @.SQLSTMT = @.SQLSTMT + @.StmtWhereClause
SET @.SQLSTMT1 = @.SQLSTMT1 + @.StmtWhereClause
If @.GroupByFields Is Not Null And RTRIM(LTRIM(@.GroupByFields))<>''
BEGIN
SET @.SQLSTMT = @.SQLSTMT + ' Group By ' +@.GroupByFields
SET @.SQLSTMT1 = @.SQLSTMT1 + ' Group By ' +@.GroupByFields
END
IF @.SortExpression Is Not Null AND RTRIM(LTRIM(@.SortExpression))<>''
BEGIN
SET @.SortExpression = LTRIM(RTRIM(' Order By '+ @.SortExpression))
SET @.SQLSTMT = @.SQLSTMT +' '+ @.SortExpression
SET @.SQLSTMT1 = @.SQLSTMT1 +' '+ @.SortExpression
END
SET @.SQLSTMT1= @.SQLSTMT1+' SELECT @.CountValue As MyRows '
--SELECT @.SQLSTMT1
--SELECT @.SQLSTMT
DECLARE @.StartRow INT
SET @.SQLSTMT = ' DECLARE temp_Cursor CURSOR SCROLL FOR '+@.SQLSTMT
EXECUTE SP_EXECUTESQL @.SQLSTMT
Open temp_Cursor
DECLARE @.RowCount INT
SET @.RowCount = 1
SET @.startRow = (@.PageSize * (@.PageNumber-1))+@.RowCount
--SELECT @.startRow as 'Current Row'
WHILE @.RowCount <= @.PageSize
BEGIN
--Select @.StartRow 'as @.StartRow'
FETCH ABSOLUTE @.startRow From temp_Cursor
SET @.RowCount= @.RowCount+1
SET @.StartRow = @.startRow + 1
END
deallocate temp_Cursor
EXECUTE SP_EXECUTESQL @.SQLSTMT1
END
It is working fine but I have problem with this kind of paging. I need to load the whole data into the cursor and i have to fetch records. The problem is that my table's contains more than Half a million records in it. If I have to load each time this cursor it will be a very big problem on the server side.
Probably it may not be a best solution, but sqlserver 2000 cannot provide more help than this. If I use sub-query for this like using Top <Number> it adversly effecting the nature of the data retrieval.
One solution that I am thinking is Load cursor once and whenever some updations performed on those tables from which cursor is getting data should be automatically reflect the changes.
Is this possible? Please help me.
Regards
Andy Rogers
hi Andy Rogers
why you use cursor with more data?
please try do'nt use cursor.
you can use temp table for sorting data.
if your data wholud'nt changes, you can use static data.
order on the 0.5 millions records has overloading on the sql server 2000.
sort your data and then use that with SELECT TOP X for best performance.
good luck
I modified your code a little. They way you were doing it, returns 1 record set with 1 record for every record. This method returns all records in 1 recordset.
Basically it does a "SELECT TOP @.startRow+PageSize", so you get the smallest set from top to bottom, into a temp table, then deletes everything before the @.startRow and returns the rest. This is the best method I have found.
DECLARE @.SQLSTMT NVarchar(4000)
DECLARE @.SQLSTMT1 NVarchar(4000)
SET @.SQLSTMT1 = ''
--check whether page size is given null or not, if so set to default value
IF @.Pagesize IS NULL OR @.Pagesize = ''
BEGIN
SET @.Pagesize =10
END
--check whether page number is given null or not, if so set to default value
IF @.PageNumber IS NULL OR @.PageNumber = ''
BEGIN
SET @.PageNumber =1
END
DECLARE @.StartRow INT
SET @.startRow = (@.PageSize * (@.PageNumber-1))+ 1
--Start constructing the query --
SET @.SQLSTMT1 = 'DECLARE @.CountValue INT SELECT @.CountValue = count(*) From '+@.BaseTable
SET @.SQLSTMT = 'SELECT TOP ' + CAST(@.startRow+@.PageSize AS VARCHAR(10)) + ' IntRowNum = IDENTITY(int,1,1), '
SET @.SQLSTMT = @.SQLSTMT + @.SelectFields + ' INTO #temptable FROM '+@.BaseTable
If @.JoinTables Is Not Null
BEGIN
SET @.SQLSTMT = @.SQLSTMT + ' ' +@.JoinTables
SET @.SQLSTMT1 = @.SQLSTMT1 + ' ' +@.JoinTables
END
DECLARE @.StmtWhereClause nvarchar(500)
SET @.StmtWhereClause =''
Get where conditional clause
If (@.SearchText Is Not Null AND RTRIM(LTRIM(@.SearchText))<>'')
BEGIN
SET @.StmtWhereClause = @.StmtWhereClause + ' WHERE ' + @.SearchText
END
If @.ConditionalClause Is Not Null AND RTRIM(LTRIM(@.ConditionalClause))<>''
BEGIN
IF (@.StmtWhereClause <> '')
BEGIN
SET @.StmtWhereClause= @.StmtWhereClause + 'AND ' +@.ConditionalClause
END
ELSE
BEGIN
SET @.StmtWhereClause = @.StmtWhereClause + ' WHERE ' + @.ConditionalClause
END
END
SET @.SQLSTMT = @.SQLSTMT + @.StmtWhereClause
SET @.SQLSTMT1 = @.SQLSTMT1 + @.StmtWhereClause
If @.GroupByFields Is Not Null And RTRIM(LTRIM(@.GroupByFields))<>''
BEGIN
SET @.SQLSTMT = @.SQLSTMT + ' Group By ' +@.GroupByFields
SET @.SQLSTMT1 = @.SQLSTMT1 + ' Group By ' +@.GroupByFields
END
IF @.SortExpression Is Not Null AND RTRIM(LTRIM(@.SortExpression))<>''
BEGIN
SET @.SortExpression = LTRIM(RTRIM(' Order By '+ @.SortExpression))
SET @.SQLSTMT = @.SQLSTMT +' '+ @.SortExpression
SET @.SQLSTMT1 = @.SQLSTMT1 +' '+ @.SortExpression
END
SET @.SQLSTMT = @.SQLSTMT + ' DELETE FROM #temptable WHERE IntRowNum < ' + CAST(@.startRow AS VARCHAR(10)) + ' SELECT * FROM #temptable DROP TABLE #temptable '
SET @.SQLSTMT1= @.SQLSTMT1+' SELECT @.CountValue As MyRows '
--SELECT @.SQLSTMT1
--SELECT @.SQLSTMT
EXECUTE SP_EXECUTESQL @.SQLSTMT
EXECUTE SP_EXECUTESQL @.SQLSTMT1
