error in procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

error in procedure

Author

Topic

tempus
Starting Member

47 Posts

Posted - 2010-10-19 : 09:17:15

Dear Sirs,

i have an error in my procedure and i don't know what is the problem. i would apreciate any help

USE [iScalaDB]
GO

/****** Object: StoredProcedure [dbo].[Romcolor_preturi_agreate] Script Date: 10/19/2010 13:15:12 ******/
SET ANSI_NULLS ON
GO
/*
select * from SC180100 WHERE SC18002 LIKE '411.1016%' AND SC18001 = 'C' AND SC18003 = 'PFPS151-G'
select * from SC180100 WHERE RIGHT (SC18002,8) = '411.1016' AND SC18001 = 'D' AND SC18003 = 'PFPS151-G'
1.815
*/

/*

EXEC [dbo].[Romcolor_preturi_agreate]
@client = 'ARCTIC SA',
@test='1',
@produs = 'PFPS151-G',
@parametru1 = '3',
@parametru2 = '1.055',
@parametru3 = '2.444',
@parametru4 = '1.0065'

*/
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[Romcolor_preturi_agreate]
@test as numeric (1),
@client as nvarchar (35),
@produs as nvarchar (35),
@parametru1 as numeric(18,2),
@parametru2 as numeric(18,2),
@parametru3 as numeric(18,2),
@parametru4 as numeric(18,2)
as

IF @test = '1'
BEGIN
BEGIN TRAN

INSERT INTO dbo.BT_TABELA_PRETURI_AGREATE
SELECT SL01001 [cod client],
SL01002 [denumire client],
SL01035 agent,
@produs [cod produs],
SC01002 [denumire produs],
RMC_clasa_intern*@parametru1*@parametru2*@parametru3*@parametru4 [pret_agreat],
SYSDATETIME() [data pret agreat]
FROM RMC_CLASA_VS_REAL , SL010100, SC010100
WHERE SC01001 = @produs and
@produs = cod and
SL01002 = @client and
@parametru1 between '1.00' and '3.00' and
@parametru2 between '1.00' and '3.00' and
@parametru3 between '1.00' and '3.00' and
@parametru4 between '1.00' and '3.00'

IF EXISTS
(SELECT SL01001 + ' ' + SC01001 SC18002
FROM SL010100, SC010100
WHERE SC01001 = @produs and
SL01002 = @client )
BEGIN

delete from SC180100 WHERE SC18002 = (SELECT SL01001 + ' ' + SC01001 SC18002
FROM SL010100, SC010100
WHERE SC01001 = @produs and
SL01002 = @client)
AND SC18001 = 'C'

delete from SC180100 WHERE RIGHT(SC18002,8) = (SELECT SL01001
FROM SL010100, SC010100
WHERE SL01002 = @client)
AND SC18003 = @produs
AND SC18001 = 'D'

insert into SC180100
SELECT 'C' SC18001,
SL01001 + ' ' + SC01001 SC18002,
SC01001 SC18003,
SC01002 SC18004,
' ' SC18005,
' ' SC18006,
'1' SC18007,
ROUND (RMC_clasa_intern*@parametru1*@parametru2*@parametru3*@parametru4,2) SC18008,
'0' SC18009,
'1900-01-01 00:00:00.000' SC18010,
'9999-12-31 00:00:00.000' SC18011,
'0.00000000' SC18012,
'0' SC18013,
'0.00000000' SC18014,
'0.00000000' SC18015,
'0.00000000' SC18016,
'0.00000000' SC18017,
'0.00000000' SC18018,
'0.00000000' SC18019,
'0.00000000' SC18020,
'0.00000000' SC18021,
'0.00000000' SC18022,
'0.00000000' SC18023,
'0.00000000' SC18024,
'0.00000000' SC18025,
'0.00000000' SC18026,
'0.00000000' SC18027,
'0.00000000' SC18028,
'0.00000000' SC18029,
'0.00000000' SC18030,
'0.00000000' SC18031,
'0.00000000' SC18032,
'0.00000000' SC18033,
'0.00000000' SC18034,
'0.00000000' SC18035,
'0.00000000' SC18036,
'0.00000000' SC18037,
'0.00000000' SC18038,
'0' SC18039,
'0.00000000' SC18040

