Results 1 to 1 of 1
  1. #1
    Join Date
    Oct 2003
    Location
    South Carolina, USA
    Posts
    2

    Unhappy Unanswered: dynamic SQL leading zeros truncated

    Hey ALL,

    When displaying a CHAR local variable that being with zeros within an EXEC command, the leading zeros are truncated. When executed outside of EXEC it works. How do I resolve this? Has this happen to anyone else? The following is the code/results.

    CODE:
    set showplan on
    declare @db varchar(25), @remote_login varchar(50),
    @SQL varchar(255), @prov_num char(10)

    -- Get current remote login id
    select @remote_login = (select suser_name())

    -- Get default database of remote login name..
    select @db = (select dbname from master..syslogins where name = @remote_login)

    select @prov_num = "0002306001"

    exec ('if not exists (select rsp_recipient_num from ' + @db + '..RSP_tbl where rsp_recipient_num = "' + @prov_num + '")
    begin
    raiserror 70501 "No value in table %1!..RSP for %2!", ' + @db + ',' + @prov_num +
    ' print "return status -400"
    end' )

    -- exec(@SQL)

    if not exists (select rsp_recipient_num from mevs_test..RSP_tbl where rsp_recipient_num = "@prov_num")
    begin
    raiserror 70501 "RSP record %1! does not exist", @prov_num
    print "return status -400"
    end


    set showplan off

    RESULTS:
    Server Message: Number 70501, Severity 16
    Line 3:
    No value in table mevs_test..RSP for 230600
    return status -400
    Server Message: Number 70501, Severity 16
    Line 22:
    RSP record 000230600 does not exist
    return status -400
    Last edited by Jacksnb; 01-07-05 at 16:43. Reason: Add greeting..

Posting Permissions

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