Tag: tsql

Can we make join between two stored procedures in SQL Server

I have this stored procedure: CREATE PROCEDURE [dbo].[TestPackageAccept] AS BEGIN SELECT A.Id, a.PackageNumber, a.Size, a.Code, a.TestPackageOrder, B.status as LineCheckState, B.ReportNumber as LineCheckReportNumber, B.SubmitDateTime as LineCheckSubmitDateTime, c.status as CleaningState,c.ReportNumber as CleanReportNumber, c.SubmitDateTime as CleanSubmitDateTime, c.status as ReInstatement, d.ReportNumber as ReInstatementReportNumber, d.SubmitDateTime as ReInstatementSubmitDateTime, E.status as Flushing, e.ReportNumber as FlushingReportNumber, e.SubmitDateTime as FlushingSubmitDateTime, f.status as Drying, f.ReportNumber […]

Implementing search criteria using JSP and SQL Server 2008?

I am trying to implement a search functionality using JSP and SQL Server. I am required to search using keywords such a phone number. But my lecturer wants me to implement the search functionality a bit differently. Let’s say – I have a phone number in the database 123456 and I key in the number […]

Error “must declare scalar variable” when using a in memory table in T-SQL

I am converting a varchar to xml and putting it into a in memory table so that I can query it, but I keep getting an error Must declare the scalar variable This is my code: Declare @templatexml table (CurrentTemplateXml XML Not Null) insert into @templatexml(CurrentTemplateXml) select convert(xml, convert(nvarchar(max), O.CurrentTemplateXml)) From BMObject O select * […]

SQL select if value higher save as column1, else save as column2

Is it possible to do something like this: IF datalength(U_Partner) > 5 THEN save value ‘9999’ into room_id and U_Partner value into partners_id IF datalength(U_Partner) <= 4 THEN save value U_Partner into room_id and ‘9999’ value into partners_id CASE WHEN datalength T5.U_Partner > 5 THEN ‘9999’ ELSE T5.U_Partner END as room_id, I have this, but […]

join rows on column (microsoff SSIS)

I have excel file that contains these informations Contry | 2000 | 2001 | 2002 |2003 ———————————- Tunisia | X1 | X2 | X3 |X4 ————————————- Algeria | X21 | X22 | X23 |X24 ————————————- And I want to store these information in database like this Tunisia 2000 X1 — Tunisia 2001 X2 — Tunisia […]

SQL Server – Join across several tables

Below are the relevant tables info: tgenie Guid | genietypeguid | … and : tgenieType guid | genietype | … I made this query to select all possible entries: select tsearch.description, tcompany.CompanyName, tsearch.CompanyGUID, tgenie.GenieNotes, tGenieType.GenieType from tcompany, tsearch, tGenieType left outer join tGenie on tgenie.GenieTypeGUID = tGenieType.GUID As the result I have this output: description […]

Is there a way to retrieve inserted identity as well as some values from the query in an INSERT SELECT?

I have a situation in which I need to insert some values from a query into a table that has an identity PK. For some of the records, I need also to insert values in another table which has a 1-to-1 (partial) relationship: CREATE TABLE A ( Id int identity primary key clustered, Somevalue varchar(100), […]

Joining 3 tables and removing nulls from 2nd and 3rd table in SQL Server

I would like to join 3 tables and remove null in second and third tables where records doesn’t match due to join. I kind of got the result but not able to remove null and hence why when I take this query into SSRS I am losing a record. Here are my 3 tables and […]

String manipulation in SQL

I’m using SQL server 2014.I have a column that has text data in it. The data would look like this: create table #temp ( stringdata varchar(100) NULL ) insert into #temp values (‘CN=ABCD,PN=XYZ,AD=123,AN=rst’), (‘AN=ABC,PN=XYZ,CN=12,AN=rst’), (‘AN=ABC,CN=XYZ,PN=123,AN=rst’), (‘AN=ABC,AN=XYZ,CN=1234567,PN=rst’) And I need the result set value that is mapped for CN. I tried to extract the data with […]

A way to not repeat expressions in a SQL Server string manipulation query

I have the following sql: SUBSTRING(Location, CHARINDEX(‘ : ‘, Location) + 3, CHARINDEX(‘ ms – ‘, Location) – (CHARINDEX(‘ : ‘, Location) + 3)) (Location could be something like this: 1dasev : 232 ms – 323-asv3R and the substring would return 232) If I wrote this in C# I would not re-calculate CHARINDEX(‘ : ‘, […]

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