Iam writing a stored procedure to insert data into table.how can we write condition oninserting data with unique id depending on another id

Question

0.00/5 (No votes)

See more:

I have 2 have tables.
one is masterdatamodel and one is masterdataattr

in masterdatamdoel i have masterdatamodelid and mastertype.
masterdataattr i have field like masterdatamodelid,mastertype,attorder,attname,atttype

i should insert new data for masterdatamodelid.
suppose for masterdatamodelid data is 1
masterdataattr data ismastermodelid =1 attorder=1 attname="test" atttype="test123"
attorder=2 attname="test2" atttype="test1234"
mastermodelid=2 attorder=1 attname="test" attype="test123"
attorder=2 attname="test" attype="test1234"

so i have to give here condition.

SQL

ALTER PROCEDURE CORE.SP_InsertMasterModelATTR 
	(
	   @MasterModelId int
	  ,@AttributeOrder int
      ,@AttributeName char(50)
      ,@AttributeType char(10)
      ,@MasterType    char(50)
      ,@Required      char(1)
      ,@ValueUsedAsKey char(1)
      ,@DefaultValue   char(100)
	)
AS
BEGIN
	
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	Insert INTO CORE.MasterModelAttr 
	(
	   AttributeOrder 
      ,AttributeName 
      ,AttributeType 
      ,MasterType    
      ,Required      
      ,ValueUsedAsKey 
      ,DefaultValue   
	)
	values(
	   @AttributeOrder 
      ,@AttributeName 
      ,@AttributeType 
      ,@MasterType    
      ,@Required      
      ,@ValueUsedAsKey 
      ,@DefaultValue )

Iam trying to write condition its not working.can anyone please help on this.

Posted 1-Nov-12 11:00am

sailaja reddy 2

Updated 1-Nov-12 11:13am

fjdiewornncalwe

v3

Add a Solution

Comments

chaau 1-Nov-12 17:08pm

What errors are you getting?

sailaja reddy 2 1-Nov-12 17:13pm

u should enter unique attroderid

sailaja reddy 2 1-Nov-12 17:14pm

Thank for ur reply Andrew, iam getting errorlike u should enter unique attributeorder id no duplicate ids will accepct. is there anything i need to change here

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

fjdiewornncalwe · Answer 1 · 2012-11-01T11:18:00

AttributeOrder is likely set up as your primary key in the MasterModelAttr table and that will be a problem.
Your issue is that you don't associate your records in the MasterModelAttr table with a specific MasterModelID.
I would create an auto-incrementing IDENTITY to act as the primary key in the MasterModelAttr table.
Then create a new foreign key column for MasterModelID.
Then create a unique constraint containing a combination of AttributeOrder and MasterModelID so that uniqueness is respected.

Create Multi Column Unique Constraint

SQL

CREATE TABLE T
{
   Name varchar(10) ,
   Location varchar(10) ,
         CONSTRAINT U_NameLocation UNIQUE (Name,Location) 
}