Why Does The Condition Activated = Isnull(@activated, -1) Work?
Jul 16, 2005
I have a stored procedure into which a bit type of parameter called '@activated' is being passed. This stored procedure gets all the users matching the condition :
Activated = isnull(@activated, -1)
In users table, one of the columns is 'Activated' of bit type.
The strange thing happening here is that if @activated is null, then the above condition is true for all rows in users table, even though we know that the column Activation is either 1 or 0 only and never -1.Can someone explain why this strange thing is happening?
View 2 Replies
ADVERTISEMENT
Mar 13, 2007
Hi There
I just had the following scenario.
I checked sys.service_queues to confirm the my queue has an activated proc assigned to it and is activation_enabled.
I send messages to the service, however i see that the messages just sit in the queue. I run profiler with all SB and Error events there is nothing, i check the sql server log, there is also nothing.
So i have no idea why the activated proc is not firing, all i see in the profiler trace is that the messages are acknowledged but the activated proc does not execute.
For a while i was stumped. i then tried to execute the proc myself and i got a syntax error.
Basically what happened was that i altered a table that the proc used and now the proc cannot execute cos there are more columns that must be inserted so i altered the proc.
However the activated proc still did not fire.
I had to disable and re-enable activation for it to work.
What i am wondering is why was there no indication in profiler or the sql og as the the fact that something was wrong with the activated proc ? Surely the activated proc should have fired and given the same error i got either to the sql log or profiler ?
And why did i have to re-enable activation ? Does sql cache the proc when you activate it and not know that the proc has changed when you alter it ?
Thanx
View 4 Replies
View Related
Sep 10, 2006
Hi there
I have multiple queues with the same activated stored procedure (for various reasons we are trying this scenario).
My biggest obsticle is i cannot figure out a way to determine with the activated sp which queue caused it to activate.
Basically i need to make the sp dynamic, so that no matter which queue activated the sp the sp can determine the queue name and use that dynamically to do the receive command from the right queue.
I am sure it is possible since sys.dm_broker_activated_tasks shows how many sp's are activated by each queue, however the sp name is the same for all queues so that does not help me.
How do i determine within an activated sp which queue caused it to activate?
Thanx
View 1 Replies
View Related
Nov 23, 2006
Hi guys!
Is it possible for a stored procedure to be activated more than once even if a message is sent only once? After sending the message, I check the sys.dm_broker_activated_tasks and see that it activates more than once. Is there a way for me to prevent this from happening?
Thanks a lot! :)
View 4 Replies
View Related
Nov 27, 2013
I have 3 tables, please find the table structure and sample data below
Table 1 : tblUserInfo
UserID | FirstName | Email | JoinedDate
1 Testuser01 User01@User.com 10-10-2013
2 Testuser10 User10@User.com 11-10-2013
3 Testuser20 User10@User.com 11-10-2013
Table 2 : tblOffers
OfferID | OfferName | ExpiryDate
1 OfferSample1 15-10-2014
2 OfferSample2 15-9-2014
3 OfferSample3 10-07-2014
Table 3 :tblOfferActivated
ActivationID | UserID | OfferID | ActivationCode | ActivatedDate
1 2 3 ABC 11-11-2013
2 2 1 CEG 13-11-2013
3 3 1 JHG 18-11-2013
4 3 2 KIU 20-11-2013
Expected Output
I want to list out the users with the first activated offer details.The OfferName Should be based on the first activated date
UserID | FirstName | Email | JoinedDate | OfferID | OfferName |ActivatedDate | ActivationCode
1 Testuser01 User01@User.com 10-10-2013 Null Null Null Null
2 Testuser10 User10@User.com 11-10-2013 3 OfferSample2 11-11-2013 ABC
3 Testuser20 User10@User.com 11-10-2013 4 offerSample3 18-11-2013 JHG
View 6 Replies
View Related
Apr 2, 2008
Hi everyone
I try to run Dynamic sql wherby sp_executesql as follows:
Code Snippet
DECLARE @params NVARCHAR(4000)
DECLARE @portion INT
SET @portion=6
DECLARE @mydynamic NVARCHAR(4000)
SELECT @mydynamic = ' SELECT TOP @portion * FROM server.database.dbo.mytable'
SELECT @params = N'@portion INT '
EXEC sp_executesql @mydynamic,@params, @portion
I get the following error message:
Code Snippet
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@portion'.
Any idea what is wrong with that code?Thanks
View 6 Replies
View Related
Aug 24, 2006
Hi There
I was wondering, i have experienced alot of stored procedure blocking where you have a stored procedure that get executed very frequently and is complex, when the sp has to recompile it is locked and cannot be executed by other processes.
How will this work with an activated stored procedure, our activated stored procedure will probably not be too complex but it may exec complex ones base on the message type.
Now lets say that a message is recieved by the activated sp he in turn executes a complex sp to process the message this causes the complex sp to recompile, during this time another activated sp is spawned by the queue with the same message type he execs the same complex sp that is busy recompiling, i presume that the second activated sp will be blocked until the sp has completed recompiling.
Is this correct, because on a very busy queue with very complex sp's being called by the activated stored procedure, the benefit of having multiple queue readers is negated by the blocking caused by the recompiling sp's called by the activated sp.
Basically no matter how many queue readers kick in to handle the messages they all wait for the sp to recompile from the first message.
I am hoping multiple spawned activated stored procedures somehow over come this? Or work differently somehow.
Does anyone know ?
Thanx
View 3 Replies
View Related
Jan 11, 2007
Hi
I'm having a problem debugging (with VS2005,) a CLR Stored Procedure which is activated on a queue. I know it is working because it is consuming and processing messages when one appears in the queue.
I can debug it 'manually' by either right-clicking on the SP within VS2005 and selecting 'Step into Stored Procedure' or by attaching to the SQL Server Management Studio process, setting a breakpoint and executing the stored procedure from a Management Studio query window.
However, if I send a message to my queue within Management Studio, my breakpoints are NOT being hit within VS2005, but the message IS reaching my queue and it IS being processed by the SP.
Can anyone help?
Thanks
View 3 Replies
View Related
Sep 4, 2007
Hi I have a couple of questions to understand better Service Broker..
Every time I alter the activation Stored Procedure on the receiving queue I also execute the:
alter database set new_broker rollback
1) can somebody explain me in a detailed way why I have to do that? what happen inside SQL Server when there is a Stored Procedure activated on a queue??
Also link to white papers, resouces on so on are appreciated.
My Activation Stored Procedure calls other stored procedure.
2) If ONLY one of the nested stored procedures changes I have to execute the command:
alter database set new_broker rollback ???
I think that the answer to the first question will answer the second one..
Thank you for any helps!
Marina B.
View 3 Replies
View Related
Apr 26, 2006
I've read Remus' article on Fire and Forget tactics when it comes to ending conversations, and I have to admit I am guilty of sending a message to a service and immediately ending the conversation. I've set up a stored procedure to end the conversation on the initiator queue, but I'm guessing it's never being fired because I am not ending the conversation in the target activated stored proc, which is a CLR stored proc.
Can you tell me how to end the conversation from a CLR stored proc?
Now that I think of it none of the code I use in my TSQL activated stored procs to handle different message types and error-checking is done in the CLR stored proc.
Thanks
View 5 Replies
View Related
Aug 2, 2006
Hi There
2 Questions :
1. Almost in every SB example you will see this sql :
BEGIN TRANSACTION
WAITFOR (
RECEIVE TOP (1)
@MessageType = message_type_name,
@Message = message_body
FROM [Queue1]
WHERE conversation_handle = @ConversationHandle
), timeout 5000;
If this sql in an activated sp do you really have to have the waitfor ? Since the sp will only be fired if there is a message on the queue ?
2. It is reccomended that for high volume SB apps you do not do a top(1) receive but process batches. Exactly what is the best practice to do this. Receive a batch into a table variable and then what ? Process through it with a cursor ? That is not very efficient either, i would just like some insight into batch queue processing as everywhere i have seen uses top (1) from the queue ?
Thanx
View 3 Replies
View Related
Oct 19, 2006
Hi everyone! Good day!
I'm not really sure if my question should fall on Service Broker or T-SQL, but I hope someone helps me with this... After activating the stored procedure assigned to the queue, is there any way for me to find out if the stored procedure is already finished executing?
I have successfully sent messages to my queue but I have no way to know if all the processing is already done.
Thanks so much!
View 4 Replies
View Related
Jun 29, 2015
I've data like below:
Now, I've to get active data for a particular date range. Let me explain the active data definition as below:
StartDate : 01-Jul-2015
EndDate : 31-Dec-2015
It should return all the data which was active for that date range even if it was only for one day.If no data found for that date range, check the last record before start date and and if its active then it should be returned else not.
I though of creating a function and pass primary key with date range and return the final status but that doesn't seems like an optimized query.
View 4 Replies
View Related
Oct 23, 2006
I have an activated SP that is issuing PRINT statements.
These are raised as ErrorLog and EventLog events when using SQLProfiler
However if you attach an event notification for ERRORLOG and EVENTLOG, nothing appears in the queue. Any other ERRORLOG or EVENTLOG event caught by profiler is caught by the event notification as expected.
View 2 Replies
View Related
Mar 7, 2008
Hi,
I am getting deadlock on activated procedure which I am using to receive message from the Service Broker Queue.
Deadlock details:
Two threads are tring to do delete on internal table queue_messages_122847900 ends up in a dead lock.
Activated procedure code
RECEIVE TOP(1) @xmlMessage = message_body,
@handle = conversation_handle,
@message_type = message_type_name
FROM TransactionQueue;
IF (@message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
END CONVERSATION @handle;
RETURN 0
END
.........................
.........................
After this I do process the message and some other processing
And then
END CONVERSATION @handle;
Note I do have single conversation group
Is their a problem in the way I am receiving and processing messages. Is it possible because of the delay between RECEIVE and END CONVERSATION same message is read by two different threads.
Thanks
View 1 Replies
View Related
Dec 1, 2006
I have a initiator and a target service broker peer.
Both are controlled by a C# unit test. The initiator uses the Microsoft.Samples.SqlServer class. The target service uses stored procedure activation.
Sending a message from the initiator to the target, saves the content of the message, along with its conversation handle in the target's database specific table.
The unit test needs - at a later time - to instruct the target to send a message back on the same conversation handle to the initiator service.
For this the C# unit test creates a Conversation off of the saved conversation handle:
Service client = new Service("cleintservicename", conn, tran);
Conversation dialog = null;
dialog = new Conversation(client, convHandle);
Sending the message on this dialog generates an error "Message body: <Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8495</Code><Description>The conversation has already been acknowledged by another instance of this service.</Description></Error>".
Is the error due to the fact that a service - using the activated stored procedure already picked up the conversation, so that a new reference to the service can not be created through the Service class in CLR?
If so, I might need then to skip the activated stored procedure in favor or a CLR service, alltogether?
Any help - greatly appreciated.
View 7 Replies
View Related
Jul 10, 2015
I am in assumption that we cannot do NULL value comparison in where clause directly (results will not be proper) we have to use ISNULL. Is my understanding correct? Is using ISNULL efficient when we have to this operation for large volume?
View 5 Replies
View Related
Dec 21, 2007
Hi All,
In the following query, although I try to restric data using FROM clause , still I get all the data without filtering. When I put it in where clause other where conditions also does not work.
Can anyone help me to fix this pls?
Code Block
SELECT
MoneyFlowTransaction.ScheduledSettlementDate, Member.LastName + ' ' + Member.FirstName AS MemberName, MoneyFlowTransaction.Amount, MoneyFlowTransaction.Direction, MoneyFlowTransaction.Status, EnumTransactionPurpose.UIText AS TransactionPurpose, CreditUnion.Name
FROM
MoneyFlowTransaction LEFT OUTER JOIN Member ON MoneyFlowTransaction.InfoMemberId = Member.Id LEFT OUTER JOIN EnumTransactionPurpose ON MoneyFlowTransaction.Purpose = EnumTransactionPurpose.Name LEFT OUTER JOIN CreditUnion ON MoneyFlowTransaction.InfoCreditUnionId = CreditUnion.Id AND (MoneyFlowTransaction.Purpose IN ( 'PartnerDepOriginationProceeds', 'PartnerDepRedemptionRequest' , 'PartnerHelpProceeds' ,'PartnerHelpRequest', 'PartnerLoanCollHelpIntProceeds', 'PartnerLoanCollHelpPriProceeds', 'PartnerLoanCollUserIntProceeds', 'PartnerLoanCollUserPriProceeds', 'PartnerLoanDisbursementRequest', 'PartnerLoanFeesProceeds'))
WHERE (CreditUnion.Id IN (@CreditUnionID)) AND (MoneyFlowTransaction.Status = 'Ready') OR
(MoneyFlowTransaction.Status = 'InProgress')
Thanks
View 2 Replies
View Related
Feb 9, 2006
For example..
select * from mytable where MyNum = 7
If this brings back more than 1 row, I want to display a message that says,
Print 'There is more than one row returned'
Else (If only 1 row returned), I don't want to print anything.
Can I do this? Thx!
View 1 Replies
View Related
Apr 19, 2007
Dear friends,
I'm having a problem... maybe it's very simple, but with soo many work, right now I can't think well...
I need to filter rows in a dataflow...
I created a condition spli to that... maybe there is a better solution...
And the condition is: Datex != NULL(DT_DATE)
(Some DATE != NULL)
[Eliminar Datex NULL [17090]] Error: The expression "Datex != NULL(DT_DATE)" on "output "Case 1" (17123)" evaluated to NULL, but the "component "Eliminar Datex NULL" (17090)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.
What is wrong??
Regards,
Pedro
View 4 Replies
View Related
Jun 22, 2015
I am trying to write an visibility function to have message shown based on two different IIF conditions:
If behavior is to Add a customer ( if message =NAME ALREADY EXISTS, return " NAME ALREADY EXISTS", otherwize return " NAME CREATED")If behavior is to Delete a customer (( if message =NAME DOES NOT EXIST, return "NAME DOES NOT EXIST", otherwize return "NAME SUCCESSFULLY DELETED")
I tried the following which doesn't work:
=IIF((UCase(First(Fields!Message.Value, "DataSetName")) = "NAME ALREADY EXISTS"), "WARNING: NAME ALREADY EXIST", "NAME CREATED"),
IIF((UCase(First(Fields!Message.Value, "DataSetName")) = " NAME DOES NOT EXIST"), "WARNING: NAME DOES NOT EXIST", " NAME DELETED")
View 6 Replies
View Related
Oct 17, 2015
I write a query to get some data as the following. but i need when a user check specified condition a query parameter change to specified condition :
create proc proc_ReservationDetails
(
@status nvarchar(50) = null
)
as
begin
select reservationId, reservationStatus, reservationDesc
[Code] .....
View 3 Replies
View Related
Aug 22, 2000
I am trying to delete a row of data that is all nulls. Help in BOL was a little help but it is not working.
View 2 Replies
View Related
Apr 30, 2008
I create a stored procedure like such:
"create procedure tmptable_query (@lname varchar(20)) as select * from #temp_table where lname like ISNULL(@lname+'%',lname)"
Unfortunately, it only returns exact matches, as if it were written as "where lname = ISNULL(@lname, lname)"
However, if I query from a static table the code works correctly.Any ideas on what could be wrong?
Thank you.
View 3 Replies
View Related
Feb 28, 2008
for the query i created i need zeros where ever the filed is blank. i have used count(acc) for selecting the count . can any one help me out with sample query. Thanks in advance
View 5 Replies
View Related
Jul 20, 2005
I have two tables, tblMTO and tblIMPORT_MTO. If I import an entire MTOinto the import table I want to create a delta from it (i.e. leave onlythe changed items). I have a view (simplified)SELECT dbo.tblIMPORT_MTO.ImportID, dbo.tblIMPORT_MTO.MTONo,dbo.tblIMPORT_MTO.Rev AS New_Rev, dbo.tblMTO.Rev AS Old_RevFROM dbo.tblIMPORT_MTO LEFT OUTER JOINdbo.tblMTO ON dbo.tblIMPORT_MTO.MTONo = dbo.tblMTO.MTONoNow to get all rows where old_rev = new_rev I also want all rows whereboth are null, is the best method to put ISNULL() in the view or toselect from the view using ISNULL in the criteria, e.g.select * from view1 where ISNULL(Old_Rev,0)=ISNULL(new_rev,0)or in the viewCREATE VIEW view1 asSELECT dbo.tblIMPORT_MTO.ImportID, dbo.tblIMPORT_MTO.MTONo,ISNULL(dbo.tblIMPORT_MTO.Rev,0) AS New_Rev, ISNULL(dbo.tblMTO.Rev ASOld_Rev,0)FROM dbo.tblIMPORT_MTO LEFT OUTER JOINdbo.tblMTO ON dbo.tblIMPORT_MTO.MTONo = dbo.tblMTO.MTONo;select * from view1 where Old_Rev=new_rev;
View 2 Replies
View Related
Apr 24, 2006
does sql server mobile 2005 support the isnull function? I'm getting an error when I try to use it and I don't know if it is becuase of using the isnull function or not, but when I run the same query on Sql Server 2005 it works fine.
View 5 Replies
View Related
Apr 29, 2008
I've seen lots of entries recommending the use of ISNULL in SQL WHERE clauses, e.g. in a search sproc where users can enter some or all parameters to search a table. Previously I would have used something like:SELECT * FROM MyTableWHERE (FName = @fname OR @fname IS NULL) AND(MName = @mname OR @mname IS NULL) AND(LName = @lname OR @lname IS NULL)So using the neat ISNULL syntax it could be updated to:SELECT * FROM MyTableWHERE (FName = ISNULL(@fname, FName)) AND(MName = ISNULL(@mname, MName)) AND(LName = ISNULL(@lname, LName))Having played around with this I stumbled upon a problem. If one of the fields, e.g. MName, is NULL then that clause will return false since MName = NULL isn't true and you have to use MName IS NULL. Did I miss all the caveats with using ISNULL in this way on fields that can contain NULL or have I missed something else?
View 4 Replies
View Related
May 1, 2008
I've got this query and it use to work or at least I thought it did. Could someone please help me with it.
Thank you
SELECT CID, CompletionDate, MarkedExport, CustomerName, EditUser, RouteID, WorkOrder FROM RouteCustomer WHERE (CompletionDate IS NOT NULL) AND (ExportDate IS NULL) AND (RouteID LIKE '%' + ISNULL(RouteID,@RouteID) + '%') AND (EditUser IS NULL OR EditUser = '' OR EditUser = @EmployeeID) AND (MONTH(CompletionDate) = ISNULL(MONTH(CompletionDate),@Month))
The problem comes with in the WHERE clause. What I wanted it to do is if the user did want to use a RouteID critera then the user would speified one else it wouldn't, and it was my belief that the ISNULL feature in SQL was the answer for that. same for the Month. I believe the EditUser is fine the way it is written.
thanks to anyone that can help me with this.
Rex
View 9 Replies
View Related
Jun 7, 2005
I have to display string "not assigined" when a datefield is null in a table.
I am using like ISNULL(datefiled, "not assigned"), but I am getting following error
Syntax error converting character string to smalldatetime data type.
Is there any way, I can acheive desired result.
Please help
View 1 Replies
View Related
Feb 1, 2015
To give you a little background, there is a CRM system with SQL server as its back-end. The CRM uses a view in SQL Server to list all the communications a user has had with his client over any given interval of time. Now there is a requirement to add a new column in the view that tells a user if the communication was filed in automatically or if it happened overnight via an automated archive manager process. I have achieved this using an expression field which is based on the comm_url field in the communications table in database.
example:
create view vCommunications
as
select col1, col2,...,case when comm_url is null then 'Manually filed' else 'Automatically Filed' as Filing
from Communications
alternatively, this can also be achieved by the following:
create view vCommunications
as
select col1, col2,...,isnull(comm_url, 'Manually Filed') as Filing
from Communications
Now my question is, given that there are many rows in the communications table, which of the above two expression fields will be more efficient in performance i.e. CASE versus ISNULL. I've checked a lot on google but I haven't been able to come up with a concrete answer.
View 3 Replies
View Related
Apr 9, 2008
Hi All,
Quick question...
why doesn't this work....
select IsNULL(select null),'TallOne')
But this does...
select IsNull((Select null),0)
How does it know what datatype I'm trying to get?
View 16 Replies
View Related
Apr 18, 2008
Hey, I'm taking an intro SQL Server class, and I have a pretty simple homework assignment. We were provided with a DB and asked to write several SELECT statements. However, I'm stuck up one of the questions. Here is the question: 12.Create a SELECT statement that displays all employees and their Qualifications. Display that individuals with no Qualifications as having ‘NoQual’. Hint: Use a function to determine this ‘empty’ field using ISNULL.
Here is what I have:
SELECT
FNAME + ' ' + LNAME AS 'Employee Name', ISNULL(QUALID, 'NoQual') AS 'Qualifications'
FROM
EMPLOYEE, QUALIFICATION
WHERE
EMPLOYEE.QUALID = QUALIFICATION.QUALID;
However, I do not get any results that have a NULL value in the QUALID column. Here is the code for the DB:
CREATE TABLE emplevel
(LevelNoint,
LowSalaryint,
HighSalaryint,
CONSTRAINT emplevel_levelno_pk PRIMARY KEY (LevelNo));
GO
CREATE TABLE position
(PositionIdint,
PosDescVARCHAR (10),
CONSTRAINT position_positionid_pk PRIMARY KEY (PositionId));
GO
CREATE TABLE qualification
(QualIdint,
QualDescVARCHAR (11),
CONSTRAINT qualification_qualid_pk PRIMARY KEY (QualId)
);
GO
CREATE TABLE dept
(DeptIdint,
DeptNameVARCHAR (12) ,
LocationVARCHAR (15),
EmployeeIdint,
CONSTRAINT dept_deptid_pk PRIMARY KEY (DeptId)
);
GO
CREATE TABLE employee
(EmployeeId int,
LnameVARCHAR (15) CONSTRAINT employee_lname_nn NOT NULL,
Fname VARCHAR (15) CONSTRAINT employee_fname_nn NOT NULL,
PositionId int,
Supervisorint,
HireDate DATETIME,
Salaryint,
Commissionint,
DeptIdint,
QualIdint,
CONSTRAINT employee_employeeid_pk
PRIMARY KEY (EmployeeId)
);
GO
CREATE TABLE dependent
(EmployeeId int,
DependentIdint,
DepDOBDATETIME,
RelationVARCHAR (8),
CONSTRAINT dependent_empiddepid_pk PRIMARY KEY (EmployeeId, DependentId)
);
GO
INSERT INTO position VALUES (1, 'President');
INSERT INTO position VALUES (2, 'Manager');
INSERT INTO position VALUES (3, 'Programmer');
INSERT INTO position VALUES (4, 'Accountant');
INSERT INTO position VALUES (5, 'Salesman');
INSERT INTO emplevel VALUES (1, 1, 25000);
INSERT INTO emplevel VALUES (2, 25001, 50000);
INSERT INTO emplevel VALUES (3, 50001, 100000);
INSERT INTO emplevel VALUES (4, 100001, 500000);
INSERT INTO qualification VALUES (1, 'Doctorate');
INSERT INTO qualification VALUES (2, 'Masters');
INSERT INTO qualification VALUES (3, 'Bachelors');
INSERT INTO qualification VALUES (4, 'Associates');
INSERT INTO qualification VALUES (5, 'High School');
INSERT INTO dept VALUES (10, 'Finance', 'Charlotte', 123);
INSERT INTO dept VALUES (20, 'InfoSys', 'New York', 543);
INSERT INTO dept VALUES (30, 'Sales', 'Woodbridge', 135);
INSERT INTO dept VALUES (40, 'Marketing', 'Los Angeles', 246);
INSERT INTO employee VALUES (111, 'Smith', 'John', 1, NULL,'04/15/1960', 265000, 35000, 10, 1);
INSERT INTO employee VALUES (246, 'Houston', 'Larry', 2, 111,'05/19/1967', 150000, 10000, 40, 2);
INSERT INTO employee VALUES (123, 'Roberts', 'Sandi', 2, 111,'12/02/1991',75000, NULL, 10, 2);
INSERT INTO employee VALUES (433, 'McCall', 'Alex', 3, 543,'05/10/1997',66500, NULL, 20, 4);
INSERT INTO employee VALUES (543, 'Dev', 'Derek', 2, 111,'03/15/1995',80000, 20000, 20, 1);
INSERT INTO employee VALUES (200, 'Shaw', 'Jinku', 5, 135,'01/03/00',24500, 3000, 30, NULL);
INSERT INTO employee VALUES (135, 'Garner', 'Stanley', 2, 111,'02/29/1996',45000, 5000, 30, 5);
INSERT INTO employee VALUES (222, 'Chen', 'Sunny', 4, 123,'08/15/1999',35000, NULL, 10, 3);
INSERT INTO dependent VALUES (543, 1,'09/28/1958','Spouse');
INSERT INTO dependent VALUES (543, 2,'10/14/1988','Son');
INSERT INTO dependent VALUES (200, 1,'06/10/1976','Spouse');
INSERT INTO dependent VALUES (222, 1,'02/04/1975','Spouse');
INSERT INTO dependent VALUES (222, 2,'08/23/1997','Son');
INSERT INTO dependent VALUES (222, 3,'07/10/1999','Daughter');
INSERT INTO dependent VALUES (111, 1,'12/12/1945','Spouse');
ALTER TABLE dept
ADD CONSTRAINT dept_employeeid_fk FOREIGN KEY(EmployeeId)
REFERENCES employee(EmployeeId);
GO
--ALTER TABLE employee
--ADD CONSTRAINT employee_supervisor_fk FOREIGN KEY(Supervisor)
--REFERENCES employee(EmployeeId);
ALTER TABLE employee ADD CONSTRAINT employee_positionid_fk FOREIGN KEY (PositionId)
REFERENCES position (PositionId);
GO
ALTER TABLE employee ADD CONSTRAINT employee_deptid_fk FOREIGN KEY (DeptId)
REFERENCES dept (DeptId);
GO
ALTER TABLE employee ADD CONSTRAINT employee_qualid_fk FOREIGN KEY (QualId)
REFERENCES qualification (QualId);
GO
ALTER TABLE dependent ADD CONSTRAINT dependent_employeeid_fk FOREIGN KEY (EmployeeId)
REFERENCES employee (EmployeeId);
GO
View 3 Replies
View Related
