The Yaeger
The Yaeger

Reputation: 43

Understanding creating simple stored procedures

I'm in the process of learning to create stored procedures in Microsoft SQL Server Management Studio. I need to create a stored procedure that adds a single new record to my table. Also, I need to create two extra output parameters along with the stored procedure (I chose @@error and SCOPE_IDENTITY()).

This is the code I use to create my stored procedure:

use bieren
go

if exists
    (select name from sysobjects
    where name = 'spBierInsert' and xtype = 'p')
    drop procedure spBierInsert
go

create procedure spBierInsert
    @Biernr int = 0,
    @Naam nvarchar(100) = '',
    @BrouwerNr int = 0,
    @SoortNr int = 0,
    @Alcohol real,
    @gelukt nvarchar(10) output,
    @id int output
as
begin
        declare @fout int
    insert into bieren
        values (@Biernr, @Naam, @BrouwerNr, @SoortNr, @Alcohol)
            set @fout = @@error
            print 'Foutnummer:' + cast(@fout as varchar(4))

            if @fout > 0
                set @gelukt = 'Neen: ' + cast(@fout as varchar(4))
            else
                set @gelukt = 'Ja'
        set @id = SCOPE_IDENTITY()
end

I must be doing something wrong, because the result is the following:

Msg 547, Level 16, State 0, Procedure spBierInsert, Line 92 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Bieren_Brouwers". The conflict occurred in database "Bieren", table "dbo.Brouwers", column 'BrouwerNr'. The statement has been terminated. Foutnummer:547

(1 row(s) affected)

What have I done incorrectly?

EDIT 30/12/2015: I have updated this question with new information. I originally just used terms like "exampletable" because I had no idea that the search to the answer to my question would be more involved than a single answer, so I've gone ahead and changed the entire code above (as well as the text for the error), and I've added the script for my table underneath. The point of this question is that I come out with code that works, or, that I at least understand what's wrong with it.

USE [Bieren]
GO

/****** Object:  Table [dbo].[Bieren]    Script Date: 30/12/2015 0:19:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Bieren](
    [BierNr] [int] NOT NULL,
    [Naam] [nvarchar](100) NULL,
    [BrouwerNr] [int] NULL,
    [SoortNr] [int] NULL,
    [Alcohol] [real] NULL,
 CONSTRAINT [PK_Bieren] PRIMARY KEY CLUSTERED 
(
    [BierNr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Bieren]  WITH CHECK ADD  CONSTRAINT [FK_Bieren_Brouwers] FOREIGN KEY([BrouwerNr])
REFERENCES [dbo].[Brouwers] ([BrouwerNr])
GO

ALTER TABLE [dbo].[Bieren] CHECK CONSTRAINT [FK_Bieren_Brouwers]
GO

ALTER TABLE [dbo].[Bieren]  WITH CHECK ADD  CONSTRAINT [FK_Bieren_Soorten] FOREIGN KEY([SoortNr])
REFERENCES [dbo].[Soorten] ([SoortNr])
GO

ALTER TABLE [dbo].[Bieren] CHECK CONSTRAINT [FK_Bieren_Soorten]
GO

Upvotes: 0

Views: 68

Answers (2)

Akshey Bhat
Akshey Bhat

Reputation: 8545

Your procedure is created fine. The problem is that you are inserting a value in column 'BrouwerNr' of table "dbo.Brouwers" which doesn't exist in "SoortNr" column of table "dbo.Soorten". There is foreign set on the table "dbo.Brouwers" named "[FK_Bieren_Soorten]" which is causing this restriction. I suggest you look into this article to know more about foreign keys.

Upvotes: 3

Suman Pathak
Suman Pathak

Reputation: 300

The error is because you are inserting 1600 in @ColumnNr, which is a foreign key of another table and does not have 1600 in it.

You can do the following :

right click on "exampletable" table and select 'Script table as'->'Create to'->'new query editor window'

Now,find "ColumnNr" in it. It will be something like this =>

ALTER TABLE [dbo].[exampleTable]  WITH CHECK ADD  CONSTRAINT [FK_exampleTable_**OtherTableName**_ColumnNr] FOREIGN KEY([ColumnNr])
REFERENCES [dbo].**[OtherTableName]** ([ColumnNr])
GO

Now open the mentioned table "OtherTableName" in the query and look for the column "ColumnNr". It will not be having value 1600.

Try to insert any value in

@ColumnNr = {//Any value from **OtherTableName**}, 

which is in table "OtherTableName"

Upvotes: 0

Related Questions