May 27, 2016 at 10:52 am
Hello all!
I need to use on this query the pagination with parameter "Skip" and "Take" around multiple select table. I already have the query that return multiple table, but I need to implement the logic for the offset fetch pagination operation. You can test the query just only set the @SearchTerm with any value you want that exists in your database. Currently this query just output all tables that contains the value (with the rows count inside the table)
-- credit to http://stackoverflow.com/a/12306613
DECLARE @Skip int = 31 -- GET FROM ROW 31
DECLARE @Take int = 10 -- TAKE 10 FROM @Skip (31) SO FROM 31 TO 41
DECLARE @SearchTerm nvarchar(4000) = N'texttosearch' -- VALUE TO SEARCH IN DATABASE
DECLARE @TableName sysname
DECLARE @TotalCount int = 0
set @TableName = N'' -- DONT CARE NOW ABOUT THIS
set nocount on
set @SearchTerm = N'%' + @SearchTerm + '%'
declare @TabCols table (
id int not null primary key identity
, table_schema sysname not null
, table_name sysname not null
, column_name sysname not null
, data_type sysname not null
)
insert into @TabCols (table_schema, table_name, column_name, data_type)
select t.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE
from INFORMATION_SCHEMA.TABLES t
join INFORMATION_SCHEMA.COLUMNS c on t.TABLE_SCHEMA = c.TABLE_SCHEMA
and t.TABLE_NAME = c.TABLE_NAME
where 1 = 1
and t.TABLE_TYPE = 'base table'
and c.DATA_TYPE not in ('image', 'sql_variant')
and c.TABLE_NAME like case when len(@TableName) > 0 then @TableName else '%' end
order by c.TABLE_NAME, c.ORDINAL_POSITION
declare
@table_schema sysname
, @table_name sysname
, @column_name sysname
, @data_type sysname
, @exists nvarchar(4000) -- Can be max for SQL2005+
, @sql nvarchar(4000) -- Can be max for SQL2005+
, @sqlcount nvarchar(4000) -- Can be max for SQL2005+
, @where nvarchar(4000) -- Can be max for SQL2005+
, @runcount nvarchar(4000) -- Can be max for SQL2005+
, @run nvarchar(4000) -- Can be max for SQL2005+
while exists (select null from @TabCols) begin
select top 1
@table_schema = table_schema
, @table_name = table_name
, @exists = 'select null from [' + table_schema + '].[' + table_name + '] where 1 = 0'
, @sql = 'select ''' + '[' + table_schema + '].[' + table_name + ']' + ''' as TABLE_NAME, * from [' + table_schema + '].[' + table_name + '] where 1 = 0'
, @sqlcount = 'select @count = count(*) from [' + table_schema + '].[' + table_name + '] where 1 = 0'
, @where = ''
from @TabCols
order by id
declare @first_column nvarchar(50) = N''
while exists (select null from @TabCols where table_schema = @table_schema and table_name = @table_name) begin
select top 1
@column_name = column_name
, @data_type = data_type
from @TabCols
where table_schema = @table_schema
and table_name = @table_name
order by id
if @first_column = '' begin set @first_column = @column_name end
-- Special case for money
if @data_type in ('money', 'smallmoney') begin
if isnumeric(@SearchTerm) = 1 begin
set @where = @where + ' or [' + @column_name + '] = cast(''' + @SearchTerm + ''' as ' + @data_type + ')' -- could also cast the column as varchar for wildcards
end
end
-- Special case for xml
else if @data_type = 'xml' begin
set @where = @where + ' or cast([' + @column_name + '] as nvarchar(max)) like ''' + @SearchTerm + ''''
end
-- Special case for date
else if @data_type in ('date', 'datetime', 'datetime2', 'datetimeoffset', 'smalldatetime', 'time') begin
set @where = @where + ' or convert(nvarchar(50), [' + @column_name + '], 121) like ''' + @SearchTerm + ''''
end
-- Search all other types
else begin
set @where = @where + ' or [' + @column_name + '] like ''' + @SearchTerm + ''''
end
delete from @TabCols where table_schema = @table_schema and table_name = @table_name and column_name = @column_name
end
-- Count row in table
declare @count int
set @runcount = @sqlcount + @where
exec sp_executesql @runcount, N'@count int OUTPUT', @count OUTPUT
-- PROBABLY HERE BEGIN THE LOGIC TO IMPLEMENT
IF @count > 0
BEGIN
--set @run = @sql + @where + ' ORDER BY [' + @first_column + '] OFFSET ' + convert(nvarchar(255), @Skip) +' ROWS FETCH NEXT ' + convert(nvarchar(255), @Take) + ' ROWS ONLY'
set @run = 'TABLE WITH ' + convert(nvarchar(255), @count) + ' ROWS => OFFSET ' + convert(nvarchar(255), @Skip) +'(wrong) ROWS FETCH NEXT ' + convert(nvarchar(255), @Take) + '(wrong) ROWS ONLY'
print @run
--exec sp_executesql @run
set @TotalCount = @TotalCount + @count
END
-- PROBABLY HERE EMD THE LOGIC TO IMPLEMENT
end
print 'TOTAL COUNT => ' + convert(nvarchar(255), @TotalCount)
set nocount off
------------------------------------------------------
[Example #1]
I have 4 tables that match "mytexttosearchexample1":
first table have 122 rows
second table have 15 rows
third table have 53 rows
fourth table have 38 rows
So, if @Skip is 130 and @Take is 20 a correct result by the query must be:
TABLE WITH 15 ROWS => OFFSET 8 ROWS FETCH NEXT 7 ROWS ONLY
TABLE WITH 53 ROWS => OFFSET 1 ROWS FETCH NEXT 13 ROWS ONLY
TOTAL COUNT => 228
[Example #2]
I have 3 tables that match "mytexttosearchexample2":
first table have 14 rows
second table have 32 rows
third table have 11 rows
So, if @Skip is 5 and @Take is 10 a correct result by the query must be:
TABLE WITH 14 ROWS => OFFSET 5 ROWS FETCH NEXT 9 ROWS ONLY
TABLE WITH 32 ROWS => OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY
TOTAL COUNT => 57
------------------------------------------------------
I very near to get that solved! That's my current work, anyway i think that i'm missing some "IF"
-- credit to http://stackoverflow.com/a/12306613
DECLARE @Skip int = 1 -- GET FROM ROW 31
DECLARE @Take int = 9 -- TAKE 10 FROM @Skip (31) SO FROM 31 TO 41
DECLARE @ActualSkip int = @Skip
DECLARE @ActualTake int = @Take
DECLARE @SearchTerm nvarchar(4000) = N'texttosearch' -- VALUE TO SEARCH IN DATABASE
DECLARE @TableName sysname
DECLARE @TotalCount int = 0
set @TableName = N'' -- DONT CARE NOW ABOUT THIS
set nocount on
set @SearchTerm = N'%' + @SearchTerm + '%'
declare @TabCols table (
id int not null primary key identity
, table_schema sysname not null
, table_name sysname not null
, column_name sysname not null
, data_type sysname not null
)
insert into @TabCols (table_schema, table_name, column_name, data_type)
select t.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE
from INFORMATION_SCHEMA.TABLES t
join INFORMATION_SCHEMA.COLUMNS c on t.TABLE_SCHEMA = c.TABLE_SCHEMA
and t.TABLE_NAME = c.TABLE_NAME
where 1 = 1
and t.TABLE_TYPE = 'base table'
and c.DATA_TYPE not in ('image', 'sql_variant')
and c.TABLE_NAME like case when len(@TableName) > 0 then @TableName else '%' end
order by c.TABLE_NAME, c.ORDINAL_POSITION
declare
@table_schema sysname
, @table_name sysname
, @column_name sysname
, @data_type sysname
, @exists nvarchar(4000) -- Can be max for SQL2005+
, @sql nvarchar(4000) -- Can be max for SQL2005+
, @sqlcount nvarchar(4000) -- Can be max for SQL2005+
, @where nvarchar(4000) -- Can be max for SQL2005+
, @runcount nvarchar(4000) -- Can be max for SQL2005+
, @run nvarchar(4000) -- Can be max for SQL2005+
while exists (select null from @TabCols) begin
select top 1
@table_schema = table_schema
, @table_name = table_name
, @exists = 'select null from [' + table_schema + '].[' + table_name + '] where 1 = 0'
, @sql = 'select ''' + '[' + table_schema + '].[' + table_name + ']' + ''' as TABLE_NAME, * from [' + table_schema + '].[' + table_name + '] where 1 = 0'
, @sqlcount = 'select @count = count(*) from [' + table_schema + '].[' + table_name + '] where 1 = 0'
, @where = ''
from @TabCols
order by id
declare @first_column nvarchar(50) = N''
while exists (select null from @TabCols where table_schema = @table_schema and table_name = @table_name) begin
select top 1
@column_name = column_name
, @data_type = data_type
from @TabCols
where table_schema = @table_schema
and table_name = @table_name
order by id
if @first_column = '' begin set @first_column = @column_name end
-- Special case for money
if @data_type in ('money', 'smallmoney') begin
if isnumeric(@SearchTerm) = 1 begin
set @where = @where + ' or [' + @column_name + '] = cast(''' + @SearchTerm + ''' as ' + @data_type + ')' -- could also cast the column as varchar for wildcards
end
end
-- Special case for xml
else if @data_type = 'xml' begin
set @where = @where + ' or cast([' + @column_name + '] as nvarchar(max)) like ''' + @SearchTerm + ''''
end
-- Special case for date
else if @data_type in ('date', 'datetime', 'datetime2', 'datetimeoffset', 'smalldatetime', 'time') begin
set @where = @where + ' or convert(nvarchar(50), [' + @column_name + '], 121) like ''' + @SearchTerm + ''''
end
-- Search all other types
else begin
set @where = @where + ' or [' + @column_name + '] like ''' + @SearchTerm + ''''
end
delete from @TabCols where table_schema = @table_schema and table_name = @table_name and column_name = @column_name
end
-- Count row in table
declare @count int
set @runcount = @sqlcount + @where
exec sp_executesql @runcount, N'@count int OUTPUT', @count OUTPUT
-- PROBABLY HERE BEGIN THE LOGIC TO IMPLEMENT
if @count > 0
begin
if @count >= @ActualSkip
begin
if @take <= @count
begin
if @ActualSkip + @Take <= @count
begin
set @ActualTake = @Take
end
else
begin
set @ActualTake = @count - @ActualSkip
end
end
else
begin
set @ActualTake = @count
end
end
else begin
set @ActualTake = 0
set @ActualSkip = @ActualSkip - @count
end
set @Take = @Take - @ActualTake
if @ActualTake > 0
begin
--set @run = @sql + @where + ' ORDER BY [' + @first_column + '] OFFSET ' + convert(nvarchar(255), @Skip) +' ROWS FETCH NEXT ' + convert(nvarchar(255), @Take) + ' ROWS ONLY'
set @run = 'TABLE WITH ' + convert(nvarchar(255), @count) + ' ROWS => OFFSET ' + convert(nvarchar(255), @ActualSkip) +' ROWS FETCH NEXT ' + convert(nvarchar(255), @ActualTake) + ' ROWS ONLY'
print @run
--exec sp_executesql @run
end
set @TotalCount = @TotalCount + @count
end
-- PROBABLY HERE EMD THE LOGIC TO IMPLEMENT
end
print 'TOTAL COUNT => ' + convert(nvarchar(255), @TotalCount)
set nocount off
Please to get all table with numbers of existing elements that contains the value refer to the first query
Thank you in advance
May 27, 2016 at 11:11 am
I don't have time to wade through all that code right now, but are you actually using the OFFSET clause?? Look it up in Books Online if not.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 27, 2016 at 11:15 am
Yes i'm using offset, the problem is that i need to calculate the offset around multiple query in print output. For understand what i mean please have a look at the 2 example in the first post
Please also don't be afraid for the bigger code 🙂 the logic I need is just in range of the "BEGIN END LOGIC IMPLEMENT" comment at the end of the code 😛
May 27, 2016 at 12:02 pm
I think it would be better to approach this as combining the tables into a single output and then applying the OFFSET/FETCH to that single output.
SELECT *, 1 AS table_num
FROM TABLE1
UNION ALL
SELECT *, 2
FROM TABLE2
UNION ALL
SELECT *, 3
FROM TABLE3
UNION ALL
SELECT *, 4
FROM TABLE4
ORDER BY table_num
OFFSET @Skip ROWS
FETCH NEXT @take ROWS ONLY
If that doesn't work, then provide sample data and expected results as outlined by the article in my signature.
Drew
Edit: corrected typo.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 27, 2016 at 12:18 pm
Thanks for your response Drew,
the output of my query is a print of multiple select of different tables, so an "union" can't work
You can test the query in all database, because that return dynamic data.
I try to explain a little better:
try to run on your database the first query I have posted putting an any value that exists in your database like "car" in @SearchTerm
Ok, the query will show to you something like that (based on result of your database):
TABLE WITH 1 ROWS => OFFSET 31(wrong) ROWS FETCH NEXT 10(wrong) ROWS ONLY
TABLE WITH 1 ROWS => OFFSET 31(wrong) ROWS FETCH NEXT 10(wrong) ROWS ONLY
TABLE WITH 1 ROWS => OFFSET 31(wrong) ROWS FETCH NEXT 10(wrong) ROWS ONLY
TABLE WITH 292 ROWS => OFFSET 31(wrong) ROWS FETCH NEXT 10(wrong) ROWS ONLY
TABLE WITH 1 ROWS => OFFSET 31(wrong) ROWS FETCH NEXT 10(wrong) ROWS ONLY
TABLE WITH 1 ROWS => OFFSET 31(wrong) ROWS FETCH NEXT 10(wrong) ROWS ONLY
TOTAL COUNT => 297
Good, if I set @Skip = 32 and @Take = 9 a correct result of the query must be:
TABLE WITH 292 ROWS => OFFSET 29 ROWS FETCH NEXT 9 ROWS ONLY
TOTAL COUNT => 297
Or, if I set @Skip = 2 and @Take = 20 a correct result of the query must be:
TABLE WITH 1 ROWS => OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY
TABLE WITH 1 ROWS => OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY
TABLE WITH 292 ROWS => OFFSET 1 ROWS FETCH NEXT 18 ROWS ONLY
TOTAL COUNT => 297
Another, if I set @Skip = 290 and @Take = 6 a correct result of the query must be:
TABLE WITH 292 ROWS => OFFSET 287 ROWS FETCH NEXT 5 ROWS ONLY
TABLE WITH 1 ROWS => OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY
TOTAL COUNT => 297
Well, i'm very near to get that solved with the second query I have posted, anyway im wrong something
May 27, 2016 at 1:25 pm
The reason that we ask for sample data and expected output, is because it is often very difficult to describe in words what you are looking for. Your description makes no sense. If the queries are related, there should be some way to put them together. If they aren't related, it doesn't make sense why the results of one query should affect the results of another query.
Drew
PS: My name is Drew, which is why I sign my posts Drew. If I wanted to be known by some other name, I would sign with that other name.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 27, 2016 at 2:16 pm
Dear Drew, I'm sorry, it was not my intention 🙂
I know that's difficult to explain and i'm trying to do my best
Ok, here some data to seed the database
CREATE TABLE Tab1 (Col1 int,Col2 nvarchar(5),Col3 nvarchar(10))
CREATE TABLE Tab2 (Col1 nvarchar(10),Col2 int)
CREATE TABLE Tab3 (Col1 int,Col2 nvarchar(10))
declare @id int = 1
while @id >=1 and @id <= 32
begin
insert into Tab1 values(@id, 'photo', 'movie')
select @id = @id + 1
end
set @id = 1
while @id >=1 and @id <= 75
begin
insert into Tab2 values('movie', @id)
select @id = @id + 1
end
set @id = 1
while @id >=1 and @id <= 24
begin
insert into Tab3 values(@id, 'movie')
select @id = @id + 1
end
if we try to run the first query with @Skip = 31, @Take = 10 and @SearchTerm = N'movie' the output will be:
TABLE WITH 32 ROWS => OFFSET 31(wrong) ROWS FETCH NEXT 10(wrong) ROWS ONLY
TABLE WITH 75 ROWS => OFFSET 31(wrong) ROWS FETCH NEXT 10(wrong) ROWS ONLY
TABLE WITH 24 ROWS => OFFSET 31(wrong) ROWS FETCH NEXT 10(wrong) ROWS ONLY
TOTAL COUNT => 131
but that's wrong! The correct output must be:
TABLE WITH 32 ROWS => OFFSET 31 ROWS FETCH NEXT 1 ROWS ONLY
TABLE WITH 75 ROWS => OFFSET 1 ROWS FETCH NEXT 9 ROWS ONLY
TOTAL COUNT => 131
Or with @Skip = 70, @Take = 15 and @SearchTerm = N'movie' an correct output must be:
TABLE WITH 75 ROWS => OFFSET 38 ROWS FETCH NEXT 15 ROWS ONLY
TOTAL COUNT => 131
Hope that can help, again thank you all very much
May 31, 2016 at 10:05 am
Why do you want to skip the first row in subsequent tables?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply