I wish to do something like this: DECLARE @IgnoreNulls = 1; SELECT Col1, Col2 FROM tblSimpleTable IF @IgnoreNulls BEGIN WHERE Col2 IS NOT NULL END ORDER BY Col1 DESC; The idea is to, in a very PHP/ASP.NET-ish kinda way, only filter NULLs if the user wishes to. Is this possible in T-SQL? Or do we need one large IF block like so: IF...
I have two tables. Table A has a list of employee names. Table B is a complex table with information about phone calls made by employees. My goal is to make a table with columns 'name' and 'callCount'. I am aiming to do this with a 'left join' and a 'group by', but I keep missing the employees that have made no calls. How can I just get...
I am in a situation where I need to run multiple times the same queries. I have to look if one of the rows returned correspond to a specific value (query 1) otherwise, I have to return the first row of the result set (query 2). In SQL Server 2008, I am wondering what is best: running the query and storing the results in a table variable ...
I wrote a stored procedure with optional parameters. CREATE PROCEDURE dbo.GetActiveEmployee @startTime DATETIME=NULL, @endTime DATETIME=NULL AS SET NOCOUNT ON SELECT columns FROM table WHERE (@startTime is NULL or table.StartTime >= @startTime) AND (@endTIme is NULL or table.EndTime <= @endTime) I'm won...
There is a customer table. I want to list active and deactive status in one query. How can I do this ? SELECT count(*) as ACTIVE, count(*) as DEACTIVE FROM V_CUSTOMER WHERE STATUS='a' AND STATUS='d' ...
Hi, Is there a an empty statement keyword in T-SQL in Sql Server 2005 or newer? Something like NULL statement in PL/SQL. ...
Hello. I have an assignment table in my database. I also have a assignment_notes table, which has a reference to the assignment table. There can exists multiple rows in this table. When I select all my assignments, I want to check if there exists some notes to this assignment. And all I want is a true/false return. Is it possible to d...
I have an insert statement: insert into parentTbl select firstId, secondId, thirdId, dateTm from importTbl where codeId = @codeIdParam I need to reliably find out if that insert inserted anything at all. Ideally, I would like to set a @insertedCount variable to the number of rows inserted, even if that is 0. I am currently using: s...
I wrote a T-SQL Statement similar like this (the original one looks different but I want to give an easy example here): SELECT first_name + CASE last_name WHEN null THEN 'Max' ELSE 'Peter' END AS Name FROM dbo.person This Statement does not have any syntax errors but the case-clause always chooses the ELSE-part - also if the last_name...
I start with a table looking like this... +-------------------------+ | procName | TimeEnded | +-------------------------+ | A | 04:00:00.000 | | B | 04:01:00.000 | | C | 04:03:00.000 | | A | 04:06:00.000 | | B | 04:10:00.000 | | ... | ... | Run a query to generate a RunTime column,...
I need to run a script like EXEC sp_dbcmptlevel AdventureWorks, 100; GO Now I'd like to have a generic version that uses the database I am connected to, is it possible in T-SQL? Something like this, but this doesn't work: EXEC sp_dbcmptlevel DBNAME(), 100; GO It doesn't work of course because a string is returned, I'd like to have...
I am currently writing a script that intelligently strips a database down into a series of ordered INSERT statements with seeded identity columns that will allow me to place the records into a new database without destroying any keys or relationships. I am using the PRINT function to write the finished insert statements to the message w...
I have a query which returns a set of numbers: SELECT Sequence FROM Table1 WHERE Hash=2783342 Returns: 578 642 313 Now, I want to find all rows in the first table where any of that set of numbers is between two other columns. For the purpose of illustration, I am just picking 578, but I also want all of the rest: SELECT * FROM Tab...
Greetings, How to create duplicate table (structure only) with new name in the same database in SQL Server 2008? I have table with 45 fields so I want to create new with same structure but new name. I do not want to copy the data!! Thank you, ...
I have a table that I need to normalize with many fields In SQL-Server 2000. It contains 2 fields which I'm using to come up with distinct combination as defined by the specs. ID and Rate: there are multiple rows of same IDs and Rates I first created a temp table by grouping the IDs and Rates combination. SELECT ID, Count(*) AS IDCou...
I want to be able to create a group of records at one time and guarantee that the identity field is continuous for the group (no breaks due to somebody else coming in and creating a record while this is in process). I'm assuming some kind of table lock would work, but I'm no sql guru so any advice would be appreciated (what type of lock?...
Is it possible to include a set of 'constant' values in a TSQL stored procedure? I have a situation where I'm using an integer field to store bit values and I have small set of 'constant' values that I use to insert/select against that field DECLARE @CostsCalculated int = 32 DECLARE @AggregatedCalculated int = 64 --Set CostCalculated b...
Hi, What changes SqlCommand.Parameters.AddWithValue() does with the query? I expect that: It replaces every ' character by '', If a parameter value is a string or something which must be converted to a string, it surrounds the value by ', so for example select * from A where B = @hello will give select * from A where B = 'hello world...
Let's consider this basic insert insert into TableName (Col1,Col2,Col3) values (Val1,Val2,Val3) i want this insert to be done only if Val1 !=null and Val3!=null How to accomplish this? ...
Hello everyone, I am using SQL Server 2008 Enterprise + VSTS 2008 + C# + .Net 3.5 + ASP.Net + IIS 7.0 to develop a simple web application. In my database table, I have a XML type column. The content is like below, I want to get AdditionalInfoList of all rows in the table if Title contains "software engineer" or Info contains "Software ...