Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Unanswered: syntax error on dynamic SP

    I am some what new to stored procedures. Anyhow, I am attempting one and I am getting errors. I have overlooked my code numerious times and I can't figure out where I am going wrong....

    ALTER PROCEDURE sp_gener_queue2
    @ccr char(20),
    @tech char(10),
    @zone char(5),
    @status char(5),
    @begin char(10),
    @end char(10),
    @sort char(10)

    AS

    DECLARE @SQLString varchar(5000)

    SET @SQLString = 'SELECT wo_id, status, priority, customer_id, customer_name_1, customer_street_1, customer_city, customer_ac_1, customer_ph_1, customer_ac_2, customer_ph_2, customer_ac_3, customer_ph_3, access, call_type, eta_hour, eta_min, van_id, usr_create, tech_id, lname as tech_name, mfst_comment
    FROM (wo_master LEFT JOIN usr ON tech_id = usr_id) LEFT JOIN mfst_remarks ON wo_id = mfst_wo_id'

    if @ccr <> '' AND @tech = '' AND @zone = '' AND @status = ''
    begin
    SET @SQLString = @SQLString + 'WHERE usr_create ='+ @ccr +' AND date_est_run BETWEEN dbo.std2jul('+@begin+') AND dbo.std2jul('+@end+') ORDER BY '+ @sort
    end

    exec(@SQLString)

  2. #2
    Join Date
    Feb 2004
    Posts
    4
    It errors when the if statement = true

  3. #3
    Join Date
    Dec 2003
    Posts
    31
    add space before the 'where' statement ...

  4. #4
    Join Date
    Feb 2004
    Posts
    4
    Thanks for the help. I inserted the space and now I get a invalid column name. And what is interesting is that the column name stated is actaully the value of the @ccr variable.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Surround your variables with a function call, like you can see below:

    declare @str varchar(100)
    set @str = 'dflkjhdgh'
    select quotename(@str, '''')

  6. #6
    Join Date
    Feb 2004
    Posts
    4
    The error was resulting in the where statement. The following was the fix......

    SET @SQLString = @SQLString + ' WHERE usr_create = ''' + @ccr + ''' AND date_est_run BETWEEN dbo.std2jul('+@begin+') AND dbo.std2jul('+@end+')'

    Note the 3 single quotes around the @ccr variable.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    We're glad that you DID not

    ...and QUOTENAME(..., ...) will do exactly the same, BTW.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •