Tag: sql

SQL Server : check for existing entry before UPDATE

This question already has an answer here: Solutions for INSERT OR UPDATE on SQL Server 21 answers

SQL Server – How to filter rows based on matching rows?

I have a complex query that feeds into a simple temp table named #tempTBRB. select * from #tempTBRB ORDER BY AccountID yields this result set: In all cases, when there is only 1 row for a given AccountID, the row should remain, no problem. But whenever there are 2 rows (there will never be more […]

SQL Finding maximum value without top command

Let’s say I have a bases with a table: -courses (key: name [ofthecourse], other attributes: year in which the course takes place) I want to complete a query looking for an answer to the question: On which year of study there is a maximum number of courses? Normally, the query would be: SELECT TOP 1 […]

CharIndex exact match?

I have a parameter being passed into a usp that is a varchar(max). This parameters is something like the following: ‘1,20,3,40,0’ ‘1,5,30,0,5,9’ ‘0,50,40,8,9’ ‘1,10,2,3,4’ I need to figure out if the parameter has a value of just 0 in it anywhere. I was going to use: CASE WHEN CHARINDEX(‘0’,@DispIDs) > 0 THEN convert(bit,1) ELSE convert(bit,0) […]

Error while getting sql output to html

DECLARE @tableHTML NVARCHAR(MAX) ; SET @tableHTML = N'<H1>Additions</H1>’ + N'<table border=”1″>’ + N'<th>f1</th>’ + N'<th>f2</th>’ + N'<th>f3</th>’ + ‘<tr>’ + CAST ( ( SELECT td = CL.f1, ”, td = CL.F2, ”, td = CL.F3, ” from (select * from table2 Where asof = ‘2/21/2014’ and f1= ‘abc’) CL FOR XML PATH(‘tr’), TYPE ) AS […]

How can I check a uniqueidentifier is in a comma delimited list of ids?

I am trying to insert some records into a many to many table, the condition though is that a particular Id (uniqueidentifier) is in a comman seperated list of Id’s (nvarchar). I had thought it was working but it only seems to compare it to the first Id in the list? How can I correctly […]

Set variable value from case statement

I need to count all ‘0’ in the string after coma if value is smaller then 1. Example: I have string ‘0,005’, the result will be 2. Thats works fine: declare @s varchar(15), @i int Set @s=’0,05′ Set @i=Len(Substring(@s, 3, Len(@s)-3)); print @i But if I do so, I have an error: declare @s varchar(15), […]

How to output a single column or concatenate another one if multiple rows are in group in T-SQL

Imagine the following structure: Group Color ColorDesc —– —– —– 1 ‘Red’ ‘The cool name of Red Color’ 1 ‘Green’ ‘The cool name of Green Color’ 2 ‘Blue’ ‘The cool name of Blue Color’ 2 ‘Yellow’ ‘The cool name of Yellow Color’ 2 ‘Purple’ ‘The cool name of Purple Color’ 3 ‘Pink’ ‘The cool name […]

SQL DataType Conversion Error

I am trying this code: DECLARE @testID varchar(max) set @testID = ‘1,2,3’ Delete from [Test] where [Test_ID] in (@testID) but I’m getting an error: Conversion failed when converting the varchar value ‘1,2,3’ to data type int. Can anyone please help….

Query to find Exact count

Below is my database tables I need to find exact count [Request] Table [RequestID] RequestName RequestedOn 1 XYZ 4/5/2014 2 ABC 5/5/2014 3 MNO 4/5/2014 [Inspection] Table InspectionID, [RequestID] 1 1 2 2 3 3 [InspectionTrack] Table TrackID InspectionID StatusCode FinishedOn 1 1 0 5/5/2014 2 1 2 6/5/2014 3 1 5 6/5/2014 4 2 […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.