July 17, 2013 at 4:52 am
create procedure insyenkcek
@phone nvarchar(20),@SertiNom nvarchar(50),@ASA nvarchar(100),@telUnvan nvarchar(100),@kateqoriya nvarchar(100)
,@SHesab nvarchar(20),@Alam nvarchar(60),@IDCDMAalam nvarchar(30),@TelTarix nvarchar(50),@MuqBasTar nvarchar(50)
,@MuqSonTar nvarchar(50),@Milliyet nvarchar(60),@Cinsi nvarchar(50),@DoYer nvarchar(100),@DoTarix nvarchar(50),
@SexVes nvarchar(60),@Elaqe nvarchar(50),@Odeme nvarchar(30),@Mebleg nvarchar(20),@BudceA nvarchar(60),@qeyd,
@SenTar nvarchar(50),@MINIATS nvarchar(50),@XusKart nvarchar(20),@Eskiz nvarchar(50),@NarN nvarchar(30),@NarBTar nvarchar(30)
,@NarSTar nvarchar(50),@men nvarchar(100)
as
insert natiq.YENCEK
(phone,SertiNom ,ASA ,telUnvan,IDKat
,SHesab ,Alam ,IDCDMAalam ,TelTarix,MuqBasTar
,MuqSonTar ,Milliyet ,Cinsi ,DoYer,DoTarix ,
SexVes ,Elaqe ,Odeme ,Mebleg ,BudceA ,qeyd,
SenTar ,MINIATS,XusKart,Eskiz ,NarN ,NarBTar
,NarSTar ,men
)
select convert(int,@phone),select convert(decimal(18, 0),@SertiNom),select @ASA,select @telUnvan,(select Kod from natiq.kateqor where Name=@kateqoriya )
,select convert(int,@SHesab) ,(select Kod from natiq.alamat where Name=@Alam) ,(select Kod from natiq.CDM where Name=@IDCMAalam)
,select @TelTarix,select @MuqBasTar
,select @MuqSonTar ,(select Kod from natiq.milli where Name=@Milliyet ),(select kod from cins where Name=@Cinsi) ,select @DoYer ,select @DoTarix ,
select @SexVes ,select @Elaqe ,(select Kod from natiq.ode where Name=@Odeme) ,select @Mebleg ,(select Kod from natiq.budc where Name=@BudceA) ,select @qeyd,
select @SenTar ,(select Kod from natiq.minats where Name=@MINIATS) ,select @XusKart ,select @Eskiz ,select @NarN,select @NarBTar
,select @NarSTar ,select @men
-------getting error
Post 170, Level 15, State 1, Procedure insyenkcek, line 5
Line 5: Incorrect syntax near ','.
Post 156, Level 15, State 1, Procedure insyenkcek, line 19
Incorrect syntax near the keyword 'select'.
Post 156, Level 15, State 1, Procedure insyenkcek, line 19
Incorrect syntax near the keyword 'select'.
Post 156, Level 15, State 1, Procedure insyenkcek, line 19
Incorrect syntax near the keyword 'select'.
Post 156, Level 15, State 1, Procedure insyenkcek, line 20
Incorrect syntax near the keyword 'select'.
Message 137, level 15, the status 2, the procedure insyenkcek, line 20
Must declare the variable '@ IDCMAalam'.
Post 156, Level 15, State 1, Procedure insyenkcek, line 21
Incorrect syntax near the keyword 'select'.
Post 156, Level 15, State 1, Procedure insyenkcek, line 22
Incorrect syntax near the keyword 'select'.
Post 156, Level 15, State 1, Procedure insyenkcek, line 22
Incorrect syntax near the keyword 'select'.
Post 156, Level 15, State 1, Procedure insyenkcek, line 22
Incorrect syntax near the keyword 'select'.
Post 156, Level 15, State 1, Procedure insyenkcek, line 23
Incorrect syntax near the keyword 'select'.
Post 156, Level 15, State 1, Procedure insyenkcek, line 23
Incorrect syntax near the keyword 'select'.
Post 156, Level 15, State 1, Procedure insyenkcek, line 23
Incorrect syntax near the keyword 'select'.
Post 156, Level 15, State 1, Procedure insyenkcek, line 23
Incorrect syntax near the keyword 'select'.
Message 137, level 15, the status 2, the procedure insyenkcek, line 23
Must declare the variable '@ qeyd'.
Post 137, Level 15, State 2, Procedure insyenkcek, line 24
Must declare the variable '@ SenTar'.
Post 137, Level 15, State 2, Procedure insyenkcek, line 24
Must declare the variable '@ MINIATS'.
Post 137, Level 15, State 2, Procedure insyenkcek, line 24
Must declare the variable '@ XusKart'.
Post 137, Level 15, State 2, Procedure insyenkcek, line 24
Must declare the variable '@ Eskiz'.
Post 137, Level 15, State 2, Procedure insyenkcek, line 24
Must declare the variable '@ NarN'.
Post 137, Level 15, State 2, Procedure insyenkcek, line 25
Must declare the variable '@ NarBTar'.
Post 137, Level 15, State 2, Procedure insyenkcek, line 25
Must declare the variable '@ NarSTar'.
Post 137, Level 15, State 2, Procedure insyenkcek, line 25
Must declare the variable '@ men'.
July 17, 2013 at 5:19 am
You cannot write your select statement like that (meaning by concatenating multiple selects in a row).
Either type INSERT ... SELECT convert(int,@phone), convert(decimal(18, 0),@SertiNom), ...
or use the VALUES clause.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 17, 2013 at 6:05 am
As Koen allready pointed out, you need to use a single SELECT statement to insert the values. You also made some errors declaring he variables. Below is the working code. I've put some REMARKs in the code to point out your mistakes (and assumptions I've made about datatype)
CREATE PROCEDURE insyenkcek @phone NVARCHAR(20)
, @SertiNom NVARCHAR(50)
, @ASA NVARCHAR(100)
, @telUnvan NVARCHAR(100)
, @kateqoriya NVARCHAR(100)
, @SHesab NVARCHAR(20)
, @Alam NVARCHAR(60)
, @IDCDMAalam NVARCHAR(30)
, @TelTarix NVARCHAR(50)
, @MuqBasTar NVARCHAR(50)
, @MuqSonTar NVARCHAR(50)
, @Milliyet NVARCHAR(60)
, @Cinsi NVARCHAR(50)
, @DoYer NVARCHAR(100)
, @DoTarix NVARCHAR(50)
, @SexVes NVARCHAR(60)
, @Elaqe NVARCHAR(50)
, @Odeme NVARCHAR(30)
, @Mebleg NVARCHAR(20)
, @BudceA NVARCHAR(60)
, @qeyd NVARCHAR(100) -- this datatype was NOT entered
, @SenTar NVARCHAR(50)
, @MINIATS NVARCHAR(50)
, @XusKart NVARCHAR(20)
, @Eskiz NVARCHAR(50)
, @NarN NVARCHAR(30)
, @NarBTar NVARCHAR(30)
, @NarSTar NVARCHAR(50)
, @men NVARCHAR(100)
, @IDCMAalam NVARCHAR(100) -- this variable was NOT entered
AS
INSERT natiq.YENCEK (
phone
, SertiNom
, ASA
, telUnvan
, IDKat
, SHesab
, Alam
, IDCDMAalam
, TelTarix
, MuqBasTar
, MuqSonTar
, Milliyet
, Cinsi
, DoYer
, DoTarix
, SexVes
, Elaqe
, Odeme
, Mebleg
, BudceA
, qeyd
, SenTar
, MINIATS
, XusKart
, Eskiz
, NarN
, NarBTar
, NarSTar
, men
)
SELECT convert(INT, @phone)
, convert(DECIMAL(18, 0), @SertiNom)
, @ASA
, @telUnvan
, (
SELECT Kod
FROM natiq.kateqor
WHERE NAME = @kateqoriya
)
, convert(INT, @SHesab)
, (
SELECT Kod
FROM natiq.alamat
WHERE NAME = @Alam
)
, (
SELECT Kod
FROM natiq.CDM
WHERE NAME = @IDCMAalam
)
, @TelTarix
, @MuqBasTar
, @MuqSonTar
, (
SELECT Kod
FROM natiq.milli
WHERE NAME = @Milliyet
)
, (
SELECT kod
FROM cins
WHERE NAME = @Cinsi
)
, @DoYer
, @DoTarix
, @SexVes
, @Elaqe
, (
SELECT Kod
FROM natiq.ode
WHERE NAME = @Odeme
)
, @Mebleg
, (
SELECT Kod
FROM natiq.budc
WHERE NAME = @BudceA
)
, @qeyd
, @SenTar
, (
SELECT Kod
FROM natiq.minats
WHERE NAME = @MINIATS
)
, @XusKart
, @Eskiz
, @NarN
, @NarBTar
, @NarSTar
, @men
July 17, 2013 at 6:08 am
One more point to keep in mind: the sub-selects must only return one single row. If more rows are returned, the insert will fail. You can use the SELECT TOP 1.... to make sure you will only get one row.
July 17, 2013 at 6:57 am
THANK YOU VERY MUCH
July 17, 2013 at 11:07 pm
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[insyenicek11] @phone NVARCHAR(20)
, @SertiNom NVARCHAR(50)
, @ASA NVARCHAR(100)
, @telUnvan NVARCHAR(100)
, @kateqoriya NVARCHAR(100)
, @SHesab NVARCHAR(20)
, @Alam NVARCHAR(60)
, @TelTarix NVARCHAR(50)
, @MuqBasTar NVARCHAR(50)
, @MuqSonTar NVARCHAR(50)
, @Milliyet NVARCHAR(60)
, @Cinsi NVARCHAR(50)
, @DoYer NVARCHAR(100)
, @DoTarix NVARCHAR(50)
, @SexVes NVARCHAR(60)
, @Elaqe NVARCHAR(50)
, @Odeme NVARCHAR(30)
, @Mebleg NVARCHAR(20)
, @BudceA NVARCHAR(60)
, @qeyd NVARCHAR(100) -- this datatype was NOT entered
, @SenTar NVARCHAR(50)
, @MINIATS NVARCHAR(50)
, @XusKart NVARCHAR(20)
, @Eskiz NVARCHAR(50)
, @NarN NVARCHAR(30)
, @NarBTar NVARCHAR(30)
, @NarSTar NVARCHAR(50)
, @men NVARCHAR(100)
, @IDCMAalam NVARCHAR(100) -- this variable was NOT entered
AS
INSERT natiq.YENCEK (
phone
, SertiNom
, ASA
, telUnvan
, IDKat
, SHesab
, Alam
, IDCDMAalam
, TelTarix
, MuqBasTar
, MuqSonTar
, Milliyet
, Cinsi
, DoYer
, DoTarix
, SexVes
, Elaqe
, Odeme
, Mebleg
, BudceA
, qeyd
, SenTar
, MINIATS
, XusKart
, Eskiz
, NarN
, NarBTar
, NarSTar
, men
)
SELECT convert(decimal(7, 0), @phone)
, convert(DECIMAL(18, 0), @SertiNom)
, @ASA
, @telUnvan
, (
SELECT Kod
FROM natiq.kateqor
WHERE NAME = @kateqoriya
)
, convert(INT, @SHesab)
, (
SELECT Kod
FROM natiq.alamat
WHERE NAME = @Alam
)
, (
SELECT Kod
FROM natiq.CDM
WHERE NAME = @IDCMAalam
)
, CONVERT(smalldatetime,@TelTarix)
, CONVERT(smalldatetime,@MuqBasTar)
,CONVERT(smalldatetime, @MuqSonTar)
, (
SELECT Kod
FROM natiq.milli
WHERE NAME = @Milliyet
)
, (
SELECT kod
FROM cins
WHERE NAME = @Cinsi
)
, @DoYer
, CONVERT(smalldatetime,@DoTarix)
, @SexVes
, @Elaqe
, (
SELECT Kod
FROM natiq.ode
WHERE NAME = @Odeme
)
, CONVERT(decimal(7, 2),@Mebleg)
, (
SELECT Kod
FROM natiq.budc
WHERE NAME = @BudceA
)
, @qeyd
, CONVERT(smalldatetime,@SenTar)
, (
SELECT Kod
FROM natiq.minats
WHERE NAME = @MINIATS
)
, @XusKart
, CONVERT(smallint,@Eskiz)
, CONVERT(smallint,@NarN)
, CONVERT(smalldatetime,@NarBTar)
, CONVERT(smalldatetime,@NarSTar)
, @men
BUT EXECUTE GET ME ERROR
exec insyenicek11 '11','22','sff','dgdgd',N'Hüquqi s?xs','`12312',N'Adi',N'Limitli',
'20130105','20130105','20130105',N'RUS',N'QADIN','jinkjnn','20130102','hbjkbjb','jnjnnj',N'Kreditl?','5451'
,N'?hali','jnjnnjn','20120101',N'Mini ATS','jjnjnjn','2151','551','20120101','20120101','jhkk'
Post 295, Level 16, State 3, Procedure insyenicek11, line 31
Syntax error converting character string to smalldatetime data type.
July 17, 2013 at 11:40 pm
July 17, 2013 at 11:51 pm
gurbanov.1984 (7/17/2013)
Post 295, Level 16, State 3, Procedure insyenicek11, line 31Syntax error converting character string to smalldatetime data type.
All your variables in your query are declared with a NVARCHAR datatype. The error indicates that at least one of the columns in the table is defined with a SMALLDATETIME datatype. The value you enter in the variable is not a valid date/time value. Hence the value cannot be inserted in the table.
Please change the datatypes of ALL the variables to match the datatype of the columns.
July 17, 2013 at 11:59 pm
I have changed procedure
this
(SELECT TOP 1 Kod FROM natiq.kateqor WHERE NAME = @kateqoriya )
And it worked
THANK YOU VERY MUCH
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply