|
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
|
|
|
|
|
U have to write BEGIN Keyword after As Statement
|
|
|
|
|
Hey i got it. but that's not compulsory i guess.
|
|
|
|
|
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?
|
|
|
|
|
May be you should append a single quotes after +@status in all your if conditions, if it a varchar field?
|
|
|
|
|
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.
|
|
|
|