create porc - 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

create porc

Author

Topic

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-07-17 : 06:38:57

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'.

kmkmmm

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-07-17 : 07:06:47

if you double click on the error, it will bring you to the line that cause the error


@SexVes nvarchar(60),@Elaqe nvarchar(50),@Odeme nvarchar(30),@Mebleg nvarchar(20),@BudceA nvarchar(60),@qeyd <data type>,

also, remove those select ...

select convert(int,@phone),select convert(decimal(18, 0),@SertiNom),select @ASA...

change to


select convert(int,@phone), convert(decimal(18, 0),@SertiNom), @ASA...

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-17 : 07:12:35

also the select is wrong it should be like below


...
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 

...

Also all of those subqueries should return only single row otherwise above select will fail

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-07-17 : 08:56:26

THANK YOU VERY MUCH VISKAH,KHTAN

kmkmmm

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-17 : 11:05:19

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-07-18 : 01:04:23

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.

kmkmmm

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 01:22:51

you can omit parameters like that. if you dont want to sent values for all you need to make them optional by giving a default value. As of now I'm not seeing default value specified for any. So you need to explicitly pass a value for all parameters

Also in case you make them optional with a default value and doesnt include them in EXEC you need to specify parameter names for all others like



exec insyenicek11 @parameter1name='11',@parameter2name='22',...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-07-18 : 01:38:32

BUT I GET ERROR AGAIN

exec insyenicek11
@phone='11',
@SertiNom='22',
@ASA='sff',
@telUnvan='dgdgd',
@kateqoriya=N'Hüquqi s?xs',
@SHesab='12312'
,@Alam=N'Adi',
@IDCMAalam=N'Limitli',
@TelTarix='20130105',
@MuqBasTar='20130105',
@MuqSonTar='20130105',
@Milliyet=N'RUS',
@Cinsi=N'QADIN',
@DoYer='jinkjnn',
@DoTarix='20130102',
@SexVes='hbjkbjb',
@Elaqe='jnjnnj',
@Odeme=N'Kreditl?',
@Mebleg='5451'
,@BudceA=N'?hali',
@qeyd='jnjnnjn',
@SenTar='20120101',
@MINIATS=N'Mini ATS',
@XusKart='jjnjnjn'
,@Eskiz='2151',
@NarN='551',
@NarBTar='20120101'
,@NarSTar='20120101',
@men='jhkk'

Post 512, Level 16, State 1, Procedure insyenicek11, line 31
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.

(rows processed: 0)

kmkmmm

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 01:42:53

See my response on 07/17/2013 : 07:12:35 (given below as well)

Also all of those subqueries should return only single row otherwise above select will fail

Thats exactly the reason why you're getting the current error message

you need to make sure subqueries like


(
SELECT Kod
FROM natiq.kateqor
WHERE NAME = @kateqoriya
)

etc used in the SELECT for the INSERT will return only one value otherwise you'll get error like this. If its returning duplicates you need to determine which value you want out of it by applying some kind of aggregation like MIN,MAX,...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-07-18 : 01:58:11

thank you very much visakh

I have changed procedure

this

(SELECT TOP 1 Kod FROM natiq.kateqor WHERE NAME = @kateqoriya )

And it worked

kmkmmm

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 02:03:58

ok...but keep in mind that using just TOP 1 without any ORDER BY accompanied just gives you a random value out of the group of values returned by the query and may vary within executions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources