ALTER PROCEDURE ERROR - 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

General SQL Server Forums

New to SQL Server Programming

ALTER PROCEDURE ERROR

Author

Topic

Lesombrero
Starting Member

43 Posts

Posted - 2012-09-20 : 13:41:49

Hello everyone,
I am new here, and novice in sql!
I get this error in the script bellow.

Error:
Msg 102, Niveau 15, État 1, Procédure Temp_EQUIP, Ligne 44
Syntaxe incorrecte vers 'A'. (sorry french version!)

I did compare all the types of the fields of the tables, and they are all exactly similar!

Thank you for your help!

script:

USE [planet_horse_sales]
GO
/****** Object : StoredProcedure [dbo].[Temp_EQUIP] Date de génération du script : 09/18/2012 10:37:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Temp_EQUIP]
-- Add the parameters for the stored procedure here

AS

-- DELETE
DELETE FROM equip_1tempo WHERE id_equip NOT In (Select id_equip From equip_vue)

-- INSERT
INSERT INTO equip_1tempo SELECT * FROM equip_vue WHERE id_equip NOT In (Select id_equip From equip_1tempo)

-- UPDATE
UPDATE A SET
A.picture = B.picture,
A.id_equip_matrice = B.id_equip_matrice,
A.id_accessoire_parent = B.id_accessoire_parent,
A.id_equip_type = B.id_equip_type,
A.id_equip_familly = B.id_equip_familly,
A.equip_nom_fr = B.equip_nom_fr,
A.equip_nom_en = B.equip_nom_en,
A.equip_nom_de = B.equip_nom_de,
A.id_equip_matiere = B.id_equip_matiere,
A.id_equip_color = B.id_equip_color,
A.equip_long = B.equip_long,
A.equip_larg = B.equip_larg,
A.equip_haut = B.equip_haut,
A.equip_profond = B.equip_profond,
A.equip_epai = B.equip_epai,
A.equip_diam = B.equip_diam,
A.equip_poids = B.equip_poids,
A.equip_volume = B.equip_volume,
A.equip_volume_unit = B.equip_volume_unit, /* Here is the problem!)*/
A.equip_power = B.equip_power,
A.equip_power_unit = B.equip_power_unit,
A.equip_nb_chx = B.equip_nb_chx,
A.description_fr = B.description_fr,
A.description_en = B.description_en,
A.description_de = B.description_de
A.prix_ht = B.prix_ht,
A.id_equip_fourn = B.id_equip_fourn,
A.equip_ref = B.equip_ref,
A.equip_fourn_ref = B.equip_fourn_ref,
A.equip_fourn_deal = B.equip_fourn_deal,
A.equip_fourn_prix = B.equip_fourn_prix,
A.equip_fourn_pourcent = B.equip_fourn_pourcent,
A.masquer = B.masquer,
A.nouveaute = B.nouveaute
FROM equip_1tempo A
INNER JOIN equip_vue B
ON A.id_equip = B.id_equip
AND A.id_equip In (Select id_equip From (Select id_equip,picture,id_equip_matrice,id_accessoire_parent,id_equip_type,id_equip_familly,equip_nom_fr,equip_nom_en,equip_nom_de,id_equip_matiere,id_equip_color,equip_long,equip_larg,equip_haut,equip_profond,equip_epai,equip_diam,equip_poids,equip_volume,equip_volume_unit,cast(description_fr as nvarchar(MAX)) as description_fr,equip_volume_unit,equip_power,equip_power_unit,equip_nb_chx,cast(description_fr as nvarchar(MAX)) as description_fr,cast(description_en as nvarchar(MAX)) as description_en,cast(description_de as nvarchar(MAX)) as description_de,prix_ht,id_equip_fourn,equip_ref,equip_fourn_ref,equip_fourn_deal,equip_fourn_prix,equip_fourn_pourcent,masquer,nouveaute From equip_vue
EXCEPT
Select id_equip,picture,id_equip_matrice,id_accessoire_parent,id_equip_type,id_equip_familly,equip_nom_fr,equip_nom_en,equip_nom_de,id_equip_matiere,id_equip_color,equip_long,equip_larg,equip_haut,equip_profond,equip_epai,equip_diam,equip_poids,equip_volume,equip_volume_unit,equip_power,equip_power_unit,equip_nb_chx,cast(description_fr as nvarchar(MAX)) as description_fr,cast(description_en as nvarchar(MAX)) as description_en,cast(description_de as nvarchar(MAX)) as description_de,prix_ht,id_equip_fourn,equip_ref,equip_fourn_ref,equip_fourn_deal,equip_fourn_prix,equip_fourn_pourcent,masquer,nouveaute From equip_1tempo) C)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-20 : 13:53:16

You have a missing comma:

....
A.description_fr = B.description_fr,
A.description_en = B.description_en,
A.description_de = B.description_de, --<<<<<<
A.prix_ht = B.prix_ht,
A.id_equip_fourn = B.id_equip_fourn,
A.equip_ref = B.equip_ref,
A.equip_fourn_ref = B.equip_fourn_ref,
....

Lesombrero
Starting Member

43 Posts

Posted - 2012-09-20 : 13:58:15

Thank you so much

Lesombrero
Starting Member

43 Posts

Posted - 2012-09-20 : 14:04:48

But now I have :
Msg 208, Niveau 16, État 6, Procédure Temp_EQUIP, Ligne 18
Nom d'objet 'dbo.Temp_EQUIP' non valide.

The name of the object is not valid !!??

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-20 : 14:13:34

That error message is usually seen when you try to alter a stored procedure that does not exists. To create a stored proc, use CREATE keyword instead of ALTER.


create PROCEDURE [dbo].[Temp_EQUIP]
-- Add the parameters for the stored procedure here

AS

You can check if the stored procedure exists by looking in the object explorer in SSMS, or by running this query

SELECT * FROM sys.procedures p WHERE NAME = 'Temp_EQUIP'

Lesombrero
Starting Member

43 Posts

Posted - 2012-09-20 : 14:40:53

Thank you sunitabeck.
And ok, I lied! I am not novice, but VERY novice ;-)!!!
Thanks again for everything.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-20 : 15:13:48

Glad to help :)

BTW, you can double click on an error message that you see in the output pane and your cursor will be taken to the line on which the error occurred. Useful very often, although not always.

Another thing you might consider is a code formatting utility. There are several paid and free tools out there. SSMS Tools Pack is a free one - here: http://www.ssmstoolspack.com/

Lesombrero
Starting Member

43 Posts

Posted - 2012-09-20 : 15:25:53

Thanks for the tips!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources