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

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • sanika1507
    New Member
    • Sep 2007
    • 34
    #1

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

    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
    Tags: None
    • Shashi Sadasivan
      Recognized Expert Top Contributor
      • Aug 2007
      • 1435
      #2
      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

        Comment

        • sanika1507
          New Member
          • Sep 2007
          • 34
          #3
          Originally posted by 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(@st artDate)) <> '' and @enddate is not null and rtrim(ltrim(@en ddate)) <> ''
          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

            Comment

            • azimmer
              Recognized Expert New Member
              • Jul 2007
              • 200
              #4
              Originally posted by sanika1507
              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(@st artDate)) <> '' and @enddate is not null and rtrim(ltrim(@en ddate)) <> ''
              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:
              Code:
              [B]declare @strMain varchar(100)
declare @Id varchar(100)
declare @Cd varchar(100)
declare @startdate varchar(100)
[/B]
set @strMain = 'Select * from C_Eng with(nolock) Where '
[B]declare [/B]@strId varchar(100)
Set @strId = ''
[B]declare [/B]@strCd varchar(100)
Set @strCd = ''

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

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


if @startDate is not null and rtrim(ltrim([B]@startdate[/B])) <> '' and @enddate is not null and rtrim(ltrim(@enddate)) <> ''
set [B]@[/B]strOriginDate = ' and OriginDate between ' + [B]@startdate[/B] + ' and ' + @EndDate


[B]--[/B] set strMain = @strMain + @strCCR +
print [B]@[/B]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".
              Last edited by azimmer; Oct 5 '07, 01:53 PM. Reason: Note added

                Comment

                • sanika1507
                  New Member
                  • Sep 2007
                  • 34
                  #5
                  Originally posted by azimmer
                  A couple syntax corrections in bold:
                  Code:
                  [B]declare @strMain varchar(100)
declare @Id varchar(100)
declare @Cd varchar(100)
declare @startdate varchar(100)
[/B]
set @strMain = 'Select * from C_Eng with(nolock) Where '
[B]declare [/B]@strId varchar(100)
Set @strId = ''
[B]declare [/B]@strCd varchar(100)
Set @strCd = ''

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

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


if @startDate is not null and rtrim(ltrim([B]@startdate[/B])) <> '' and @enddate is not null and rtrim(ltrim(@enddate)) <> ''
set [B]@[/B]strOriginDate = ' and OriginDate between ' + [B]@startdate[/B] + ' and ' + @EndDate


[B]--[/B] set strMain = @strMain + @strCCR +
print [B]@[/B]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

                    Comment

                    Previous template Next
                    Working...