Syntax error in store procedure

[anubhaw gupta]

here is my piece of code alter procedure mdi_anu ( @entry_date varchar(20), @updated_date varchar(20), @status varchar(20), @cno varchar(20), @eno varchar(20)) as declare @SQL varchar(max) set @SQL='SELECT * FROM anu123 where 1=1 ' if (@status='ALL') begin set @status='ALL' end if (@status='A') begin set @status='A' end if (@status='R') begin set @status='R' end if (@status='D') begin set @status='D' end if (@status='P') begin set @status='P' end if (@entry_date<>'' and @updated_date<>'') begin set @entry_date =CONVERT (datetime, @entry_date, 103) set @updated_date= CONVERT(datetime, @updated_date, 103) set @SQL=@SQL+'and entry_date>=''' +@entry_date+'''and updated_date<='''+@updated_date+'''' end if (@cno<>'') begin set @cno=@cno set @SQL=@SQL+'and cno=''' +@cno end if (@eno<>'') begin set @eno=@eno set @SQL=@SQL+'and eno=''' +@eno end exec (@SQL) End Can anyone tell me,where am wrong? stuck from 2 hr. Saying incorrect syntax near the last end :|

[Sravanthid28]

U have to write BEGIN Keyword after As Statement

[anubhaw gupta]

Hey i got it. but that's not compulsory i guess. :|

[anubhaw gupta]

alter procedure mdi_anu
(
@entry_date varchar(20),
@updated_date varchar(20),
@status varchar(20),
@cno varchar(20),
@eno varchar(20)
)

as
begin
declare @SQL varchar(max)
set @SQL='SELECT * FROM anu123 where 1=1 '

--if (@status='ALL')  
--	begin  
--	set @status='ALL'  
--	set @SQL=@SQL + 'and status=''' +@status
--end  

if (@status='A')  
	begin  
	set @status='A'  
	--set @SQL=@SQL+' and status='''+@status
end  

if (@status='R')  
	begin  
	set @status='R'  
	set @SQL=@SQL+' and status=''' +@status
end  

if (@status='D')  
	begin  
	set @status='D'  
	set @SQL=@SQL+' and status=''' +@status
end  

if (@status='P')  
	begin  
	set @status='P'  
	set @SQL=@SQL+' and status=''' +@status
end 


 
if (@entry\_date<>'' and @updated\_date<>'')  
begin  

set @entry\_date =CONVERT (datetime, @entry\_date, 103)        
set @updated\_date= CONVERT(datetime, @updated\_date, 103)  
set @SQL=@SQL+'and entry\_date>=''' +@entry\_date+'''and updated\_date<='''+@updated\_date+''''  
end

if (@cno<>'')  
begin  
set @cno=@cno  
set @SQL=@SQL+'and cno=''' +@cno+
end  


if (@eno<>'')  
begin  
set @eno=@eno  
set @SQL=@SQL+'and eno=''' +@eno+
end 

exec (@SQL)

end

the error which m getting is that,suppose m choosing 'D' as my input in the status i get "Unclosed quotation mark after the character string 'D'. Incorrect syntax near 'D'." Whats the problem?

[sathish s]

May be you should append a single quotes after +@status in all your if conditions, if it a varchar field?

[Zafar Sultan]

Include a space before and after every string. For example instead of writing 'and eno=''' write ' and eno='' ' Hope it helps. If it does not instead of exec(@SQL) in your SP write print(@SQL) and you will have the complete SQL Query. That will clear the picture to a good deal where the error is.