JOIN Efficiency Using Multiple ON Conditions Versus WHERE Conditions
Jan 10, 2008
My question is fairly simple. When I join between two tables, I always use the ON syntax. For example:
SELECT
*
FROM
Users
JOIN UserRoles
ON (Users.UserRoleId = UserRoles.UserRoleId)
No problems there. However, if I then decide to further filter the selection based on some trait of the UserRole, I have two options: I can add the condition as a WHERE statement, or I can add the condition within the ON block.
--Version 1:
SELECT
*
FROM
Users
JOIN UserRoles
ON (Users.UserRoleId = UserRoles.UserRoleId)
WHERE
UserRoles.Active = 'TRUE'
-- Version 2
SELECT
*
FROM
Users
JOIN UserRoles
ON (Users.UserRoleId = UserRoles.UserRoleId
AND UserRoles.Active = 'TRUE')
So, the question is, which is faster/better, if either? The Query Analyzer shows the two queries have the exact same execution plan, which makes sense, since they're both joining the same tables. However, I'm wondering if adding the condition in the ON statement results in fewer rows the JOIN statement initially needs to join up, thus reducing the overall initial size of the results table before the WHERE conditions are applied.
So is there a difference, performance wise? I imagine that if Users had a thousand records, and UserRoles had 10 records, then the JOIN would create a cartesian product of the two tables, resulting in 10,000 records in the table before the WHERE conditions are applied. However, if only three of the UserRoles is set to Active, would that mean that the resulting table, before applying WHERE conditions, would only contain 3000 records?
pls: 1/ can we do it this way: inner join Table2 ON table1.fld1=table2.fld21 AND table1.fld12=table2.fld22 2/also: what s the difference between join , iner join and left join Thanks .
I have three table For example Employee (Empid , Empname , Esal) Department (Deptid , Deptname , empid ) Staff (staffid , Staffname , Empid)
It is just example how can i update Empname whose staffid =1 accor to staffid) using Join Conditions :- Pls help me out .. or how to update data using JOIN Conditions
Dear All, I am not sure this is the correct forum for my issue, still im going on..... I have a sql query which uses left outer join and where clause select I.ir_id, I.ir_title,I. ir_b_type, I.ir_label, I.ir_createdby, I.ir_createdon, B.mstatus, B.bstatus from BR_IR I left outer join BR_BS Bon I.ir_id = B.bs_ir_id where I.ir_tr_rel = 'V03.03.06' order by I.ir_id desc this works perfect. Now I want to exclude few records from the result on condn 'bstatus <> 'Yes'. So now my query will be select I.ir_id, I.ir_title, I.ir_b_type, I.ir_label, I.ir_createdby, I.ir_createdon, B.mstatus, B.bstatus from BR_IR I left outer join BR_BS Bon I.ir_id = B.bs_ir_id where I.ir_tr_rel = 'V03.03.06' and B.bstatus <> 'Yes'order by I.ir_id desc The below query will not return the left table records which doesnt have a matching one in the right table. It works something like inner join. Could you please anyone tell me,where I am wrong? Thanks, Girija
I am using Transact-Sql 2005, and I'm trying to do a left outer join, including only certain accounts. The account number is x-xxx-xxxx-xxxx. I want to include only accounts where the last 4 digits are > 7149, and the first 5 digits are between 2-110 and 2-999, or are equal to 8-001.
This is my code, based on 2 temporary tables I have previously populated:
from #budgets b left outer join #transactions t on t.accountnumber=b.accountnumber And right(t.accountnumber,4) > 7149 and (left(t.accountnumber,5) between '2-110' and '2-999' or left(t.accountnumber,5)='8-001') order by b.accountnumber
I keep getting accounts with the last 4 digits > 7149, and also accounts whose numbers don't fall into the desired group of the first 5 digits.
I'm fairly new to SQL, and I'm thinking there must be a way to do what I want to do, but I don't know what it is.
Hi Faculties,I have two queries which give me the same output.-- Query 1SELECT prod.name, cat.nameFROM products prod INNER JOIN categories catON prod.category_id = cat.idWHERE cat.id = 1;-- Query 2SELECT prod.name, cat.nameFROM products prod INNER JOIN categories catON prod.category_id = cat.id AND cat.id = 1;The first query uses the WHERE clause and the second one has all theconditions in the ON clause. Is there anthing wrong with the secondapproach in terms of performance? Please suggest.Thanks in advanceJackal
I am facing a problem in writing the stored procedure for multiple search criteria.
I am trying to write the query in the Procedure as follows
Select * from Car where Price=@Price1 or Price=@price2 or Price=@price=3 and where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3 and where Model=@Model1 or Model=@Model2 or Model=@Model3 and where City=@City1 or City=@City2 or City=@City3
I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.
I have a SELECT Statement that I am using that is pulling from two tables. There won't always be results in the second table so I made a LEFT OUTER JOIN. The problem I am having is that I need to have three conditions in there:WHERE (employee.emp_id = @emp_id) AND (request.requested_time_taken = 'FALSE') AND (request.request_end_date >= GETDATE()))The two conditions from the request table are causing the entire query to return NULL as the value. I need help trying get a value whether or not there are any results in the request table.Here is the full select statement:SELECT (SELECT SUM(ISNULL(request.request_duration, '0')) AS Expr1 FROM employee LEFT OUTER JOIN request AS request ON employee.emp_id = request.emp_id WHERE (employee.emp_id = @emp_id) AND (request.requested_time_taken = 'FALSE') AND (request.request_end_date >= GETDATE())) AS dayspending FROM employee AS employee_1 LEFT OUTER JOIN request AS request_1 ON employee_1.emp_id = request_1.emp_id WHERE (employee_1.emp_id = @emp_id) GROUP BY employee_1.emp_id, employee_1.emp_begin_accrual, employee_1.emp_accrual_rate, employee_1.emp_fname, employee_1.emp_minitial, employee_1.emp_lname
Select DISTINCT p.dbPatID, p.dbpatfirstname, p.dbPatLastName, s.dbSchTypeCnt as SchDetailType, t.dbSchTypeCnt as SchTypeType, ISNULL(r.dbStatusDesc, 'No Ref') AS dbStatusDesc, ISNULL(t.dbSchTypeCode, 'No Ref') AS dbSchTypeCode, ISNULL(t.dbSchTypeDesc, 'No Ref') AS dbSchTypeDesc,
[code]....
however, I only want the lastest a.dbPatApptTime and only when a.dbPFStatus = 1 and a.ClientRef = 'EPS'
So the stand alone sql could be....
Select Top(1) dbPatApptTime as LastVisitDate, dbSchTypeDesc as LastVisitDesc from appointments where dbPFStatus = 1 and clientref = 'EPS' order by dbPatApptTime desc
I'm just not sure how to incorporate that into my sql or whether there is a better way,
Hi All,Just wondering if anyone can tell me if you can test for multipleconditions as part of an "IF" statement in T-SQL in SQL Server 2000?ie something like:IF @merr = 1 or @merr=2BeginSELECT statementUPDATE statementEndAny help much appreciated!Much warmth,M.
I've a table with a field named 'TypeOfProd' that has an ID for the various types of products: ex.: 1 - product A 2 - product B 3- product C 4 - product D .... 10 - product J and so on
I need to create a stored procedure that querys only the product types selected by the user.
The user can select 1, 3, 5, 10 or 1, 3 or 3 or 0 for all or some other combination.
For the first user selection a have something like this SELECT Prod FROM tableProd WHERE TypeOfProd = 1 OR TypeOfProd = 3 or TypeOfProd = 5 OR TypeOfProd = 10 For the second, SELECT Prod FROM tableProd WHERE TypeOfProd = 1 OR TypeOfProd = 3
Is it possible to have a stored procedures that runs a query with this random scenario?
Select c.Plan, c.ClaimNumber from tbFLags c inner join tbMembers m On c.Claim = m.HistoryNum where c.Plan = 'J318' and c.Paymon = c.Rmon and c.Rmon = '2008-03-01'
Now I want to add these into this statement, what should be done.
Members meeting any of the 3 sets of criteria should not be selected
1) tbFlags.Hosp='1'
2) tbFlags.RD='1' OR tbCMSFlags.RAType in ('D', 'I2')
I am have a dimension to use as a slicer, I am trying to associate that dimension with a measure group using a calculated measure. I am using the below query:
I have a ListBox controls that contains about 5 items. A stored procedure is executed based on selections of other controls ont he screen, but I cann't figure out how to properly get the dynamically selected conditions passed to the sproc from C#.
If a user selects 3 of the five items, the sproc needs to build something like this:
WHERE Region LIKE Item1 OR Region LIKE Item2 OR Region LIKE Item3
I cannot figure out how to do this. It works properly if I just make the ListBox SelectionMode as Single and pass that one selected item's value.
I am attempting to explain my probelm again. Please read it:
I have 3 tables. CallDetail, Call and Request. The tables are populated in the following order: One row for CallDetail, One for Call and one for Request and so on.
I have to generate a UniqueNo - Per empid, Per StateNo, Per CityNo, Per CallType. The no will remain same for the same CallDetailID and ordered by the date created. However if the CallDetailId changes, the no. will increment based on the empid, Per StateNo, Per CityNo, Per CallType
For eg:
For Eg: ( Assume Call Detail id is changing for all the days) Monday - 3 calls made for empid 1, state SA023, city 12 and call type 1 will generate a unique id 1 for all 3 calls Tuesday - 2 calls made for empid 1, state SA023, city 12 and call type 1 will generate a unique id 2 for both calls Wednesday - 3 calls made for emp id 1, state SA023, city 12 and call type 2 will generate a unique id 1 for 3 calls as the call type is different than the previous day for same employee Thursday - 2 calls made for empid 2, state SA023, city 13 and call type 1 will generate unique id 1 for both the calls as combi of city and call type are different.
So the unique id has to be generated considering empid, state, city and call type, ordered by the EntryDt. EntryDt is needed because : 3 calls made for empid 1, state SA023, city 12 and call type 1 at 10/11/2007 10.00 AM will generate a unique id 1 for all 3 calls 2 calls made for empid 1, state SA023, city 12 and call type 1 at 10/11/2007 12.00 AM will generate a unique id 2 as the call was registered later.
Here is what I wrote with the help of a mod over here:
INSERT @Request SELECT '324234', 'Jack', 'SA023', 12, 111, Null UNION ALL SELECT '223452', 'Tom', 'SA023', 12, 112, Null UNION ALL SELECT '456456', 'Bobby', 'SA023', 12, 114, Null UNION ALL SELECT '22322362', 'Guck', 'SA023', 12, 123, Null UNION ALL SELECT '22654392', 'Luck', 'SA023', 12, 134, Null UNION ALL SELECT '225652', 'Jim', 'SA023', 12, 143, Null UNION ALL SELECT '126756', 'Jasm', 'SA023', 12, 145, Null UNION ALL SELECT '786234', 'Chuck', 'SA023', 12, 154, Null UNION ALL SELECT '66234', 'Mutuk', 'SA023', 12, 185, Null UNION ALL SELECT '2232362', 'Buck', 'SA023', 12, 195, Null
DECLARE @Call TABLE(CallID INT, CallType INT, CallDetailID INT) INSERT @Call SELECT 111, 1, 12123 UNION ALL SELECT 112, 1, 12123 UNION ALL SELECT 114, 1, 12123 UNION ALL SELECT 123, 2, 12123 UNION ALL SELECT 134, 2, 12123 UNION ALL SELECT 143, 1, 6532 UNION ALL SELECT 145, 1, 6532 UNION ALL SELECT 154, 1, 6532 UNION ALL SELECT 185, 2, 6532 UNION ALL SELECT 195, 3, 6532
-- Query written with help of a helpful person here UPDATE r SET r.UniqueNo = dt.CallGroup FROM @Request r JOIN @Call c ON r.CallID = c.CallID JOIN (SELECT CallDetailID, EntryDt,EmpID, CallGroup = ROW_NUMBER() OVER (ORDER BY EntryDt ) FROM @CallDetail ) dt ON c.CallDetailID = dt.CallDetailID select * from @Request
as the call for Buck is of calltype 3 which was not done earlier. So the no starts from 1.
Also how to add the paritioning by empid, StateNo, Per CityNo, Per CallType and yet maintain the same unique no for the same calldetailid. Eg: CallGroup = ROW_NUMBER() OVER (PARTITION BY empid, state, city, calltype ORDER BY EntryDt )
This shows a banking history of transactions and includes a field called TransType and a field called PaymentID.
I also have two other tables called Suppliers and SubContractors.
For each record in the bank, I need to match up a record in either the suppliers or subcontractors tbl based on the PaymentID value. I know if the record relates to either a Supplier or Subcontractor based on the value of the TransType field which will be either SUPPLIER or SUBCONTRACTOR or Null (in which case a match doesn't matter)
I have a working query based on joining just the Supplier tbl.. but how do I do the join to the other tbl aswell?
So overall, for each record in the bank, if the transtype is SUPPLIER I need to look in the supplier tbl for a match for that paymentID, and if the transtype is SUBCONTRACTOR, I need to do the same but in SUBCONTRACTOR tbl.
I have a record that I want to insert into (2) tables. The first thing I want to do is see if a record already exists in the table for the user, if it does - I just want to skip over the insert.
next I want to do the same thing in the SW_REQUEST table. If there is a record in there for the member, I want to just skip the insert.
My code works as long as there isn't an existing record in the tables. Can someone give me a hand?
Here's what I have (and it doesn't work)
CREATE PROCEDURE b4b_sw_request
@FName as varchar(50)= NULL, @LName as varchar(50)=NULL, @Address1 as varchar(100) = NULL, @Address2 as varchar(100) = NULL, @City as varchar(50) = NULL, @State as char(2) = NULL, @Zip as char(5) = NULL, @Email as varchar(100) = NULL, @Send_Updates as smallint = '0'
AS
IF EXISTS (SELECT FName, LName, Address1, Zip from MEMBERS WHERE FName = @FName AND LName = @LName AND Zip = @Zip) BEGIN RETURN END
ELSE BEGIN INSERT INTO MEMBERS (FName, LName, Address1, Address2, City, State, Zip, Email) Values (@FName, @LName, @Address1, @Address2, @City, @State, @Zip, @Email) END
IF EXISTS (SELECT MEMBER_ID FROM SW_REQUESTS WHERE MEMBER_ID = @@Identity) BEGIN RETURN END
ELSE BEGIN INSERT INTO SW_REQUESTS (MEMBER_ID, Send_Updates) Values (@@Identity, @Send_Updates) END GO
I have following query. Now I want to change this in such a way that if Condition 1 satisfies then it should not go to next condition ,if not then should go to next. Similarly the same format till condition 4.
select * from table where IND = 'Y' and ( (STATE = @STATE and LOB =@LOB and COMPANY=@COMPANY)--Condition 1 OR (STATE = @STATE and LOB =@LOB)--Condition 2 OR (STATE = @STATE and COMPANY=@COMPANY)--Condition 3 OR (STATE = @STATE)--Condition 4 )
I have a table with the following fields and data:
Offense, Year, Month, Count, Amount F 2007 January 11 $49,238.00 F 2007 February 12 $24,000.00 F 2007 March 31 $55,184.00 F 2007 April 19 $64,647.00 F 2007 May 33 $100,010.00 F 2007 June 16 $59,678.00 F 2007 July 22 $39,700.00 F 2007 August 3 $9,000.00 F 2007 September 4 $75,000.00 F 2007 October 8 $19,250.00 F 2007 November 50 $106,153.00 F 2007 December 26 $80,000.00
I have data for years 2007-2014. I am designing an SSRS report, and want to use IIF statement to return data. Using the following:
=IIF(Fields!Year.Value="2007" AND Fields!Month.Value="February", "return value of count field", "n/a")
The above does not work, it doesn't return the value of Count field, it does return the n/a.
This is an exact matching record and straight forward
Is it possible to identify the record using T SQL query based on the following scenarios
1) return the record - If all the three where conditions match 2) if record not found check and return the record where 2 columns values in the where condition match
-- Expected Result for below query: 'Orange', because 2 of the columns in where condition have matching values
SELECT Productname FROM @MappingTable where identification_key1=1 or identification_key2 =2 or identification_key3 =1
Hi, I have 1 table with 5 rows. One of the rows has dateTime values. I want to know how many rows there are with a value in that column < today AND how many rows there are with a value in that column > today. I'm not sure how to do this.
SELECT Count(*) WHERE dateColumn <= today AND dateColumn > today gives me everything. I like to end up with a column containing the count of rows <= today and a column with rows where date > today.
Is this possible in SQL or do I have to retrieve all rows and then loop over the resultset and check each row?
My goal is to create a trigger to automatically set the value for a status id on a table based on set criteria. Depending on the values of other fields will determine the value that the status id field is assigned. So far, I have written the trigger to update the status id field and I go through a seperate Update statement for each status id value. The problem is that I can't get this to work at the record level. The problem that I am getting is that if I have 50 records in TABLE1 and at least one of them satisfies the where clause of the update statement, all of the records get updated. So, using these two update statements, all of my records end up with a status value of '24' because that was the last update statement run in the trigger. Here is the code I have so far:
CREATE TRIGGER dbo.JulieTrigger1
ON dbo.Table1
AFTER INSERT,UPDATE
AS
BEGIN
BEGIN TRY
/*Update Table1.Status to POTENTIAL (id 23) status */
UPDATE TABLE1
SET status_id = 23
WHERE EXISTS (SELECT *
FROM TABLE1 a INNER JOIN TABLE2 b
ON b.order_id = a.order_id
WHERE a.start_dt IS NULL
AND b.current_status_ind = 1
AND b.lead_status_id NOT IN (15,16)
AND a.order_id = TABLE1.order_id)
/*Update Table1.Status to ACTIVE (id 24) status */
Have a nvarchar column in ms sql that, based on user input, may or may not have a carriage return and a line break together (CHR(13)&CHR(10)), just a carriage return (CHR(13)), just a line break (CHR(10)). In using that column in ssrs, I need to find if any of the above exist and replace them with the string "x0Dx0A", "x0D", or "x0A" respectively.
System.Web.Services.Protocols.SoapException: The Value expression for the textrun ‘Info.Paragraphs[0].TextRuns[0]’ contains an error: [BC30455] Argument not specified for parameter 'Replacement' of 'Public Function Replace(Expression As String, Find As String,
I have a table CREATE TABLE [dbo].[CmnLanguage]( [Id] [char](2) NOT NULL CONSTRAINT PkCmnLanguage_Id PRIMARY KEY, [EnglishName] [varchar](26) NOT NULL, [NativeName] [nvarchar](26) NOT NULL, [DirectionType] [smallint] NOT NULL, [IsVisible] [bit] NOT NULL, [CreatedDateTime] [datetime] NOT NULL DEFAULT GETDATE(), [ModifiedDateTime] [datetime] NULL) We will use these 3 queries select * from CmnLanguage where IsVisible = 0select * from CmnLanguage where IsVisible = 1select * from CmnLanguage I want to make a method which handles these queries. But at the back end on Stored Procedures We have to write 3 queries Which I don't want to do. I want to minimize the queries and conditions and want to just write one for these 3 Can any one do it?
I'm having some real problems using the OR condition in a very simple SQL statement and could use your help or insight on where the problem lies, or perhaps a workaround.
I have a large flat table in a SQL 7 database with 10 million + records called "HISTORY". I have not installed either service pack 1 or 2. I'm attempting to run a query that references the following four fields which are all non-clustered keys:
EQUIPMENT_NO TEXT 12 CHASSIS_IN TEXT 12 CHASSIS TEXT 12 SVC_DATE_TIME SMALLDATETIME
Here's the SQL statement:
SELECT * FROM HISTORY WHERE (HISTORY.EQUIPMENT_NO = 'XYZ123' OR HISTORY.CHASSIS = 'XYZ123' OR HISTORY.CHASSIS_IN = 'XYZ123') AND SVC_DATE_TIME >= '01/15/00 00:00:00 AM' AND SVC_DATE_TIME <= '02/28/00 23:59:59 PM' ORDER BY EQUIPMENT_NO
This query takes 11 min. 5 sec. inder the Query Analyzer and ultimately returns the 31 desired records.
If you remove the SVC_DATE_TIME criteria, about 350 records are returned in a matter of seconds. I've also tried variations on the date syntax such as '01/15/00', etc. with no change in the amount of time to execute.
Other queries such as a simple AND condition combining EQUIPMENT_NO and SVC_DATE_TIME are snappy.
Are there known problems/bugs with "OR" conditions in queries that anyone is aware of, particularly with parentheses; am I composing this query incorrectly? Is there some alternate syntax that would work as expected? I can't see where the query shouldn't execute quickly as expected, particularly with all indexed fields involved. I'm stumped! Lend me your expertise. Thanks much.
Clark R. Farabaugh, Jr. Financial Systems Analyst VIT Norfolk, VA
I have a table called sample and i have the following requirement. i.e i need sum(credit) group by ssn no.
One special condition is as follows:
For each distinct ssn if "flag" has the same CX value,then out of all the records with the same CX value, the highest "credit" value is added to the sum for that "ssn" and the rest are ignored. If while adding "credit" to the sum and if "credit" value is equal to zero then "sum" value is used for summing else "credit" value is used. Can any one help me out in trying this logic. I have tried but i could'nt able embed the conditions inbetween the Sql statetment.
Code: Drop table #table Drop table #table_with_groupid -- Prepare test data CREATE TABLE #table ([Admissions_key] bigint NOT NULL PRIMARY KEY, MRN nvarchar(10) NOT NULL,
[Code] ....
How can I compare dates with conditions. I only want to Mark C where the difference between adm_datetime and prevsep_datetime is <= 1 otherwise E as well
where datediff(MINUTE,tg.adm_datetime,tg.pre_sep_date)< =1 ??
is it correct ? where should I put this to implement correctly ?
MAX(Vernum) AS test, MAX(Case WHEN A2.AttrID = 2 AND A2.DefID = 10057945 THEN A2.ValStr END) AS TYPE_DOCUMENT
This works not perfect. In many cases I have more then one row and my query takes the max value of column Valstr. Thats is not exactly what I want. I'd like to have the value of Column Valstr of the row where column Vernum has the maximum value.
I've tried many things like:
MAX(Vernum) AS test, MAX(Case WHEN A2.AttrID = 2 AND A2.DefID = 10057945 AND A2.Vernum=test THEN A2.ValStr END) AS TYPE_DOCUMENT
OR
MAX(Vernum) AS test, MAX(Case WHEN A2.AttrID = 2 AND A2.DefID = 10057945 AND A2.Vernum=MAX(A2.Vernum) THEN A2.ValStr END) AS TYPE_DOCUMENT
IF NOT EXISTS(SELECT * FROM CPA WHERE PrimaryEmail=@PrimaryEmail or PrimaryEmail=@SecondaryEmail or SecondaryEmail=@PrimaryEmail or SecondaryEmail=@SecondaryEmail ) BEGIN --select * from o_login Begin transaction InsCPA
INSERT INTO CPA(SupervisorId,BookmarkerId,PreparerId,FirmNo,FirmName,FirstName,MiddleName,LastName,TaxYear,TaxSoftware,HomePhone,WorkPhone,Fax,PrimaryEmail,SecondaryEmail,CountryId,State,Zipcode,Status) VALUES(@SupervisorId,@BookmarkerId,@PreparerId,@FirmNo,@FirmName,@FirstName,@MiddleName,@LastName,@TaxYear,@TaxSoftware,@HomePhone,@WorkPhone,@Fax,@PrimaryEmail,@SecondaryEmail,@CountryId,@State,@Zipcode,@Status)
--Error handling IF @@ERROR <> 0 BEGIN -- Returns 0 to the calling program to indicate failure. ROLLBACK TRAN InsCPA SET @RESULT = 0 END ELSE BEGIN --DECLARE @ID1 INTEGER -- Returns 1 to the calling program to indicate success. COMMIT TRAN InsCPA SET @RESULT = 1 END END ELSE BEGIN -- Return 2 to the calling program to indicate record already exists. set @RESULT = 2 END END
ELSE IF(@Operator='U') BEGIN declare @pemail as varchar(30) declare @semail as varchar(30) declare @firm as varchar(20) select @pemail=PrimaryEmail,@semail=SecondaryEmail,@firm=FirmNo from CPA WHERE Id = @Id --select * from CPA
if(@pemail=@PrimaryEmail) or(@semail=@PrimaryEmail)--or((@semail=@SecondaryEmail)and (@semail=@PrimaryEmail))) begin
print 'prim1' if(@semail=@SecondaryEmail)or (@pemail=@SecondaryEmail) begin print 'sec1' if(@firm=@FirmNo) begin print'firm' BEGIN TRANSACTION UpdateCPA UPDATE CPA SET SupervisorId=@SupervisorId, BookmarkerId=@BookmarkerId, PreparerId=@PreparerId, FirmNo=@FirmNo, FirmName=@FirmName, FirstName=@FirstName, MiddleName=@MiddleName, LastName=@LastName, TaxYear=@TaxYear, TaxSoftware=@TaxSoftware, HomePhone=@HomePhone, WorkPhone=@WorkPhone, Fax=@Fax, PrimaryEmail=@PrimaryEmail, SecondaryEmail=@SecondaryEmail, CountryId=@CountryId, State=@State, Zipcode=@Zipcode, Status=@Status WHERE Id = @Id UPDATE EMPLOYEE SET FirmNo=@FirmNo WHERE FirmNo=@firm
IF @@ERROR <> 0 BEGIN -- Returns 0 to the calling program to indicate failure. ROLLBACK TRAN UpdateCPA SET @RESULT = 0
END ELSE BEGIN -- Returns 1 to the calling program to indicate success. print'1' COMMIT TRAN UpdateCPA SET @RESULT = 1
END END else begin IF NOT EXISTS(SELECT * FROM CPA WHERE FirmNo=@FirmNo) BEGIN print'fd' BEGIN TRANSACTION UpdateCPA UPDATE CPA SET SupervisorId=@SupervisorId, BookmarkerId=@BookmarkerId, PreparerId=@PreparerId, FirmNo=@FirmNo, FirmName=@FirmName, FirstName=@FirstName, MiddleName=@MiddleName, LastName=@LastName, TaxYear=@TaxYear, TaxSoftware=@TaxSoftware, HomePhone=@HomePhone, WorkPhone=@WorkPhone, Fax=@Fax, PrimaryEmail=@PrimaryEmail, SecondaryEmail=@SecondaryEmail, CountryId=@CountryId, State=@State, Zipcode=@Zipcode, Status=@Status WHERE Id = @Id UPDATE EMPLOYEE SET FirmNo=@FirmNo WHERE FirmNo=@firm IF @@ERROR <> 0 BEGIN -- Returns 0 to the calling program to indicate failure. ROLLBACK TRAN UpdateCPA SET @RESULT = 0
END ELSE BEGIN -- Returns 1 to the calling program to indicate success. print'1' COMMIT TRAN UpdateCPA SET @RESULT = 1
END end ELSE BEGIN -- Returns 1 to the calling program to indicate success. print'4' --COMMIT TRAN UpdateCPA SET @RESULT = 4
END end end else begin IF NOT EXISTS(SELECT * FROM CPA WHERE PrimaryEmail=@SecondaryEmail or SecondaryEmail=@SecondaryEmail) BEGIN if(@firm=@FirmNo) begin BEGIN TRANSACTION UpdateCPA UPDATE CPA SET SupervisorId=@SupervisorId, BookmarkerId=@BookmarkerId, PreparerId=@PreparerId, FirmNo=@FirmNo, FirmName=@FirmName, FirstName=@FirstName, MiddleName=@MiddleName, LastName=@LastName, TaxYear=@TaxYear, TaxSoftware=@TaxSoftware, HomePhone=@HomePhone, WorkPhone=@WorkPhone, Fax=@Fax, PrimaryEmail=@PrimaryEmail, SecondaryEmail=@SecondaryEmail, CountryId=@CountryId, State=@State, Zipcode=@Zipcode, Status=@Status WHERE Id = @Id UPDATE EMPLOYEE SET FirmNo=@FirmNo WHERE FirmNo=@firm
IF @@ERROR <> 0 BEGIN -- Returns 0 to the calling program to indicate failure. ROLLBACK TRAN UpdateCPA SET @RESULT = 0
END ELSE BEGIN -- Returns 1 to the calling program to indicate success. print'1' COMMIT TRAN UpdateCPA SET @RESULT = 1
END END else begin IF NOT EXISTS(SELECT * FROM CPA WHERE FirmNo=@FirmNo) BEGIN BEGIN TRANSACTION UpdateCPA UPDATE CPA SET SupervisorId=@SupervisorId, BookmarkerId=@BookmarkerId, PreparerId=@PreparerId, FirmNo=@FirmNo, FirmName=@FirmName, FirstName=@FirstName, MiddleName=@MiddleName, LastName=@LastName, TaxYear=@TaxYear, TaxSoftware=@TaxSoftware, HomePhone=@HomePhone, WorkPhone=@WorkPhone, Fax=@Fax, PrimaryEmail=@PrimaryEmail, SecondaryEmail=@SecondaryEmail, CountryId=@CountryId, State=@State, Zipcode=@Zipcode, Status=@Status WHERE Id = @Id UPDATE EMPLOYEE SET FirmNo=@FirmNo WHERE FirmNo=@firm IF @@ERROR <> 0 BEGIN -- Returns 0 to the calling program to indicate failure. ROLLBACK TRAN UpdateCPA SET @RESULT = 0
END ELSE BEGIN -- Returns 1 to the calling program to indicate success. print'1' COMMIT TRAN UpdateCPA SET @RESULT = 1
END end ELSE BEGIN -- Returns 1 to the calling program to indicate success. print'44' --COMMIT TRAN UpdateCPA SET @RESULT = 4
END end /* --select * from o_login Begin transaction InsCPA
UPDATE CPA SET SupervisorId=@SupervisorId, BookmarkerId=@BookmarkerId, PreparerId=@PreparerId, FirmNo=@FirmNo, FirmName=@FirmName, FirstName=@FirstName, MiddleName=@MiddleName, LastName=@LastName, TaxYear=@TaxYear, TaxSoftware=@TaxSoftware, HomePhone=@HomePhone, WorkPhone=@WorkPhone, Fax=@Fax, PrimaryEmail=@PrimaryEmail, SecondaryEmail=@SecondaryEmail, CountryId=@CountryId, State=@State, Zipcode=@Zipcode, Status=@Status WHERE Id = @Id --Error handling IF @@ERROR <> 0 BEGIN -- Returns 0 to the calling program to indicate failure. ROLLBACK TRAN InsCPA SET @RESULT = 0 END ELSE BEGIN --DECLARE @ID1 INTEGER -- Returns 1 to the calling program to indicate success. print'11' COMMIT TRAN InsCPA SET @RESULT = 1 END*/ END ELSE BEGIN print 'sec same' -- Return 2 to the calling program to indicate record already exists. set @RESULT = 3 END end end else begin IF NOT EXISTS(SELECT * FROM CPA WHERE PrimaryEmail=@PrimaryEmail or SecondaryEmail=@PrimaryEmail) BEGIN /*--select * from o_login Begin transaction InsCPA
UPDATE CPA SET SupervisorId=@SupervisorId, BookmarkerId=@BookmarkerId, PreparerId=@PreparerId, FirmNo=@FirmNo, FirmName=@FirmName, FirstName=@FirstName, MiddleName=@MiddleName, LastName=@LastName, TaxYear=@TaxYear, TaxSoftware=@TaxSoftware, HomePhone=@HomePhone, WorkPhone=@WorkPhone, Fax=@Fax, PrimaryEmail=@PrimaryEmail, SecondaryEmail=@SecondaryEmail, CountryId=@CountryId, State=@State, Zipcode=@Zipcode, Status=@Status WHERE Id = @Id --Error handling IF @@ERROR <> 0 BEGIN -- Returns 0 to the calling program to indicate failure. ROLLBACK TRAN InsCPA SET @RESULT = 0 END ELSE BEGIN --DECLARE @ID1 INTEGER -- Returns 1 to the calling program to indicate success. print'111' COMMIT TRAN InsCPA SET @RESULT = 1 END*/ if(@firm=@FirmNo) begin BEGIN TRANSACTION UpdateCPA UPDATE CPA SET SupervisorId=@SupervisorId, BookmarkerId=@BookmarkerId, PreparerId=@PreparerId, FirmNo=@FirmNo, FirmName=@FirmName, FirstName=@FirstName, MiddleName=@MiddleName, LastName=@LastName, TaxYear=@TaxYear, TaxSoftware=@TaxSoftware, HomePhone=@HomePhone, WorkPhone=@WorkPhone, Fax=@Fax, PrimaryEmail=@PrimaryEmail, SecondaryEmail=@SecondaryEmail, CountryId=@CountryId, State=@State, Zipcode=@Zipcode, Status=@Status WHERE Id = @Id
UPDATE EMPLOYEE SET FirmNo=@FirmNo WHERE FirmNo=@firm
IF @@ERROR <> 0 BEGIN -- Returns 0 to the calling program to indicate failure. ROLLBACK TRAN UpdateCPA SET @RESULT = 0
END ELSE BEGIN -- Returns 1 to the calling program to indicate success. print'1' COMMIT TRAN UpdateCPA SET @RESULT = 1
END END else begin IF NOT EXISTS(SELECT * FROM CPA WHERE FirmNo=@FirmNo) BEGIN BEGIN TRANSACTION UpdateCPA UPDATE CPA SET SupervisorId=@SupervisorId, BookmarkerId=@BookmarkerId, PreparerId=@PreparerId, FirmNo=@FirmNo, FirmName=@FirmName, FirstName=@FirstName, MiddleName=@MiddleName, LastName=@LastName, TaxYear=@TaxYear, TaxSoftware=@TaxSoftware, HomePhone=@HomePhone, WorkPhone=@WorkPhone, Fax=@Fax, PrimaryEmail=@PrimaryEmail, SecondaryEmail=@SecondaryEmail, CountryId=@CountryId, State=@State, Zipcode=@Zipcode, Status=@Status WHERE Id = @Id
UPDATE EMPLOYEE SET FirmNo=@FirmNo WHERE FirmNo=@firm IF @@ERROR <> 0 BEGIN -- Returns 0 to the calling program to indicate failure. ROLLBACK TRAN UpdateCPA SET @RESULT = 0
END ELSE BEGIN -- Returns 1 to the calling program to indicate success. print'1' COMMIT TRAN UpdateCPA SET @RESULT = 1
END end ELSE BEGIN -- Returns 1 to the calling program to indicate success. print'2' --COMMIT TRAN UpdateCPA SET @RESULT = 2
END end END ELSE BEGIN print 'prim same' -- Return 2 to the calling program to indicate record already exists. set @RESULT = 2 END end end
Above procedure has many if else conditions Is there any way to write procs other than this process
1. Are stored procedures WITH ENCRYPTION slower than the ones withoutencryption?2. Should i put most restrictive conditions first or last in WHERE? Inwhich order does MSSQL execute conditions? Or MSSQL determents whatwould be best and does not bother with the way i sorted conditions?for example:SELECT *FROM [users]WHERE[user_id] = 1 AND[baned] = 0Is "[user_id] = 1" or "[baned] = 0" going to be executed first?
