I have a Microsoft SQL Server 2008 query that returns data from three tables using a left outer join. Many times, there is no data in the second and third tables and so I get a null which I think is the default for left outer join. Is there a way to replace the default values in the select statement? I have a workaround in that I can ...
I'm developing an ASP.NET/C#/SQL application. I've created a query for a specific grid-view that involves a lot of joins to get the data needed. On the hosted server, the query has randomly started taking up to 20 seconds to process. I'm sure it's partly an overloaded host-server (because sometimes the query takes <1s), but I don't th...
I want to know status of every database across a SQL Server farm. I was using: select name, case status when 32 then 'loading' when 128 then 'recovering' when 512 then 'offline' when 4096 then 'single user' when 64 then 'pre recovery' when 256 then 'not recovered' el...
I'm using SQL Server 2005 and I have a DateTime column. How can I multiply the hour and minute of a DateTime column by 1.2 and return the new value So something like: SELECT MyColMinute = DATEPART(minute, myCol) * 1.2 MyColHour = DATEPART(second, myCol) * 1.2, MyCol From NyTable Of course the above won't work! So for example myCo...
Please help me to convert the below statement: CASE WHEN TITLE IS NOT NULL THEN 'A' WHEN LOCAL_TITLE IS NOT NULL THEN 'B' END AS COMBINED_TITLE to something like this: CASE WHEN TITLE IS NOT NULL THEN COMBINED_TITLE=TITLE WHEN LOCAL_TITLE IS NOT NULL THEN COMBINED_TITLE=LOCAL_TITLE END AS COMBINED_T...
How can I select the book Id that is listed under every category id I provide as a set of category Ids e.g I want to get the book Ids that are listed under all categories I listed here: (A,B,C,D,...etc) and not that are listed under any of them. the following select statement does not work with my query's conditions: SELECT bookId FROM...
My data looks like ID MyText 1 some text; some more text 2 text again; even more text How can I update MyText to drop everything after the semi-colon and including the semi colon, so I'm left with the following: ID MyText 1 some text 2 text again I've looked at SQL Server Replace, but can't think of a viable w...
Is there a way to use one CASE statement and return 2 different values? Following query has 2 CASE statements with same conditions. select case when DA.value = 1 then da.Good else da.Bad end as Foo, case when DA.value = 1 then ot.Good else ot.Bad end as Bar, from someTable DA (nolock) join otherTable OT (nolock) on OT... wh...
I found something like this in a SqlServer DB stored proc: SELECT stuff FROM mytable WHERE mytable.column = + @parameter It seems to run without error, so I assume it's okay. What would the "+" be doing? (Not surprisingly, this is a difficult topic to effectively search on, so I apologize in advance if this is a duplicate.) ...
I have a carsale project. It completely works on localhost. I have a "AddCar.aspx" page that inserts a car record with car's features. Car features are selected with checkboxes. If i don't check any checkbox, there is no problem. But if i check one of feature checkboxes, my page gives an error like this: "Subqueries are not allowed i...
I have a SQL view with following data: ID ClassName Description Flags 1 Class1 Desc1 F1 2 Class1 Desc1 F2 3 Class1 Desc1 F3 4 Class1 Desc1 F4 5 Class2 Desc2 F2 6 Class2 Desc2 F6 7 Class3 Desc3 F1 8 Clas...
when the optional parameters are null or empty how can i ignore that condition? e.g. declare @color nvarchar(50) declare @time datetime set @color = null select * from apples where apple.color = @color and apple.time = @time if @color or @time is null , i would like to ignore that condition. ...
I have a temporary table (or, say, a function which returns a table of values). I want to execute some statements for each record in the table. Can this be done without using cursors? I'm not opposed to cursors, but would like a more elegant syntax\way of doing it. Something like this randomly made-up syntax: for (select A,B from @te...
I'm trying to retrieve db sizes of all databases in my SQL Server 2005 instance. I found this select filename, size, 8192.0E * size / 1048576.0E as result from master..sysaltfiles Is the result varibale in KB? How can get this to MB? I'm confused. ...
My table name is table_1, fields are: name dept location status Status is already 1, but I want to update the status into 2 based on a certain condition else not update. My code is UPDATE Table_1 SET model = @model, make = @make, [Asset Serial No / Service Tag] = @Asset, [IT Asset Tag] = @AssetTag, ...
We have lots of stored procedures which all contain a lot of comments. Is there a way to extract the comments from the stored procedures for deployment so the users can't see them when they modify a stored procedure with SQL Server Management Studio? Note: We're using SQL Server 2008. ...
I have a database containing tables with more than 600 million records and a set of stored procedures that make complex search operations on the database. The performance of the stored procedures is so slow even with suitable indexes on the tables. The design of the database is a normal relational db design. I want to change the database...
With a temporary table as: DECLARE @Results TABLE ( CDA varchar(60), Found int default 0, Accepted int default 0, Percentage decimal(3,0) default 0.0, ) How do you take populated Found and Accepted columns and write it back to the Pecentage column? I have: UPDATE @Results SET Percentage = ( SELECT (((Accepted *...
How would one write a function in SQL Server to output a number in words? input: 1 output: one input: 129 output: one hundred twenty-nine ...
The title really is the question for this one: Is there an equivalent in T-SQL to C#'s "throw;" to re-throw exceptions? In C# one can do this: try { DoSomethingThatMightThrowAnException(); } catch (Exception ex) { // Do something with the exception throw; // Re-throw it as-is. } Is there something in T-SQL's BEGIN CATCH f...