Tag: sql

Combine result set from stored procedure

I want to combine the result set of my stored procedure. I am using UNION, however it doesn’t work and throws errors like this: Msg 156, Level 15, State 1, Line 147 Incorrect syntax near the keyword ‘UNION’. Msg 102, Level 15, State 1, Line 251 Incorrect syntax near ‘END’. I also tried putting in […]

How to set that a table foreign key value must be same in another table

For example that i have 4 tables, that is Student, School,Tour and TourAttend. Tours are organised on a school basis and there will never be students from more than one school on the same tour. School SchoolID Primary key SchoolName Student StudentID primary key SchoolID Foreign key StudentName Tour TourID Primary key TourName TourAttend TourAttendID […]

SQL Server, Duplicate subquery, and Alias

I have a simple query as the following: Select SUBSTRING(Email, Charindex(‘@’, Email) + 1, Len(Email) – CharIndex(‘@’, Email)) as EmailDomain, Count(Email) as Total from tblPerson Group by SUBSTRING(Email, Charindex(‘@’, Email) + 1, Len(Email) – CharIndex(‘@’, Email)) Which is working fine except that there is a subquery duplicate: SUBSTRING(Email, Charindex(‘@’, Email) + 1, Len(Email) – CharIndex(‘@’, […]

Converting entire table column of varchar/varbinary to xml

I have a table with one of the columns as varbinary, which actually contains base64 encoded string. Table: Messages Id | int Payload | varbinary(MAX) I can see its contents using select cast(Payload as varchar(max)) from Messages However, I need to convert the whole column to XMl, so that these kind of cast is not […]

Alternative to SQL Sub Query for Returning Prior Matching Records

SQL Azure Database V12 (SQL Server 2016) Given the following basic table structure: MyTable ============== Id int PK TheDate datetime2 not null TheValue varchar(50) not null — other fields And the following SQL: select ( select count(*) from MyTable mt2 where mt2.TheValue = mt1.TheValue and mt2.TheDate < mt1.TheDate ) as PriorCount , mt1.TheDate, mt1.TheValue from […]

Get most recent job's data for all unique jobs

I am having a hard time figuring out where the problem is in my code. I Have a few jobs running periodically at different frequencies. From those jobs I need data for most recent job running data for all unique jobs from Jobs Table. Any help will be highly appreciated. Jobs – Master Table Job_Id […]

Procedure or function expects parameter which was not supplied – Parameter is in stored procedure(I think)

I’ve encountered an error which is absolutely driving me insane. Forgive me, I’m a novice, so I may be missing something out which is stupid or silly so sorry in advance. I keep getting the error “Procedure or function ‘prcPersonalSelectedByPatientIdAppointmentSelect’ expects parameter ‘@PatientNumber’, which was not supplied.” This is baffling to me as ‘@PatientNumber’ is […]

Selecting objects that are associated with similar datasets

I’m trying to select all company rows from a [Company] table that share with at least one other company, the same number of employees (from an [Employee] table that has a CompanyId column), where each group of respective employees share the same set of LocationIds (a column in the [Employee] table) and in the same […]

Store update, insert, or delete statement affected an unexpected > number of rows (0)

I am working on creating WCF restful Services and when I try adding a record via entity framework into my database I get this exception: Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. I have a trigger created at […]

Get records which are not in tables when passed in In clause?

I am sure that the title of the question need to get change , but not sure what to put from my end . I am passing In clause in two Tables want to get the records which are not in both the tables . Table A contains ID 1,2 Table B contains ID 3,4 […]

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