can you please correct this syntax.........

[sanika1507]

hi this a part of the stored proc .....Actually the syntax is a problem .....i mean only the quotes ...i m getting an error in the stmt below. Can any one check this and let me knw

set @strCd = 'Cd =' + @Cd + @strCC + @strCd + 'and'+ Interface = 'P' and StatusId Not In (4,8)

thanks a lot in advance

[Shashi Sadasivan]

No clue on what you are trying to do
but look at the = operator, it is not sorrounded in quotes.

that might be ther error.

This is trying to locate the error.....if you provide what error you recieved it would help a lot better

cheers

[sanika1507]

hi this a part of the stored proc .....Actually the syntax is a problem .....i mean only the quotes ...i m getting an error in the stmt below. Can any one check this and let me knw

set @strCd = 'Cd =' + @Cd + @strCC + @strCd + 'and'+ Interface = 'P' and StatusId Not In (4,8)

thanks a lot in advance

Hi actually

this is a stored proc which i have


@Cd Char(11),
@Id int,
@opt int,

AS
BEGIN
SET NOCOUNT ON

if @opt = 1 Begin
Select * from C_Eng with(nolock) Where Id = @d And SysInd = 'P' And statusId Not In (4,8)
End

if @opt = 2 Begin
Select * from C_Eng with(nolock) Where Cd = @Cd And SysInd = 'P' And StatusId Not In (4,8)
End

if @opt = 3 Begin
Select * from C_Eng with(nolock) Where Id = @Id And Cd = @Cd
And SysInd = 'P' And StatusId Not In (4,8)
End


SET NOCOUNT OFF
END



Now i want to modify this stored proc and make it dynamic, and also i want to include the startdate and end date so tht i can select the id which r created between the start and end date . the start date and end date have to be dynamic. if the end date is not given then we giv present date as the end date

so i have modified it like this ...

set @strMain = 'Select * from C_Eng with(nolock) Where '
@strId varchar(100)
Set @strId = ''
@strCd varchar(100)
Set @strCd = ''

if @Id is not null and rtrim(ltrim(@Id)) <> ''
set strId = 'Id = ' + @Id

if @Cd is not null and rtrim(ltrim(@Cd)) <> ''
set @strCd = 'Cd = ' + @Cd + @strId + @strCd + ' And SysInd = 'P' And StatusId Not In (4,8)


if @startDate is not null and rtrim(ltrim(@startDate)) <> '' and @enddate is not null and rtrim(ltrim(@enddate)) <> ''
set strOriginDate = ' and OriginDate between ' + @StartDate + ' and ' + @EndDate


set strMain = @strMain + @strCCR +
print strMain


and the foll are the errors
above i have declare the variables also ........Can u plZZZZZZZZZZZ help
Server: Msg 137, Level 15, State 1, Line 10
Must declare the variable '@strMain'.
Server: Msg 170, Level 15, State 1, Line 16
Line 16: Incorrect syntax near '='.
Server: Msg 170, Level 15, State 1, Line 19
Line 19: Incorrect syntax near 'P'.
Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near '='.
Server: Msg 128, Level 15, State 1, Line 27
The name 'strMain' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

IT WUD BE GR8 IF U HELP ME
THANKS A LOT IN ADVANCE

[azimmer]

Hi actually

this is a stored proc which i have


@Cd Char(11),
@Id int,
@opt int,

AS
BEGIN
SET NOCOUNT ON

if @opt = 1 Begin
Select * from C_Eng with(nolock) Where Id = @d And SysInd = 'P' And statusId Not In (4,8)
End

if @opt = 2 Begin
Select * from C_Eng with(nolock) Where Cd = @Cd And SysInd = 'P' And StatusId Not In (4,8)
End

if @opt = 3 Begin
Select * from C_Eng with(nolock) Where Id = @Id And Cd = @Cd
And SysInd = 'P' And StatusId Not In (4,8)
End


SET NOCOUNT OFF
END



Now i want to modify this stored proc and make it dynamic, and also i want to include the startdate and end date so tht i can select the id which r created between the start and end date . the start date and end date have to be dynamic. if the end date is not given then we giv present date as the end date

