Have a look at this DECLARE @Reviews TABLE( ReviewID INT, ReviewDate DATETIME ) DECLARE @Reviewers TABLE( ReviewerID INT, ReviewID INT, UserID INT ) DECLARE @Users TABLE( UserID INT, FName VARCHAR(50), LName VARCHAR(50) ) INSERT INTO @Reviews SELECT 1, ’12 Jan 2009′ INSERT INTO @Reviews SELECT 2, ’25 Jan 2009′ INSERT INTO @Users SELECT 1, ‘Bob’, … Read more
select (select p.ProblemType as ‘td’ for xml path(”), type), (select p.Onset as ‘td’ for xml path(”), type), (select p.DiagnosisStatus as ‘td’ for xml path(”), type) from tblProblemList p where p.PatientUnitNumber = @PatientUnitNumber for xml path(‘tr’) To add the header as well you can use union all. select (select ‘Problem’ as th for xml path(”), type), … Read more
This is just about the most bizarre query I’ve ever written, but I’m pretty sure it gets the “last 5” rows from a table without ordering: select * from issues where issueid not in ( select top ( (select count(*) from issues) – 5 ) issueid from issues ) Note that this makes use of … Read more
Best method in SQL Server 2016 Example: SELECT FORMAT(GETDATE(), ‘yyyy/MM/dd-HH:mm:ss’, ‘fa’) Answer: 1398/10/08-05:37:59
If you call the function without an argument they will both return the same value. But they do take different arguments: SUSER_SNAME() takes the varbinary(85) SID of a login as argument SUSER_NAME() takes the integer principal_id of a login You can verify this like: select suser_name(principal_id) , suser_name(sid) , suser_sname(principal_id) , suser_sname(sid) from sys.server_principals where … Read more
I assume this forum posting, which I quote fully below, should answer the question. Inside a procedure, function, or trigger definition, or in a dynamic SQL statement (embedded in a host program): BEGIN ATOMIC DECLARE example VARCHAR(15) ; SET example=”welcome” ; SELECT * FROM tablename WHERE column1 = example ; END or (in any environment): … Read more
I think what you want to do is create a linked server as per this webarchive snapshot of msdn article from 2015 or this article from learn.microsoft.com. You would then select using a 4 part object name eg: Select * From ServerName.DbName.SchemaName.TableName
OUTER APPLY: SELECT Stuff.id ,Results.pn ,Results.s FROM stackoverflow_454945 AS Stuff OUTER APPLY dbo.Split(‘,’, Stuff.myColumn) AS Results WHERE ISNULL(Results.s,”) <> ”
Look at this You can find an example DECLARE @data ObjectList INSERT @data (Id) VALUES (1) EXEC GetData @data
The SET needs to come before the FROM\JOIN\WHERE portion of the query. UPDATE CE SET sJobNumber = AD.JobNumber FROM CostEntry CE INNER JOIN ActiveCostDetails As AD ON CE.lUniqueID = AD.UniqueID WHERE CE.SEmployeeCode=”002″ AND SubString(CostCentre, 1, 1) = sDepartmentCode AND substring(CostCentre, 3, 1) = sCategoryCode AND substring(CostCentre, 5, 2) = sOperationCode