N.B. – this question and answer relate to the 2000 version of SQL Server. In later versions, the restriction on INSERT INTO @table_variable … EXEC … were lifted and so it doesn’t apply for those later versions. You’ll have to switch to a temp table: CREATE TABLE #tmp (code varchar(50), mount money) DECLARE @q nvarchar(4000) … Read more
You could use NOT IN: SELECT A.* FROM A WHERE ID NOT IN(SELECT ID FROM B) However, meanwhile i prefer NOT EXISTS: SELECT A.* FROM A WHERE NOT EXISTS(SELECT 1 FROM B WHERE B.ID=A.ID) There are other options as well, this article explains all advantages and disadvantages very well: Should I use NOT IN, OUTER … Read more
You can use the CONVERT operator. SELECT id, name, CONVERT(bit, 1) AS active FROM users CAST or CONVERT will work.
If you want one row for each filename, reflecting a specific states and listing the most recent date then this is your friend: select filename , status , max_date = max( dates ) from some_table t group by filename , status having status=”<your-desired-status-here>” Easy!
How about combining COALESCE and NULLIF. SELECT COALESCE(NULLIF(SomeColumn,”), ReplacementColumn) FROM SomeTable
For SQL 2005 and up SELECT ROW_NUMBER() OVER( ORDER BY SomeColumn ) AS ‘rownumber’,* FROM YourTable for 2000 you need to do something like this SELECT IDENTITY(INT, 1,1) AS Rank ,VALUE INTO #Ranks FROM YourTable WHERE 1=0 INSERT INTO #Ranks SELECT SomeColumn FROM YourTable ORDER BY SomeColumn SELECT * FROM #Ranks Order By Ranks see … Read more
You have to use a CASE statement for this: SELECT CASE WHEN columnName IS NULL THEN ‘false’ ELSE ‘true’ END FROM tableName;
I wouldn’t offer this answer except that you worked so hard to document it and it’s been upvoted with no answer after a month. So, here goes. Your only choices appear to be to change the data or change the tool. Probably, I am clearly doing something wrong and missing the obvious. Could someone please … Read more
@Chris, you almost have it. ORDER BY (CASE WHEN [Submission Date] IS NULL THEN 1 ELSE 0 END) DESC, [Submission Date] DESC [Edit: #Eppz asked me to tweak the code above as currently shown] I personally prefer this a lot better than creating “magic numbers”. Magic numbers are almost always a problem waiting to happen.
Is this what you are after? select * from tempdb..sysobjects –for sql-server 2000 and later versions select * from tempdb.sys.objects –for sql-server 2005 and later versions