That method doesn’t work in SQL Server. You can accomplish the same thing in a couple different ways: 1.) Use the code for each aliased column instead of the alias: (SELECT COUNT(*) FROM UserEvent UE WHERE UE.EventTypeID = 1 AND UE.PracticeID = au.PracticeID AND (UE.EventDate BETWEEN @Date1 and @Date2) – COUNT(CASE WHEN udi.DevicePlatform = ‘iOS’ … Read more
How to search all columns of all tables in a database for a keyword? http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm EDIT: Here’s the actual T-SQL, in case of link rot: CREATE PROC SearchAllTables ( @SearchStr nvarchar(100) ) AS BEGIN — Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved. — Purpose: To search all columns of all tables for a … Read more
you have to call it like this SELECT dbo.CheckIfSFExists(23, default) From Technet: When a parameter of the function has a default value, the keyword DEFAULT must be specified when the function is called in order to retrieve the default value. This behaviour is different from using parameters with default values in stored procedures in which … Read more
Actually a new line in a SQL command or script string can be any of CR, LF or CR+LF. To get them all, you need something like this: SELECT REPLACE(REPLACE(@str, CHAR(13), ”), CHAR(10), ”)
MERGE was introduced in SQL Server 2008. If you want to use that syntax, you’ll need to upgrade. Otherwise, the typical approach will depend on where the source data is from. If it’s just one row and you don’t know if you need to update or insert, you’d probably do: UPDATE … WHERE key = … Read more
These 3 will get the same exact execution plan declare @id varchar(40) select @id = ‘172-32-1176’ select * from authors where au_id <> @id select * from authors where au_id != @id select * from authors where not (au_id = @id) It will also depend on the selectivity of the index itself of course. I … Read more
SELECT CASE CHARINDEX(‘ ‘, @Foo, 1) WHEN 0 THEN @Foo — empty or single word ELSE SUBSTRING(@Foo, 1, CHARINDEX(‘ ‘, @Foo, 1) – 1) — multi-word END You could perhaps use this in a UDF: CREATE FUNCTION [dbo].[FirstWord] (@value varchar(max)) RETURNS varchar(max) AS BEGIN RETURN CASE CHARINDEX(‘ ‘, @value, 1) WHEN 0 THEN @value ELSE … Read more
You can’t use the alias in the WHERE clause. Either repeat the expression (messy) or else put your SELECT in a subquery and then put the WHERE clause in the outer query: SELECT Id, Name, City, State FROM ( SELECT ID, Name, CASE T.N WHEN 1 THEN City1 WHEN 2 THEN City2 WHEN 3 THEN … Read more
this should give you the results you want. CREATE TABLE #temp ( id int, data varchar(50), section varchar(50) ) insert into #temp values(1, ‘1AAA’, ‘AAA’) insert into #temp values(1, ‘1BBB’, ‘BBB’) insert into #temp values(1, ‘1CCC’, ‘CCC’) insert into #temp values(2, ‘2AAA’, ‘AAA’) insert into #temp values(2, ‘2BBB’, ‘BBB’) insert into #temp values(2, ‘2CCC’, ‘CCC’) … Read more
ISNUMERIC will not do – it tells you that the string can be converted to any of the numeric types, which is almost always a pointless piece of information to know. For example, all of the following are numeric, according to ISNUMERIC: £, $, 0d0 If you want to check for digits and only digits, … Read more