My current view is no, prefer Transact SQL stored procedures because they are a lighter weight and (possibly) higher performing option, while CLR procedures allow developers to get up to all sorts of mischief. However recently I have needed to debug some very poorly written TSQL stored procs. As usual I found many of the problems due t...
How do you randomly select a table row in T-SQL based on an applied weight for all candidate rows? For example, I have a set of rows in a table weighted at 50, 25, and 25 (which adds up to 100 but does not need to), and I want to select one of them randomly with a statistical outcome equivalent to the respective weight. ...
I'm using ADO.NET to access SQL Server 2005 and would like to be able to log from inside the T-SQL stored procedures that I'm calling. Is that somehow possible? I'm unable to see output from the 'print'-statement when using ADO.NET and since I want to use logging just for debuging the ideal solution would be to emit messages to DebugVie...
I'm using SQL Server 2005, and I would like to know how to access different result sets from within transact-sql. The following stored procedure returns two result sets, how do I access them from, for example, another stored procedure? CREATE PROCEDURE getOrder (@orderId as numeric) AS BEGIN select order_address, order_number fro...
Post your favorite book about Microsoft SQL Server (single book per comment please) or vote it up if someone has already listed it. Edit: Please include a small review or overview of subject matter and the reason for its applicability. ...
Is there a system stored procedure to get the version #? ...
I realize that CHAR is recommended if all my values are fixed-width. But, so what? Why not just pick VARCHAR for all text fields just to be safe. ...
I have a query where I wish to retrieve the oldest X records. At present my query is something like the following: SELECT Id, Title, Comments, CreatedDate FROM MyTable WHERE CreatedDate > @OlderThanDate ORDER BY CreatedDate DESC I know that normally I would remove the 'DESC' keyword to switch the order of the records, however in this...
Let's say I have the following simple table variable: declare @databases table ( DatabaseID int, Name varchar(15), Server varchar(15) ) -- insert a bunch rows into @databases Is declaring and using a cursor my only option if I wanted to iterate through the rows? Is there another way? ...
I want to be able to perform a IF...THEN in an SQL SELECT Statement. For Example; SELECT IF(Obsolete = 'N' or InStock = 'Y';1;0) as Salable, * FROM Product ...
I had to delete all the rows from a log table that contained about 5 million rows. My initial try was to issue the following command in query analyzer: delete from client_log which took a very long time. ...
Is there a system stored procedure or a system view I can use? ...
I'm using SQL Server 2000 to print out some values from a table using PRINT. With most non-string data, I can cast to nvarchar to be able to print it, but binary values attempt to convert using the bit representation of characters. For example: DECLARE @binvalue binary(4) SET @binvalue = 0x12345678 PRINT CAST(@binvalue AS nvarchar) ...
I want to port data from one server's database to another server's database. The databases are both on a different mssql 2005 server. Replication is probably not an option since the destination database is generated from scratch on a [time interval] basis. Preferebly I would do something like insert * from db1/table1 into db2/table2 wh...
I have the ISO week and year but how do I correctly convert that into two dates representing the start and end of that week? ...
How do you return 1 value per row of the max of several columns: TableName [Number, Date1, Date1, Date3, Cost] I need to return something like this: [Number, Most Recent Date, Cost] Query? ...
What's the most efficient way to convert the output of this function from a varbinary() to a a varchar()? ...
I need to provide statistics on how many lines of code (LOC) associated with a system. The application part is easy but I need to also include any code residing within the SQL Server database. This would apply to stored procedures, functions, triggers, etc. How can I easily get that info? Can it be done (accurately) with TSQL by queryi...
I just got surprised by something in TSQL. I thought that if xact_abort was on, calling something like raiserror('Something bad happened', 16, 1); would stop execution of the stored procedure (or any batch). But my ADO.NET error message just proved the opposite. I got both the raiserror error message in the exception message, plus ...
In a stored procedure, I need to get the count of the results of another stored procedure. Specifically, I need to know if it returns any results, or an empty set. I could create a temp table/table variable, exec the stored procedure into it, and then run a select count on that data. But I really don't care about the data itself, all I...