FROM RMC_CLASA_VS_REAL , SL010100, SC010100
WHERE SC01001 = @produs and
@produs = cod and
SL01002 = @client and
@parametru1 between '1.00' and '3.00' and
@parametru2 between '1.00' and '3.00' and
@parametru3 between '1.00' and '3.00' and
@parametru4 between '1.00' and '3.00'
*/
commit tran
END

/*insert into SC180100
SELECT 'D' SC18001,
SL01001 + ' ' + SC01001 SC18002,
SC01001 SC18003,
SC01002 SC18004,
' ' SC18005,
' ' SC18006,
'1' SC18007,
ROUND (RMC_clasa_intern*@parametru1*@parametru2*@parametru3*@parametru4,2) SC18008,
'0' SC18009,
'1900-01-01 00:00:00.000' SC18010,
'9999-12-31 00:00:00.000' SC18011,
'0.00000000' SC18012,
'0' SC18013,
'0.00000000' SC18014,
'0.00000000' SC18015,
'0.00000000' SC18016,
'0.00000000' SC18017,
'0.00000000' SC18018,
'0.00000000' SC18019,
'0.00000000' SC18020,
'0.00000000' SC18021,
'0.00000000' SC18022,
'0.00000000' SC18023,
'0.00000000' SC18024,
'0.00000000' SC18025,
'0.00000000' SC18026,
'0.00000000' SC18027,
'0.00000000' SC18028,
'0.00000000' SC18029,
'0.00000000' SC18030,
'0.00000000' SC18031,
'0.00000000' SC18032,
'0.00000000' SC18033,
'0.00000000' SC18034,
'0.00000000' SC18035,
'0.00000000' SC18036,
'0.00000000' SC18037,
'0.00000000' SC18038,
'0' SC18039,
'0.00000000' SC18040

FROM RMC_CLASA_VS_REAL , SL010100, SC010100
WHERE SC01001 = @produs and
@produs = cod and
SL01002 = @client and
@parametru1 between '1.00' and '3.00' and
@parametru2 between '1.00' and '3.00' and
@parametru3 between '1.00' and '3.00' and
@parametru4 between '1.00' and '3.00' */

END

-- interesant, daca parametrul nu este intre conditia pusa la where atunci update-ul nu se face :)

ELSE (SELECT SL01001 [cod client],
SL01002 [denumire client],
SL01035 agent,
@produs [cod produs],
SC01002 [denumire produs],
RMC_clasa_intern*@parametru1*@parametru2*@parametru3*@parametru4 [pret_agreat],
SYSDATETIME() [data pret agreat]
FROM RMC_CLASA_VS_REAL , SL010100, SC010100
WHERE SC01001 = @produs and
@produs = cod and
SL01002 = @client)

GO

when i execute i get the following error:

(1 row(s) affected)

(0 row(s) affected)
Msg 512, Level 16, State 1, Procedure Romcolor_preturi_agreate, Line 65
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

also i am i need of help for the following:

i have 2 field SC01001 and SL01001 which are different lenght of chars each and i need to find a formula that SC01001 + '--spaces--' + SL01001 to be exact 43 characters, keeping in mind that SC01001 and SL01001 can be from 4 to 20 characters (depending on the values in the database)

thank you in advance for the help.

Best regards,
Bogdan Tiba

tempus
Starting Member

47 Posts

Posted - 2010-10-19 : 10:12:38

hello. it seems that modifing the "=" with "in" at the deleted parts of the procedure doesn't return any error.

i will come back with more feeback.

Still i need to calculate that 43 char field with 2 random lenght of columns.

