How to determine ROWCOUNT without executing the select statement

I'm selecting data from a large table in a paged manner by using the
ROW_NUMBER ranking function in a function like the one shown below... I
want the function to also return the total number of rows in the dataset
AFTER the primary select is issued but before the paged data is selected
out. In other words, if there are 10,000 rows, and 3500 of them get
selected by the where clause, but the paging only returns 101 .. 200, I
want the total rows output to be set to 3500.
The way this is written currently, I use @.@.ROWCOUNT, but only get the
page size (eg 100). Is there an easy way to get the primary data set
size without resorting to memory or temporary tables, and without
executing the main query twice'
-mdb
#############################
PROCEDURE [dbo].[GetTablePagedAndSorted]
(
@.tableName nvarchar(100),
@.columnList varchar(2000),
@.sortExpression nvarchar(100),
@.whereClause varchar(2000),
@.startRowIndex int,
@.maximumRows int,
@.totalRows int OUTPUT
) AS
IF (LEN(@.whereClause) = 0) SET @.whereClause = '1=1'
-- Issue query
DECLARE @.sql nvarchar(4000)
SET @.sql = 'SELECT ' + @.columnList + ',RowRank '
SET @.sql = @.sql + ' FROM (
SELECT ' + @.columnList + ', ROW_NUMBER() OVER (ORDER BY ' +
@.sortExpression + ') AS RowRank
FROM ' + @.tableName + '
WHERE (' + @.whereClause + ')
) AS TableWithRowNumbers '
IF (@.maximumRows > 0)
BEGIN
SET @.sql = @.sql + '
WHERE RowRank >= ' + CONVERT(nvarchar(10), @.startRowIndex) +
'
AND RowRank < (' + CONVERT(nvarchar(10), @.startRowIndex) +
' + ' + CONVERT(nvarchar(10), @.maximumRows) + ')'
END
-- Execute the SQL query
PRINT @.sql
EXEC sp_executesql @.sql
SET @.totalRows = @.@.ROWCOUNT
######################################You could put the result into a temp. table. Or you could create a SELECT
COUNT(*) to get the count before you do the actual SELECT. I guess the
question is how important is it to know the intermediate result set size -
is it worth the extra inefficiency?
"Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in message
news:Xns99E1B6650917Embrayctiusacom@.207.46.248.16...
> I'm selecting data from a large table in a paged manner by using the
> ROW_NUMBER ranking function in a function like the one shown below... I
> want the function to also return the total number of rows in the dataset
> AFTER the primary select is issued but before the paged data is selected
> out. In other words, if there are 10,000 rows, and 3500 of them get
> selected by the where clause, but the paging only returns 101 .. 200, I
> want the total rows output to be set to 3500.
> The way this is written currently, I use @.@.ROWCOUNT, but only get the
> page size (eg 100). Is there an easy way to get the primary data set
> size without resorting to memory or temporary tables, and without
> executing the main query twice'
> -mdb
> #############################
> PROCEDURE [dbo].[GetTablePagedAndSorted]
> (
> @.tableName nvarchar(100),
> @.columnList varchar(2000),
> @.sortExpression nvarchar(100),
> @.whereClause varchar(2000),
> @.startRowIndex int,
> @.maximumRows int,
> @.totalRows int OUTPUT
> ) AS
> IF (LEN(@.whereClause) = 0) SET @.whereClause = '1=1'
> -- Issue query
> DECLARE @.sql nvarchar(4000)
> SET @.sql = 'SELECT ' + @.columnList + ',RowRank '
> SET @.sql = @.sql + ' FROM (
> SELECT ' + @.columnList + ', ROW_NUMBER() OVER (ORDER BY ' +
> @.sortExpression + ') AS RowRank
> FROM ' + @.tableName + '
> WHERE (' + @.whereClause + ')
> ) AS TableWithRowNumbers '
> IF (@.maximumRows > 0)
> BEGIN
> SET @.sql = @.sql + '
> WHERE RowRank >= ' + CONVERT(nvarchar(10), @.startRowIndex) +
> '
> AND RowRank < (' + CONVERT(nvarchar(10), @.startRowIndex) +
> ' + ' + CONVERT(nvarchar(10), @.maximumRows) + ')'
> END
> -- Execute the SQL query
> PRINT @.sql
> EXEC sp_executesql @.sql
> SET @.totalRows = @.@.ROWCOUNT
> ######################################
>|||"Mike C#" <xyz@.xyz.com> wrote in
news:OG9RqcZIIHA.4584@.TK2MSFTNGP03.phx.gbl:
> You could put the result into a temp. table. Or you could create a
> SELECT COUNT(*) to get the count before you do the actual SELECT. I
> guess the question is how important is it to know the intermediate
> result set size - is it worth the extra inefficiency?
> "Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in
> message news:Xns99E1B6650917Embrayctiusacom@.207.46.248.16...
>> I'm selecting data from a large table in a paged manner by using the
>> ROW_NUMBER ranking function in a function like the one shown below...
>> I want the function to also return the total number of rows in the
>> dataset AFTER the primary select is issued but before the paged data
>> is selected out. In other words, if there are 10,000 rows, and 3500
>> of them get selected by the where clause, but the paging only returns
>> 101 .. 200, I want the total rows output to be set to 3500.
Thanks, but as I said I do not want to use temporary tables, as this will
absolutely swamp my tempdb due to the potential size of the query results.
I considered your other suggestion previously, but then the question is how
do I get the results of an EXEC into a variable? Keep in mind that I have
to build the sql dynamically, and thus require sp_executesql. The
following syntax doesn't work:
SET @.totalRows = (EXEC sp_executesql @.mySqlStatement) ' doesn't parse
If I can find the solution to how to set a variable based on a dynamic sql
statement then I will be set.
-mdb|||Michael Bray <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in
news:Xns99E2544576DFAmbrayctiusacom@.207.46.248.16:
> Thanks, but as I said I do not want to use temporary tables, as this
> will absolutely swamp my tempdb due to the potential size of the query
> results. I considered your other suggestion previously, but then the
> question is how do I get the results of an EXEC into a variable? Keep
> in mind that I have to build the sql dynamically, and thus require
> sp_executesql. The following syntax doesn't work:
> SET @.totalRows = (EXEC sp_executesql @.mySqlStatement) ' doesn't parse
> If I can find the solution to how to set a variable based on a dynamic
> sql statement then I will be set.
>
OK I found the solution - sp_executesql can actually accept input and
output parameters!!
See the wonderful article at:
http://www.sommarskog.se/dynamic_sql.html
-mdb|||Oops, I see you already found that
"Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in message
news:Xns99E26438875B7mbrayctiusacom@.207.46.248.16...
> Michael Bray <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in
> news:Xns99E2544576DFAmbrayctiusacom@.207.46.248.16:
>> Thanks, but as I said I do not want to use temporary tables, as this
>> will absolutely swamp my tempdb due to the potential size of the query
>> results. I considered your other suggestion previously, but then the
>> question is how do I get the results of an EXEC into a variable? Keep
>> in mind that I have to build the sql dynamically, and thus require
>> sp_executesql. The following syntax doesn't work:
>> SET @.totalRows = (EXEC sp_executesql @.mySqlStatement) ' doesn't parse
>> If I can find the solution to how to set a variable based on a dynamic
>> sql statement then I will be set.
> OK I found the solution - sp_executesql can actually accept input and
> output parameters!!
> See the wonderful article at:
> http://www.sommarskog.se/dynamic_sql.html
> -mdb|||sp_executesql does take output params
"Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in message
news:Xns99E2544576DFAmbrayctiusacom@.207.46.248.16...
> "Mike C#" <xyz@.xyz.com> wrote in
> news:OG9RqcZIIHA.4584@.TK2MSFTNGP03.phx.gbl:
>> You could put the result into a temp. table. Or you could create a
>> SELECT COUNT(*) to get the count before you do the actual SELECT. I
>> guess the question is how important is it to know the intermediate
>> result set size - is it worth the extra inefficiency?
>> "Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in
>> message news:Xns99E1B6650917Embrayctiusacom@.207.46.248.16...
>> I'm selecting data from a large table in a paged manner by using the
>> ROW_NUMBER ranking function in a function like the one shown below...
>> I want the function to also return the total number of rows in the
>> dataset AFTER the primary select is issued but before the paged data
>> is selected out. In other words, if there are 10,000 rows, and 3500
>> of them get selected by the where clause, but the paging only returns
>> 101 .. 200, I want the total rows output to be set to 3500.
> Thanks, but as I said I do not want to use temporary tables, as this will
> absolutely swamp my tempdb due to the potential size of the query results.
> I considered your other suggestion previously, but then the question is
> how
> do I get the results of an EXEC into a variable? Keep in mind that I have
> to build the sql dynamically, and thus require sp_executesql. The
> following syntax doesn't work:
> SET @.totalRows = (EXEC sp_executesql @.mySqlStatement) ' doesn't parse
> If I can find the solution to how to set a variable based on a dynamic sql
> statement then I will be set.
> -mdb

How to detect overlapping Time Entries

Hello,

I am trying to create a SQL Statement which will identify if an entry can be added to a table or not. My table consists of 4 fields which are:

. UserID (Integer)
. StartTime (datetime)
. EndTime (datetime)
. Activity (varchar)

This is a timesheet application. I am trying to identify if a time entered by a user is valid or not. Basically, times cannot overlap. I'm trying to figure out how to code for the following conditions:

Assume an entry already exists for User 1 as follows:

. UserID: 1
. StartTime: 2006-12-30 08:00:00
. EndTime: 2006-12-30 08:15:00
. Activity: Test

I want to make sure that the following entries cannot be added by that user because they would overlap the existing entry:

. StartTime: 2006-12-30 07:50:00
. EndTime: 2006-12-30 08:05:00

OR

. StartTime: 2006-12-30 07:45:00
. EndTime: 2006-12-30 08:45:00

OR

. StartTime: 2006-12-30 08:05:00
. EndTime: 2006-12-30 08:30:00

OR

. StartTime: 2006-12-30 08:05:00
. EndTime: 2006-12-30 08:10:00

Any help is appreciated.

Thanks

Something like this...

select *
from timetrack_tbl
where
userid = 1
AND
(
(
'2006-12-30 07:50:00' between starttime and endtime
OR
'2006-12-30 08:05:00' between starttime and endtime
)
OR
(
'2006-12-30 07:50:00' <= starttime
AND
'2006-12-30 08:05:00' >= endtime
)
)
If you are going to be doing this continually, it might be a good choice for a function with the above code. Create the function to accept the userid, starting and ending dates and return some value indicating whether there is an overlap.

Example:
select dbo.CheckForOverlap(userid, '2006-12-30 07:50:00', '2006-12-30 08:05:00')

return something from the function that tells you if there is an overlap (like a bit, 1 = Overlap, 0 = No Overlap

|||

That worked like a charm.

Thank you so much for the quick response.

-- Val

How to detect if a schema if exists or not so that I will not create same schema agai

How to detect if a schema if exists or not so that I will not create same
schema again?
AND create schema statement should be the first statement of a bach?
--Frank, SQL2005devHello, Frank

> How to detect if a schema if exists or not so that I will not create same
> schema again?
Look into sys.schemas

> AND create schema statement should be the first statement of a bach?
Yes.
For example, you can use something like this:
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name='YourSchema')
EXEC('CREATE SCHEMA YourSchema')
Razvan|||... or use
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1137403952.648035.289210@.g49g2000cwa.googlegroups.com...
> Hello, Frank
>
> Look into sys.schemas
>
> Yes.
> For example, you can use something like this:
> IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name='YourSchema')
> EXEC('CREATE SCHEMA YourSchema')
> Razvan
>|||I got it, thanks.
"Razvan Socol" <rsocol@.gmail.com>
'?:1137403952.648035.289210@.g49g2000cwa.googlegroups.com...
> Hello, Frank
>
> Look into sys.schemas
>
> Yes.
> For example, you can use something like this:
> IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name='YourSchema')
> EXEC('CREATE SCHEMA YourSchema')
> Razvan
>

