Store Procedures - 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 2000 Forums

Transact-SQL (2000)

Store Procedures

Author

Topic

aozgec
Starting Member

5 Posts

Posted - 2004-04-12 : 15:54:10

I cannot assaign strings containing spaces from my code when I use querry.
if I assing @dethas varaible a string like "aþsjdh asdja" an error occures. How can eliminate it....??

My query like this..

CREATE proc tasEkle
@Sig int,
@plaka nvarchar(12) ,
@mrka int ,
@mdel smallint,
@tip nvarchar(50),
@mtr nvarchar(50),
@sanz int,
@sase bigint,
@mtrNo bigint,
@renk int,
@kulTip int,
@tekDet nvarchar(500),
@hasar tinyint,
@Ibsl datetime,
@Ibit datetime,
@stat int,
@Acik nvarchar(100),
@km int,
@detbil nvarchar(500),
@sncFyt money,
@eksfyt money,
@sigfyt money,
@tahHasY money,
@tahHasI money,
@otoPrk nvarchar(50),
@GNed int,
@SAdi int,
@SNot int,
@Blge int,
@saseno bigint,
@MinFiyat money
as

if exists (select plaka from tblTas where plaka = @plaka)
select h from tblson
else
begin
insert into tbltas (Sig,plaka,mrka,mdel,tip,mtr,sanz,sase,mtrNo,renk,kulTip,tekDet,hasar,Ibsl,Ibit,stat,Acik,km,detbil,sncFyt,eksfyt,sigfyt,tahHasY,tahHasI,otoPrk,GNed,SAdi,SNot,Blge,SaseNo,MinFiyat)
values (@Sig, @plaka, @mrka, @mdel, @tip, @mtr, @sanz, @sase, @mtrNo, @renk, @kulTip,@tekDet, @hasar, @Ibsl, @Ibit, @stat, @Acik, @km, @detbil, @sncFyt, @eksfyt, @sigfyt, @tahHasY, @tahHasI, @otoPrk, @GNed, @SAdi, @SNot, @Blge,@SaseNo,@MinFiyat)
select tstId from tbltas where @plaka = plaka
end
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-12 : 15:56:30

I do not see @dethas anywhere in the code that you posted. What is the data type of this variable?

Tara

aozgec
Starting Member

5 Posts

Posted - 2004-04-12 : 17:30:35

sorry .. @detbil

My query like this..

CREATE proc tasEkle
@Sig int,
@plaka nvarchar(12) ,
@mrka int ,
@mdel smallint,
@tip nvarchar(50),
@mtr nvarchar(50),
@sanz int,
@sase bigint,
@mtrNo bigint,
@renk int,
@kulTip int,
@tekDet nvarchar(500),
@hasar tinyint,
@Ibsl datetime,
@Ibit datetime,
@stat int,
@Acik nvarchar(100),
@km int,
@detbil nvarchar(500), ++++++++++++++++++++++
@sncFyt money,
@eksfyt money,
@sigfyt money,
@tahHasY money,
@tahHasI money,
@otoPrk nvarchar(50),
@GNed int,
@SAdi int,
@SNot int,
@Blge int,
@saseno bigint,
@MinFiyat money
as

if exists (select plaka from tblTas where plaka = @plaka)
select h from tblson
else
begin
insert into tbltas (Sig,plaka,mrka,mdel,tip,mtr,sanz,sase,mtrNo,renk,kulTip,tekDet,hasar,Ibsl,Ibit,stat,Acik,km,detbil,sncFyt,eksfyt,sigfyt,tahHasY,tahHasI,otoPrk,GNed,SAdi,SNot,Blge,SaseNo,MinFiyat)
values (@Sig, @plaka, @mrka, @mdel, @tip, @mtr, @sanz, @sase, @mtrNo, @renk, @kulTip,@tekDet, @hasar, @Ibsl, @Ibit, @stat, @Acik, @km, @detbil, @sncFyt, @eksfyt, @sigfyt, @tahHasY, @tahHasI, @otoPrk, @GNed, @SAdi, @SNot, @Blge,@SaseNo,@MinFiyat)
select tstId from tbltas where @plaka = plaka
end
GO

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-12 : 19:04:30

What is the error you are getting?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-12 : 19:28:13

I must have incorrectly typed it in when I did a CTRL+F. Oh well, I've removed my post.

Tara

aozgec
Starting Member

5 Posts

Posted - 2004-04-12 : 19:45:36

the procedure generates error if I assign a string containing spaces..

e.g.

this call generates errors..

exec tasekle @sig = 0,@plaka '125412', @mrka = 03 ,....., @detbil = 'string string",......