so i have modified it like this ...

set @strMain = 'Select * from C_Eng with(nolock) Where '
@strId varchar(100)
Set @strId = ''
@strCd varchar(100)
Set @strCd = ''

if @Id is not null and rtrim(ltrim(@Id)) <> ''
set strId = 'Id = ' + @Id

if @Cd is not null and rtrim(ltrim(@Cd)) <> ''
set @strCd = 'Cd = ' + @Cd + @strId + @strCd + ' And SysInd = 'P' And StatusId Not In (4,8)


if @startDate is not null and rtrim(ltrim(@startDate)) <> '' and @enddate is not null and rtrim(ltrim(@enddate)) <> ''
set strOriginDate = ' and OriginDate between ' + @StartDate + ' and ' + @EndDate


set strMain = @strMain + @strCCR +
print strMain


and the foll are the errors
above i have declare the variables also ........Can u plZZZZZZZZZZZ help
Server: Msg 137, Level 15, State 1, Line 10
Must declare the variable '@strMain'.
Server: Msg 170, Level 15, State 1, Line 16
Line 16: Incorrect syntax near '='.
Server: Msg 170, Level 15, State 1, Line 19
Line 19: Incorrect syntax near 'P'.
Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near '='.
Server: Msg 128, Level 15, State 1, Line 27
The name 'strMain' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

IT WUD BE GR8 IF U HELP ME
THANKS A LOT IN ADVANCE

A couple syntax corrections in bold:

 declare @strMain varchar(100)
declare @Id varchar(100)
declare @Cd varchar(100)
declare @startdate varchar(100)
 
set @strMain = 'Select * from C_Eng with(nolock) Where '
 declare @strId varchar(100)
Set @strId = ''
 declare @strCd varchar(100)
Set @strCd = ''
 
if @Id is not null and rtrim(ltrim(@Id)) <> ''
set @strId = 'Id = ' + @Id
 
if @Cd is not null and rtrim(ltrim(@Cd)) <> ''
set @strCd = 'Cd = ' + @Cd + @strId + @strCd + ' And SysInd = ''P'' And StatusId Not In (4,8)'
 
 
if @startDate is not null and rtrim(ltrim(@startdate)) <> '' and @enddate is not null and rtrim(ltrim(@enddate)) <> ''
set @strOriginDate = ' and OriginDate between ' + @startdate + ' and ' + @EndDate
 
 
 -- set strMain = @strMain + @strCCR +
print @strMain
 

Apart from this make sure your code makes sense. E.g. around the WHERE statement if you have no WHERE clause indeed...

Note: Some corrections do not show up in bold. See the code at "Reply".

[sanika1507]

A couple syntax corrections in bold:

 declare @strMain varchar(100)
declare @Id varchar(100)
declare @Cd varchar(100)
declare @startdate varchar(100)
 
set @strMain = 'Select * from C_Eng with(nolock) Where '
 declare @strId varchar(100)
Set @strId = ''
 declare @strCd varchar(100)
Set @strCd = ''
 
if @Id is not null and rtrim(ltrim(@Id)) <> ''
set @strId = 'Id = ' + @Id
 
if @Cd is not null and rtrim(ltrim(@Cd)) <> ''
set @strCd = 'Cd = ' + @Cd + @strId + @strCd + ' And SysInd = ''P'' And StatusId Not In (4,8)'
 
 
if @startDate is not null and rtrim(ltrim(@startdate)) <> '' and @enddate is not null and rtrim(ltrim(@enddate)) <> ''
set @strOriginDate = ' and OriginDate between ' + @startdate + ' and ' + @EndDate
 
 
 -- set strMain = @strMain + @strCCR +
print @strMain
 

Apart from this make sure your code makes sense. E.g. around the WHERE statement if you have no WHERE clause indeed...

Note: Some corrections do not show up in bold. See the code at "Reply".

/thanks a lot . i relaised my mistakes and now it fine ....yea by mistake i dint put the where clause ..i was in hurry .got to submit it so.....thnaks a lot for ur patience and advice