Best regards,
Bogdan Tiba.

tempus
Starting Member

47 Posts

Posted - 2010-10-19 : 10:21:07

hello again,

i managed to correct the procedure but still i have some problems. the deleted part with IF EXISTS works as intended, and its deletes from the database the rows when the condition is meet. But the part with IF NOT EXISTS does nothing. nothing is inserted into the database. any ideas what could it be?

im giving bellow the last version of the procedure.

USE [iScalaDB]
GO

/****** Object: StoredProcedure [dbo].[Romcolor_preturi_agreate] Script Date: 10/19/2010 13:15:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
/*
select * from SC180100 WHERE SC18002 LIKE '411.1016%' AND SC18001 = 'C' AND SC18003 = 'PFPS151-G'
select * from SC180100 WHERE RIGHT (SC18002,8) = '411.1016' AND SC18001 = 'D' AND SC18003 = 'PFPS151-G'
select * from dbo.BT_TABELA_PRETURI_AGREATE where [cod client] = '411.1016' order by [cod client]

1.815
*/

/*

EXEC [dbo].[Romcolor_preturi_agreate]
@client = 'ARCTIC SA',
@test='1',
@produs = 'PFPS151-N',
@parametru1 = '3',
@parametru2 = '1.05',
@parametru3 = '2.44',
@parametru4 = '1.00'

*/
ALTER procedure [dbo].[Romcolor_preturi_agreate]
@test as numeric (1),
@client as nvarchar (35),
@produs as nvarchar (35),
@parametru1 as numeric(18,2),
@parametru2 as numeric(18,2),
@parametru3 as numeric(18,2),
@parametru4 as numeric(18,2)
as

IF @test = '1'
BEGIN

INSERT INTO dbo.BT_TABELA_PRETURI_AGREATE
SELECT left(SL01001,8) [cod client],
SL01002 [denumire client],
SL01035 agent,
@produs [cod produs],
SC01002 [denumire produs],
round(RMC_clasa_intern*@parametru1*@parametru2*@parametru3*@parametru4,2) [pret_agreat],
SYSDATETIME() [data pret agreat]
FROM RMC_CLASA_VS_REAL , SL010100, SC010100
WHERE SC01001 = @produs and
@produs = cod and
SL01002 = @client and
@parametru1 between '1.00' and '3.00' and
@parametru2 between '1.00' and '3.00' and
@parametru3 between '1.00' and '3.00' and
@parametru4 between '1.00' and '3.00'

IF EXISTS
(SELECT SL01001+' '+SC01001
FROM SL010100, SC010100
WHERE SC01001 = @produs and
SL01002 = @client )

BEGIN

delete from SC180100 WHERE SC18002 in (SELECT left(SL01001,8)+' '+SC01001 SC18002
FROM SL010100, SC010100
WHERE SC01001 = @produs and
SL01002 = @client)
AND SC18001 = 'C'

delete from SC180100 WHERE RIGHT(SC18002,8) in (SELECT left(SL01001,8)
FROM SL010100, SC010100
WHERE SL01002 = @client)
AND SC18003 = @produs
AND SC18001 = 'D'
END

IF NOT EXISTS
(SELECT SL01001+' '+SC01001
FROM SL010100, SC010100
WHERE SC01001 = @produs and
SL01002 = @client )
BEGIN

