help!I can't find the 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

SQL Server 2000 Forums

SQL Server Development (2000)

help!I can't find the error

Author

Topic

pipi
Starting Member

18 Posts

Posted - 2005-06-15 : 02:49:07

hi!!!i have this stored procedure
CREATE PROCEDURE [dbo].[GetNacisPelaMeso]
@pela varchar(6),
@start datetime,
@end datetime,
@dbase varchar(20)
AS

declare @curloga varchar(20)
declare @mesoypol money
Declare @SQL varchar(5000)

--declare @start datetime
--declare @end datetime

--set @start = '1/1/2005'
--set @start = dateadd(year, (year(getdate())-year(@start)), @start)
--set @end= getdate()

set dateformat dmy;
set @mesoypol=0.00

Create Table #tempnakis (
Typos varchar (1),
Loga varchar(20),
Pela varchar(6),
Meso money,
Ypoloipo money,
Esoda money,
Eksoda money
)

set @SQL='INSERT INTO #tempnakis select pls_typo_loga as Typos,
pls_loga_kodi as Loga,
pls_loga_pela Pela,
Meso='+convert(varchar(30),@mesoypol)+',
case pls_typo_loga
when ''5'' then
(select isnull(sum(xok_poso_xreo)-sum(xok_poso_pist),0.00)
from '+@dbase+'.[DBO].xok
where xok_loga_xori=pls_loga_kodi
and datediff(day,'+''''+convert(varchar(30),@end)+''''+',xok_hmer_syna)<=0)
when ''3'' then
(select isnull(sum(kok_poso_xreo)-sum(kok_poso_pist),0.00)
from '+@dbase+'.[DBO].kok
where kok_loga_opse=pls_loga_kodi
and datediff(day,'+''''+convert(varchar(30),@end)+''''+',kok_hmer_syna)<=0)
when ''1'' then
(select isnull(sum(ktk_poso_xreo)-sum(ktk_poso_pist),0.00)
from '+@dbase+'.[DBO].ktk
where ktk_loga_tami=pls_loga_kodi
and datediff(day,'+''''+convert(varchar(30),@end)+''''+',ktk_hmer_syna)<=0)
when ''2'' then
(select isnull(sum(kpk_poso_xreo)-sum(kpk_poso_pist),0.00)
from '+@dbase+'.[DBO].kpk
where kpk_loga_prou=pls_loga_kodi
and datediff(day,'+''''+convert(varchar(30),@end)+''''+',kpk_hmer_syna)<=0)
end as Ypoloipo,
case pls_typo_loga
when ''5'' then
(select isnull(sum(xok_poso_xreo)-sum(xok_poso_pist),0.00)
from '+@dbase+'.[DBO].xok,'+@dbase+'.[DBO].gen
where xok_loga_xori=pls_loga_kodi and xok_kodi_praj=gen02
and gen00 like ''PRAJH'' AND GEN01=''XORIGHSHS'' AND substring(gen05,2,1)=''×''
and datediff(day,'+''''+convert(varchar(30),@end)+''''+',xok_hmer_syna)<=0)
when ''3'' then
(select isnull(sum(kok_poso_xreo)-sum(kok_poso_pist),0.00)
from '+@dbase+'.[DBO].kok,'+@dbase+'.[DBO].gen
where kok_loga_opse=pls_loga_kodi and kok_kodi_praj=gen02
and gen00 like ''PRAJH'' AND GEN01=''OPSEOS'' AND substring(gen05,2,1)=''×''
and datediff(day,'+''''+convert(varchar(30),@end)+''''+',kok_hmer_syna)<=0)
when ''1'' then
(select isnull(sum(ktk_poso_xreo)-sum(ktk_poso_pist),0.00)
from '+@dbase+'.[DBO].ktk,'+@dbase+'.[DBO].gen
where ktk_loga_tami=pls_loga_kodi and ktk_kodi_praj=gen02
and gen00 like ''PRAJH'' AND GEN01=''TAMIEYTIRI'' AND substring(gen05,2,1)=''×''
and datediff(day,'+''''+convert(varchar(30),@end)+''''+',ktk_hmer_syna)<=0)
when ''2'' then
(select isnull(sum(kpk_poso_xreo)-sum(kpk_poso_pist),0.00)
from '+@dbase+'.[DBO].kpk,'+@dbase+'.[DBO].gen
where kpk_loga_prou=pls_loga_kodi and kpk_kodi_praj=gen02
and gen00 like ''PRAJH'' AND GEN01=''PROUESMIAS'' AND substring(gen05,2,1)=''×''
and datediff(day,'+''''+convert(varchar(30),@end)+''''+',kpk_hmer_syna)<=0)
end as Esoda,
case pls_typo_loga
when ''5'' then
(select isnull(sum(xok_poso_xreo)-sum(xok_poso_pist),0.00)
from '+@dbase+'.[DBO].xok, '+@dbase+'.[DBO].gen
where xok_loga_xori=pls_loga_kodi and xok_kodi_praj=gen02
and gen00 like ''PRAJH'' AND GEN01=''XORIGHSHS'' AND substring(gen05,2,1)=''Ð''
and datediff(day,'+''''+convert(varchar(30),@end)+''''+',xok_hmer_syna)<=0)
when ''3'' then
(select isnull(sum(kok_poso_xreo)-sum(kok_poso_pist),0.00)
from '+@dbase+'.[DBO].kok, '+@dbase+'.[DBO].gen
where kok_loga_opse=pls_loga_kodi and kok_kodi_praj=gen02
and gen00 like ''PRAJH'' AND GEN01=''OPSEOS'' AND substring(gen05,2,1)=''Ð''
and datediff(day,'+''''+convert(varchar(30),@end)+''''+',kok_hmer_syna)<=0)
when ''1'' then
(select isnull(sum(ktk_poso_xreo)-sum(ktk_poso_pist),0.00)
from '+@dbase+'.[DBO].ktk, '+@dbase+'.[DBO].gen
where ktk_loga_tami=pls_loga_kodi and ktk_kodi_praj=gen02
and gen00 like ''PRAJH'' AND GEN01=''TAMIEYTIRI'' AND substring(gen05,2,1)=''Ð''
and datediff(day,'+''''+convert(varchar(30),@end)+''''+',ktk_hmer_syna)<=0)
when ''2'' then
(select isnull(sum(kpk_poso_xreo)-sum(kpk_poso_pist),0.00)
from '+@dbase+'.[DBO].kpk, '+@dbase+'.[DBO].gen
where kpk_loga_prou=pls_loga_kodi and kpk_kodi_praj=gen02
and gen00 like ''PRAJH'' AND GEN01=''PROUESMIAS'' AND substring(gen05,2,1)=''Ð''
and datediff(day,'+''''+convert(varchar(30),@end)+''''+',kpk_hmer_syna)<=0)
end as Eksoda
from '+@dbase+'.[DBO].pls
where pls_loga_pela='+@pela+' and pls_pela_typo like ''1''
order by pls_loga_kodi '