but this works well

exec tasekle @sig = 0,@plaka '125412', @mrka = 03 ,....., @detbil = 'string_string",......

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-12 : 19:50:21

There should be single quote after string.

EXEC tasekle .... @detbil = 'string string', ....

I think you posted a double quote. Since it is NVARCHAR, you need to put N before the string.

"Unicode constants are specified with a leading N: N'A Unicode string'."

So here ya go:

exec tasekle @sig = 0,@plaka '125412', @mrka = 03 ,....., @detbil = N'string string',......

Tara

aozgec
Starting Member

5 Posts

Posted - 2004-04-12 : 20:08:51

nop.. its still not working..

I receive this:
Incorrect syntax near 'string2'.

for this proc..

exec tasekle @sig = 0,@plaka '125412', @mrka = 03 ,....., @detbil = N'string1 string2',......

here is the whole command text ..

cmstr = "execute TasEkle " & _
" @Sig =" & Me.ddSigSirketi.Items(ddSigSirketi.SelectedIndex).Value & "," & _
" @plaka ='" & Me.txtbPlaka.Text & "'," & _
" @mrka =" & Me.ddMarka.Items(ddMarka.SelectedIndex).Value & "," & _
" @mdel =" & Me.ddMdl1Yil.Items(ddMdl1Yil.SelectedIndex).Value & "," & _
" @tip ='" & Me.txtbTip.Text & "'," & _
" @mtr ='" & Me.txtbMtr.Text & "'," & _
" @sanz =" & Me.ddSanziman.Items(ddSanziman.SelectedIndex).Value & "," & _
" @sase =" & Me.txtbSaseNo.Text & "," & _
" @mtrNo =" & Me.txtbMtr.Text & "," & _
" @renk =" & Me.ddRenk.Items(ddRenk.SelectedIndex).Value & "," & _
" @kulTip =" & Me.ddKultip.Items(ddKultip.SelectedIndex).Value & "," & _
" @tekDet = N'" & Me.txtbTekDet.Text & "'," & _
" @hasar =" & Me.ddHasar.Items(ddHasar.SelectedIndex).Value & "," & _ 'returns integer
" @Ibsl ='" & Me.txtbIhaBslSa.Text & "'," & _
" @Ibit ='" & Me.txtbIhaBitSa.Text & "'," & _
" @stat =" & Me.ddStatu.Items(ddStatu.SelectedIndex).Value & "," & _ 'returns integer
" @Acik ='" & Me.txtbAcik.Text & "'," & _
" @km =" & Me.txtbKm.Text & "," & _
" @detbil =" & Me.txtbDetay.Text & "," & _
" @sncFyt =" & Me.txtbSanFiyat.Text & "," & _
" @eksfyt =" & Me.txtbEkspFiyat.Text & "," & _
" @sigfyt =" & Me.txtbSigFiyat.Text & "," & _
" @tahHasY =" & Me.txtbTahHsrYed.Text & "," & _
" @tahHasI =" & Me.txtbTahHsrIsc.Text & "," & _
" @otoPrk ='" & Me.txtbOto.Text & "'," & _
" @GNed =" & Me.ddGelNed.Items(ddGelNed.SelectedIndex).Value & "," & _ 'returns integer
" @SAdi =" & Me.ddServis.Items(ddServis.SelectedIndex).Value & "," & _ 'returns integer
" @SNot =" & Me.ddSerNot.Items(ddSerNot.SelectedIndex).Value & "," & _ 'returns integer
" @Blge =" & Me.ddBolge.Items(ddBolge.SelectedIndex).Value & "," & _ 'returns integer
" @MinFiyat =" & Me.txtbMinFiyat.Text & "," & _
" @SaseNo =" & Me.txtbSaseNo.Text

I omit the quetos for money, and integer sqldatatypes,,

I use .Net 2003 VB and sql server 2000 On xppro..
I know that .net has some problems on server extensions, .net didnt have support for sql server..
can this be the reason???

Or is it a syntax error??

may be there is a character which bind the strings containing spaces.??

Thk u all...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-13 : 12:15:55

If you run the stored procedure in Query Analyzer instead of from your application, does it work there?

Tara

kroky
Starting Member

14 Posts

Posted - 2004-04-14 : 04:24:41

If you are constructing a strin (query) you should put all the string values in "'" and double (escape) the "'" if they exist in the string

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-04-14 : 08:47:59

@detbil =" & Me.txtbDetay.Text & "," & _

needs to be

@detbil = '" & Me.txtbDetay.Text & "'," & _

like this one

" @plaka ='" & Me.txtbPlaka.Text & "'," & _

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources