Solved

Sql Dependable table Query

Posted on 2013-11-05
5
230 Views
Last Modified: 2014-01-21
There are 3 Tables
I just want to know How to data enter Dependable table

01. Supplier Table (Primary key v_s_code)
02. Product table (Primary key v_P_code)
03. Supplier_Product table ( Dependable table and Foreign keys v_s_code(Supplier Table),v_P_code(Product table))



I wrote following Stored Procedure for insert data


ALTER procedure [dbo].[addSuppliers]
@v_s_code	nvarchar(50),
@v_Supplier_Company	nvarchar(100),
@v_S_Address1	nvarchar(100)	,
@v_S_Address2	nvarchar(100)	,

@v_S_City	nvarchar(50)	,

@v_S_Country	nvarchar(50)	,

@v_S_Telephone	nvarchar(100)	,
@v_S_Email	nvarchar(100)	,
@v_s_Description	nvarchar(MAX),	
@v_P_code nvarchar(50),

@Results int OUTPUT 
AS
BEGIN
DECLARE @EnterDate DATETIME
DECLARE @c_P_Active CHAR(1)
SET @c_P_Active='Y'
SET @EnterDate=GETDATE()


SELECT @Results=(CASE WHEN COUNT(v_s_code)=1 THEN 1 ELSE 0 END )  from  Supplier where v_s_code=@v_s_code

if @Results=0
BEGIN
INSERT INTO Supplier (v_s_code,v_Supplier_Company,v_S_Address1,v_S_Address2,v_S_City,
v_S_Country,v_S_Telephone,v_S_Email,v_s_Description,d_EnterDate,c_P_Active) 
VALUES(@v_s_code,@v_Supplier_Company,@v_S_Address1,@v_S_Address2,@v_S_City,
@v_S_Country,@v_S_Telephone,@v_S_Email,@v_s_Description,@EnterDate,@c_P_Active)

INSERT INTO Products_Supplier(v_s_code,v_P_code)VALUES(@v_s_code,@v_P_code)

END


END;

Open in new window



I need to proper way to insert records into dependable table.this correct or if not can any one kindly show me what is the best way
0
Comment
Question by:ukerandi
5 Comments
 
LVL 10

Assisted Solution

by:HuaMinChen
HuaMinChen earned 250 total points
Comment Utility
You have to insert record to both product and supplier tables first, then you can insert records into Supplier_Product table.
0
 
LVL 10

Accepted Solution

by:
ukerandi earned 0 total points
Comment Utility
yes i already inserted Data to Product Table.

I just want to know What is the best way to Insert data to Dependable table,
any example much appriciate
0
 
LVL 9

Assisted Solution

by:rajeevnandanmishra
rajeevnandanmishra earned 250 total points
Comment Utility
Hi,

If you are getting all the given information together, then the approach used by you is good. But I suggest that you should be verifying the existence of @v_P_code in [Product Table] in similar way as you are checking in the [Supplier Table].
Once verified, then you should
1. Insert in Supplier Table (if not exists)
2. Insert in Product Table (if not exists)
3. Insert in Supplier_Product Table

I am not sure that what business logic you are following, but if there are possibility of calling the procedure with already entered record in [Supplier_Product] table, then you need to do a check before inserting in this table also.
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Curiosity overwhelms me .. what's an 'un-Dependable table'?
0
 
LVL 10

Author Closing Comment

by:ukerandi
Comment Utility
good
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
getting id from database 5 24
Date conversion in sql server 2012 6 25
search for a string in all tables 4 15
IN with @variable 5 16
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now