insert into SC180100
SELECT 'C' SC18001,
left(SL01001,8)+' '+SC01001 SC18002,
left(SL01001,8) SC18003,
SC01002 SC18004,
' ' SC18005,
' ' SC18006,
'1' SC18007,
ROUND(RMC_clasa_intern*@parametru1*@parametru2*@parametru3*@parametru4,2) SC18008,
'0' SC18009,
'1900-01-01 00:00:00.000' SC18010,
'9999-12-31 00:00:00.000' SC18011,
'0.00000000' SC18012,
'0' SC18013,
'0.00000000' SC18014,
'0.00000000' SC18015,
'0.00000000' SC18016,
'0.00000000' SC18017,
'0.00000000' SC18018,
'0.00000000' SC18019,
'0.00000000' SC18020,
'0.00000000' SC18021,
'0.00000000' SC18022,
'0.00000000' SC18023,
'0.00000000' SC18024,
'0.00000000' SC18025,
'0.00000000' SC18026,
'0.00000000' SC18027,
'0.00000000' SC18028,
'0.00000000' SC18029,
'0.00000000' SC18030,
'0.00000000' SC18031,
'0.00000000' SC18032,
'0.00000000' SC18033,
'0.00000000' SC18034,
'0.00000000' SC18035,
'0.00000000' SC18036,
'0.00000000' SC18037,
'0.00000000' SC18038,
'0' SC18039,
'0.00000000' SC18040
FROM RMC_CLASA_VS_REAL , SL010100, SC010100
WHERE SC01001 = @produs and
@produs = cod and
SL01002 = @client and
@parametru1 between '1.00' and '3.00' and
@parametru2 between '1.00' and '3.00' and
@parametru3 between '1.00' and '3.00' and
@parametru4 between '1.00' and '3.00'

insert into SC180100
SELECT 'D' SC18001,
SC01001+' '+SL01001 SC18002,
SC01001 SC18003,
SC01002 SC18004,
' ' SC18005,
' ' SC18006,
'1' SC18007,
ROUND(RMC_clasa_intern*@parametru1*@parametru2*@parametru3*@parametru4,2) SC18008,
'0' SC18009,
'1900-01-01 00:00:00.000' SC18010,
'9999-12-31 00:00:00.000' SC18011,
'0.00000000' SC18012,
'0' SC18013,
'0.00000000' SC18014,
'0.00000000' SC18015,
'0.00000000' SC18016,
'0.00000000' SC18017,
'0.00000000' SC18018,
'0.00000000' SC18019,
'0.00000000' SC18020,
'0.00000000' SC18021,
'0.00000000' SC18022,
'0.00000000' SC18023,
'0.00000000' SC18024,
'0.00000000' SC18025,
'0.00000000' SC18026,
'0.00000000' SC18027,
'0.00000000' SC18028,
'0.00000000' SC18029,
'0.00000000' SC18030,
'0.00000000' SC18031,
'0.00000000' SC18032,
'0.00000000' SC18033,
'0.00000000' SC18034,
'0.00000000' SC18035,
'0.00000000' SC18036,
'0.00000000' SC18037,
'0.00000000' SC18038,
'0' SC18039,
'0.00000000' SC18040
FROM RMC_CLASA_VS_REAL , SL010100, SC010100
WHERE SC01001 = @produs and
@produs = cod and
SL01002 = @client and
@parametru1 between '1.00' and '3.00' and
@parametru2 between '1.00' and '3.00' and
@parametru3 between '1.00' and '3.00' and
@parametru4 between '1.00' and '3.00'
END
END
-- interesant, daca parametrul nu este intre conditia pusa la where atunci update-ul nu se face :)
ELSE SELECT left(SL01001,8) [cod client],
SL01002 [denumire client],
SL01035 agent,
@produs [cod produs],
SC01002 [denumire produs],
round(RMC_clasa_intern*@parametru1*@parametru2*@parametru3*@parametru4,2) [pret_agreat],
SYSDATETIME() [data pret agreat]
FROM RMC_CLASA_VS_REAL , SL010100, SC010100
WHERE SC01001 = @produs and
@produs = cod and
SL01002 = @client
GO

tempus
Starting Member

47 Posts

Posted - 2010-10-19 : 11:45:58

hello again. somewhere in the IF EXISTS and IF NOT EXISTS was a problem. so i deleted them and left only the deleted and insert statements directly

delete from ....

delete from ....

insert into ....

insert into ....

and it works as intended.

thank you anyway! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources