Yes, in SQL Server 2005 it&#39;s possible to use a variable in the `top` clause.

    select top (@top) * from tablename

SQL Server 2005 actually allows us to parameterize the TOP clause, using a variable, expression or statement. So you can do things like: 
 

    SELECT TOP (@foo) a FROM table ORDER BY a 
     
    SELECT TOP (SELECT COUNT(*) FROM somewhere else) a FROM table ORDER BY a 
     
    SELECT TOP (@foo + 5 * 4 / 2) a FROM table ORDER BY a 

[Source][1]


  [1]: https://web.archive.org/web/20150520123828/http://sqlserver2000.databases.aspfaq.com:80/how-do-i-use-a-variable-in-a-top-clause-in-sql-server.html

In 2005 and later, you can do it as there are several replies in this thread.

Whats less known is that you can achieve this also in 2k, by using SET ROWCOUNT.

      -- Works in all versions
      SELECT TOP 10
     
      -- Does not work on 2000
      SELECT TOP (10)
      SELECT TOP (@rows)
     
      -- Works in both 2ooo and 2oo5
      SET ROWCOUNT @max
     
      SELECT * 
      FROM ...
     
      SET ROWCOUNT 0

Note, if you forget the SET ROWCOUNT 0 at the end, the limit persists.. and you will end up with very hard to locate bugs :-)


So I have two dates `YYYY-MM-DD` and `ZZZZ-NN-EE`

How can I find out how many seconds there are between them?

How do I calculate how many seconds between two dates?

There is paradox in the exception description:
Nullable object must have a value (?!)

This is the problem:

I have a `DateTimeExtended` class, 
that has 

    {
      DateTime? MyDataTime;
      int? otherdata;
    
    }

and a constructor

    DateTimeExtended(DateTimeExtended myNewDT)
    {
       this.MyDateTime = myNewDT.MyDateTime.Value;
       this.otherdata = myNewDT.otherdata;
    }

running this code

    DateTimeExtended res = new DateTimeExtended(oldDTE);

throws an `InvalidOperationException` with the message:

&gt;Nullable object must have a value.

`myNewDT.MyDateTime.Value` - is valid and contain a regular `DateTime` object.

What is the meaning of this message and what am I doing wrong?


Note that `oldDTE` is not `null`. I&#39;ve removed the `Value` from `myNewDT.MyDateTime` but the same exception is thrown due to a generated setter.


nullable object must have a value

    declare @top  int
    set @top = 5
    select top @top * from tablename


Is it possible?