How to delete repeated entries from table using T-SQL statement

Hi Friends..

I want delete repeated entries which comes twice in a table. How to delete that extra entry and keep each single entry using T-SQL statement(SQL server 2000). Please give me the example.

Thanks & Regards,
Ravi.Hi,

You may select distinct the duplicate entry and save it in a temporary table, then delete the double entry to the main table and insert the content of the temporary table to the main table.|||Hi,

Thank you for giving solution, but still I don't know how to do that, can you send me code and e.g. It will help me for understanding. I hope you will give this solution very soon.

Thanks & Regards,
Ravi.

How to Delete Multiple Stored Procedures ?

I would Like to delete all stored procedures from the database using sql statement, wild cards with DROP PROCEDURE , dont work
ThanksOriginally posted by pkrol
I would Like to delete all stored procedures from the database using sql statement, wild cards with DROP PROCEDURE , dont work
Thanks
Use PL/SQL:

BEGIN
FOR r in (SELECT object_name FROM user_objects WHERE object_type = 'PROCEDURE' )
LOOP
EXECUTE IMMEDIATE 'DROP PROCEDURE '||r.object_name;
END LOOP;
END;
/

How to delete all rowa in all tables of a schema in Oracle?

Hi all,
I want to delete all records of all tables of a schema and think there should be some statement for this but I dont know how?
may you help?As this question is Oracle specific, I'd suggest that you post it in the Oracle (http://www.dbforums.com/f4) forum. One of the Oracle folks can probably answer your question definitively without even needing to look it up!

-PatP|||There is no simple statement available to delete only the tables.
U can instead use
DROP USER <username> CASCADE.
But caution....this will delete everything belonging to the user tables, views, sequences..etc.
If u want to delete only the table of a schema
then u can write a PL/SQL which will query for all the table from user_objects and then execute statements to delete the data from the tables|||PL/SQL procedure would do the work indeed.

Perhaps another suggestion - write a query and spool its output to an .sql file and then run it. Such as:

> set heading off;
> set feedback off;
>
> spool truncall.sql
>
> select 'truncate table ' || tname ||';' from tab where tabtype = 'TABLE';
>
> spool off;
>
> @.truncall

Why 'truncate' and not 'delete'? Delete saves all the deleted records in rollback segment(s) which slows things down.

However, you might need to run this script several times due to referential integrity constraints which might prevent some tables to be truncated (you can't delete parent while child exists).|||Thanx all for help,
but I want to delete all the records from all my tables not truncating all tables.Any idea?|||What difference do you see between deleting all of the rows and truncating the table?

-PatP