exec(@SQL)
set @curloga = (select top 1 loga from #tempnakis)

while (@curloga is not null )
begin

exec csbxalkida.[DBO].GetPelaLogaMesoYpol @pela, @curloga, @start, @end, @mesoypol output

if (@mesoypol is null)
begin
set @mesoypol = 0.00
end

update #tempnakis
set meso = @mesoypol
where loga=@curloga

set @curloga = (select top 1 loga from #tempnakis where @curloga < loga)

end
select Typos,Pela,Loga,Meso,Ypoloipo,Esoda,Eksoda
from #tempnakis

drop table #tempnakis
GO

---
when i execute it:
declare @date1 datetime
declare @date2 datetime
declare @xrhsh varchar(30)
declare @base varchar(20)

set @date2=getdate()
set @date1=getdate()

set @base='csbxalkida'

exec NACIS.[DBO].GetNacisPelaMeso '00001',@date1,@date2,@base
occurs this error:

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '. ' to a column of data type int.

i can not find why..........

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-06-15 : 11:36:31

Try changing the second line above "exec(@SQL)" to this:

where pls_loga_pela = ''' + @pela + ''' and pls_pela_typo like ''1''

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-15 : 11:45:20

You got more problems than just syntax problems...

You need to get much simpler...what is this suppose to be doing?

And why the dynamic SQL?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

pipi
Starting Member

18 Posts

Posted - 2005-06-16 : 01:50:51

Goodmorning!
I have two databases,NACIS and (CSBXALKIDA OR CSBKOZANI OR CSBPATRA...)
These databases have the same main tables
and NACIS has only one table,where i save the returned rows of this procedure,when i executed in my program.
This stored procedure is in database NACIS.iwant to pass as a parameter the name of database,so the procedure works for each database(CSBXALKIDA OR CSBKOZANI OR CSBPATRA...)

So i created a @dbase varchar(20),but the select statement didn't work,occured syntax error near from
e.g
from @dbase.[dbo].tablename

and the solution was to use dynamic SQL.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-16 : 02:08:31

Refer this thread
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50769

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources