Problem with Stored Procedure

Last Post 08 Jan 2007 05:35 AM by FabioEinaus. 7 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
FabioEinaus
New Member
New Member

--
08 Jan 2007 02:15 AM
Hi Guys!

I'm getting a little problem making my stored procedure..

I'm making a stored procedure for copy all the records that I have on the Carrinho table to the DocumentosArtigos table.

I have an image that discribes what I want:

Image

I made some code with a little help and I have this now:

USE EINAUS
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE e_TransformaCarrinho
-- Add the parameters for the stored procedure here
172312 as CHAR(5)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @I INT
SET @I = 0
INSERT DocumentosArtigos
SELECT 'T', 50 ,CodDocumentoID, @I, ArtigoID, NomeArtigo, Quantidade,
CASE WHEN CodDocumento = 1 THEN 'PVP' WHEN CodDocumento = 2 THEN 'PC' END,
0, 0, 0, 0.21, (Quantidade*PVP+(Quantidade*PVP*0.21)), 0, (Quantidade*PVP*0.21), 0, 0
FROM Carrinho
WHERE UserID = 172312

END
GO

I'm getting the folowing error:

Msg 515, Level 16, State 2, Procedure tgDocumentosArtigos_Insert, Line 28
Cannot insert the value NULL into column 'PrUnitario', table 'EINAUS.dbo.DocumentosArtigos'; column does not allow nulls. INSERT fails.
The statement has been terminated.

I think the SP ig going to work I only don't know if @I will be sequencial.. is it?


A Happy 2007 to everyone!

Thanks in advance for the replys
JHunter
New Member
New Member

--
08 Jan 2007 03:29 AM
Could the column CodDocumento be anything other than 1 or 2?

Try adding an "else" clause to your case statement.

Jamie
FabioEinaus
New Member
New Member

--
08 Jan 2007 04:21 AM
Yes it can be more than those 2 values so the else is not possible :S
JHunter
New Member
New Member

--
08 Jan 2007 04:40 AM
If you don't have an else clause the case statement will return NULL, causing your insert error.

If these other values are not required to be dealt with, filter them out in your where clause.

Jamie
FabioEinaus
New Member
New Member

--
08 Jan 2007 04:57 AM
I finally solved this problem. thanks but now i'm getting another.. it doesn't insert anything
JHunter
New Member
New Member

--
08 Jan 2007 05:24 AM
Does the SELECT query return any data? Is your calling program do any error checking that may cause the query to be rolled back?

Jamie
FabioEinaus
New Member
New Member

--
08 Jan 2007 05:35 AM
Yes the
Select *
From Carrinho
Where UserID ='FAP'
returns 3 record..

I just don't get it..
FabioEinaus
New Member
New Member

--
08 Jan 2007 07:19 AM
My problem is that It have to be inserted one row at a time and it could be done by a while or something like that but how can I know the records that I've inserted before?

Like the arrays I use to do like this:

a=[0,0,0,0,0,0,0,0,0]
i=0
temp=5
do
a
i++
while(i!=10)

But in this case I know the index that i'm working.. how can I now this in SQLServer?
You are not authorized to post a reply.

Acceptable Use Policy