Or any idea for such a logic (i don&#39;t want to use dynamic query)?



Use variable with TOP in select statement in SQL Server without making it dynamic

<pre><code class="hljs language-sql">declare @top int
set @top = 5
select top @top * from tablename
</code></pre>
Is it possible?
Or any idea for such a logic (i don't want to use dynamic query)?

In MySQL, I&#39;m sick of adding the columns `dt_created` and `dt_modified` (which are date time stamps for creation and last modified respectively) to all the tables I have in my database.

Every time I `INSERT` or `UPDATE` the database, I will have to use the `NOW()` keyword. This is going all over my persistence. 

Is there any efficient alternative where MySQL can automatically store at least the datatime of the row that is inserted and let me retrieve it?

MySQL auto-store datetime for each row

I am learning about the wonders of JdbcTemplate and NamedParameterJdbcTemplate. I like what I see, but is there any easy way to see the underlying SQL that it ends up executing? I&#39;d like to see this for debug purposes (in order to for example debug the resulting SQL in an outside tool).

Seeing the underlying SQL in the Spring JdbcTemplate?

I am trying to insert a datetime value into a [SQLite][1] database. It seems to be sucsessful but when I try to retrieve the value there is an error:

&gt; &amp;lt;Unable to read data&gt;

The SQL statements are:

    create table myTable (name varchar(25), myDate DATETIME)
    insert into myTable (name,mydate) Values (&#39;fred&#39;,&#39;jan 1 2009 13:22:15&#39;)


  [1]: http://en.wikipedia.org/wiki/SQLite

How do I insert datetime value into a SQLite database?

How can I select 12/20/2008 in `where` clause of sql?

The server is SQL server 2005.

    select * from tblErrorLog
    where errorDate = &#39;12/20/2008&#39;

Datetime in where clause

We have a large Oracle database with many tables.  Is there a way I can query or search to find if there are any tables with certain column names?

IE show me all tables that have the columns: `id, fname, lname, address`

Detail I forgot to add: I need to be able to search through different schemas.  The one I must use to connect doesn&#39;t own the tables I need to search through.

Search an Oracle database for tables with specific column names?

I&#39;m running an IIS 7 Website with an AppPool of *Integrated Pipeline Mode*.
The AppPools does NOT run under NetworkService, etc.. identity (by purpose), but uses its own AppPool Identitiy (IIS AppPool\MyAppPool).

This is a so called service account or virtual account.
(a user account, which is not a full account...)

I&#39;d like to give this service account (IIS AppPool\MyAppPool) permissions to connect to my SQL Server 2008 Express (running in Mixed Auth. Mode).

**While SQL Server can add any normal user account, the IIS AppPool\MyAppPool virtual account cannot be added to the valid logons (SQL Server says, that the account cannot be found).**

Is there any trick, anything I need to enable to make the virtual accounts work?
(the w3wp.exe process runs under this identity according to taskmgr, but I cannot use the account in NTFS security either...)

Thanks for your help!

Add IIS 7 AppPool Identities as SQL Server Logons

I am trying to print a selected value, is this possible?

Example:

    PRINT 
        SELECT SUM(Amount) FROM Expense

SQL Server PRINT SELECT (Print a select query result)?

I have two variables, one is called `PaidThisMonth`, and the other is called `OwedPast`. They are both results of some subqueries in SQL. How can I select the smaller of the two and return it as a value titled `PaidForPast`?

The `MIN` function works on columns, not variables.


Getting the minimum of two values in SQL

Every SQL Server connection string I ever see looks something like this:

    Data Source=MyLocalSqlServerInstance;Initial Catalog=My Nifty Database;
        Integrated Security=SSPI;

Do I need the Initial Catalog setting?  (Apparently not, since the app I&#39;m working on appears to work without it.)

Well, then, what&#39;s it for?

What is the point of &quot;Initial Catalog&quot; in a SQL Server connection string?

I want to update two tables in one go. How do I do that in SQL Server 2005?

    UPDATE 
      Table1, 
      Table2
    SET 
      Table1.LastName=&#39;DR. XXXXXX&#39;, 
      Table2.WAprrs=&#39;start,stop&#39;
    FROM 
      Table1 T1, 
      Table2 T2
    WHERE 
      T1.id = T2.id
    AND 
      T1.id = &#39;010008&#39;

How to update two tables in one statement in SQL Server 2005?

I&#39;m curious which of the following below would be more efficient?

I&#39;ve always been a bit cautious about using `IN` because I believe SQL Server turns the result set into a big `IF` statement. For a large result set, this could result in poor performance. For small result sets, I&#39;m not sure either is preferable. For large result sets, wouldn&#39;t `EXISTS` be more efficient?

    WHERE EXISTS (SELECT * FROM Base WHERE bx.BoxID = Base.BoxID AND [Rank] = 2)

vs.

    WHERE bx.BoxID IN (SELECT BoxID FROM Base WHERE [Rank = 2])

SQL Server IN vs. EXISTS Performance

I&#39;m interested in the side effects and potential problems of the following pattern:


    CREATE PROCEDURE [Name]
    AS
    BEGIN
    	BEGIN TRANSACTION
    	BEGIN TRY
    		[...Perform work, call nested procedures...]
    	END TRY
    	BEGIN CATCH
    		ROLLBACK TRANSACTION
    		RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]
    	END CATCH
    END

To the best of my understanding this pattern is sound when used with a single procedure - the procedure will either complete all of its statements without error, or it will rollback all actions and report the error.

However when one stored procedure calls another stored procedure to do some sub-unit of work (with the understanding that the smaller procedure is sometimes called on its own) I see an issue coming about with relation to rollbacks - an informational message (Level 16) is issued stating `The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.`. This I assume is because the rollback in the sub-procedure is always rolling back the outer-most transaction, not just the transaction started in the sub-procedure.

I do want the whole thing rolled back and aborted if any error occurs (and the error reported to the client as an SQL error), I&#39;m just not sure of all the side effects that come from the outer layers trying to rollback a transaction that has already been rolled back. Perhaps a check of `@@TRANCOUNT` before doing a rollback at each TRY CATCH layer?

Finally there is the client end (Linq2SQL), which has it&#39;s own transaction layer:

    try
    {
        var context = new MyDataContext();
    	using (var transaction = new TransactionScope())
    	{    	
                // Some Linq stuff
	        context.SubmitChanges();
    		context.MyStoredProcedure();
    		transactionComplete();
    	}
    }
    catch
    {
    	// An error occured!
    }

In the event that a stored procedure, &quot;MySubProcedure&quot;, called *inside* MyStoredProcedure raises an error, can I be sure that everything previously done in MyStoredProcedure will be rolled back, all the Linq operations made by SubmitChanges will be rolled back, and finally that the error will be logged? Or what do I need to change in my pattern to ensure the whole operation is atomic, while still allowing the child parts to be used individually (i.e. the sub-procedures should still have the same atomic protection)

Nested stored procedures containing TRY CATCH ROLLBACK pattern?

Is there a way in T-SQL to cast an nvarchar to int and return a default value or NULL if the conversion fails?

TSQL - Cast string to integer or return default value

I&#39;ve read up on this on MSDN forums and here and I&#39;m still not clear. I think this is correct: Varchar(max) will be stored as a text datatype, so that has drawbacks. So lets say your field will reliably be under 8000 characters. Like a BusinessName field in my database table. In reality, a business name will probably always be under (pulling a number outta my hat) 500 characters. It seems like plenty of varchar fields that I run across fall well under the 8k character count. 

So should I make that field a varchar(500) instead of varchar(8000)? From what I understand of SQL there&#39;s no difference between those two. So, to make life easy, I&#39;d want to define all my varchar fields as varchar(8000). Does that have any drawbacks? 

Related: https://stackoverflow.com/questions/177354/size-of-varchar-columns (I didn&#39;t feel like this one answered my question). 

is there an advantage to varchar(500) over varchar(8000)?

I&#39;m trying to set the value in one table to the sum of the values in another table.  Something along these lines:

    UPDATE table1
    SET field1 = SUM(table2.field2)
    FROM table1
    INNER JOIN table2 ON table1.field3 = table2.field3
    GROUP BY table1.field3

Of course, as this stands, it won&#39;t work - &lt;code&gt;SET&lt;/code&gt; doesn&#39;t support &lt;code&gt;SUM&lt;/code&gt; and it doesn&#39;t support &lt;code&gt;GROUP BY&lt;/code&gt;.  

I should know this, but my mind&#39;s drawing a blank.  What am I doing wrong?

Content Type	Original Author	Original Content on Stackoverflow
Question	Paresh	View Question on Stackoverflow
Solution 1 - Sql	Guffa	View Answer on Stackoverflow
Solution 2 - Sql	Espo	View Answer on Stackoverflow
Solution 3 - Sql	Brimstedt	View Answer on Stackoverflow

Use variable with TOP in select statement in SQL Server without making it dynamic

Sql Problem Overview

Sql Solutions

Solution 1 - Sql

Solution 2 - Sql

Solution 3 - Sql

nullable object must have a value

How do I calculate how many seconds between two dates?

Attributions