Makiwa

I tried to uninstall SQL Server 2005 Developer Edition today and was mortified when, halfway through the uninstallation (is that a word?), “SQL Server setup encountered an error and needs to close”! I got a sickly, sinking feeling in my stomach - my immediate future was devoid of fun. After all, I needed to re-install […]

I was really confused this morning. I was executing a stored proc that returned a single row of data, and a return value. However, the return value was always null - even though the stored proc ALWAYS returned a return value. The code was domething like this:
PLAIN TEXT
C#:

SqlParameter retval = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);

retval.Direction = ParameterDirection.ReturnValue;

command1.Parameters.Add(retval);

using [...]

The Enterprise Library Data Access application block offers some time-saving routines for calling stored procedures. However, they don't all work when the stored procedure is a synonym.
For example, once I have a Database object, I can get a DataReader from a stored proc called GetUser by simply calling:
PLAIN TEXT
C#:

Database db = DatabaseFactory.CreateDatabase();

IDataReader reader = db.ExecuteReader("GetUser", [...]

The project I am working on required sorting, filtering and paging of large data sets. This isn't an uncommon requirement; yet the solutions are often either fiddly or not very performant. But fortunately, using SQL Server 2005's common table expressions and the new ROW_NUMBER() function, I managed to create a stored procedure that provides sorting, [...]