Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2005
    Location
    Kansas
    Posts
    11

    Question Unanswered: Help needed concatenating a tsql statement

    I'm almost embarrassed to even post this but I am totally stumped.

    I have a tsql insert statement that I am concatenating into a variable named @tSql and then running it via exec(@tSql). Well the table it is supposed to be inserting into remains empty so it ain't workin.

    In the past I run the statement in QA by right clicking the name of the sp in the object browser and selecting open. This opens a panel that allows me to input values for my sp. I put my values in there, hit 'Execute' and a window opens that isn't printing the varchar contents of my @tSql!!

    It appears that my concatentation isn't working

    My first attempt at adding to the string based on some logic fails! Can someone tell me where my syntax might be failing...and if the syntax is right ...wtf?

    if @NewTrainer1 is not NULL
    begin
    set @strSql = @strSql + convert(varchar,@NewExtTrainer1ID) + ', '
    end
    print (@strSql) < --- won't print

  2. #2
    Join Date
    Mar 2005
    Location
    Kansas
    Posts
    11
    **** UPDATE ****
    Actually my concatenation is working up to a point. For some reason it doesn't like this:

    set @strSql = @strSql + '''' + @ClassNotes + ''''

    I'm attempting to put a single tic around a variable which could be null and in the case of my sp test is null.

    When I added the check it prints fine. Dam I really need to improve my debugging. I'll have to revisit The Debugger's Handbook.

  3. #3
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Did you manually set the CONCAT_NULL_YIELDS_NULL switch off?

    If not, depending upon your default, concatinating a null to any string will yield a null result. Sounds like that could have been your problem.

    Look it up in BOL for more information.

    -- This is all just a Figment of my Imagination --

  4. #4
    Join Date
    Mar 2005
    Location
    Kansas
    Posts
    11
    Thanks for the tip. I checked it out in BOL and using DATABASEPROPERTYEX('MyDB','IsNullConcat') returned a zero which I reckon means it is off (I'm on sql server 2000 sp 2 btw). I'm using a machine that a previous developer had used so perhaps they set it? Its also off on our test and production machines

  5. #5
    Join Date
    Feb 2004
    Posts
    88
    you have to be a bit careful with this setting, it is a bit of a moveable feast...for example, see this in BOL..."Connection-level settings (set using the SET statement) override the default database setting for CONCAT_NULL_YIELDS_NULL. By default, ODBC and OLE DB clients issue a connection-level SET statement setting CONCAT_NULL_YIELDS_NULL to ON for the session when connecting to SQL Server. " . Also your client tools can override this value (see the tools/options/connection properties tab in query analyzer.)

    I always (if there's any doubt about it) wrap an ISNULL around anything doubtful I'm concatenating, so:

    SET @sql = ISNULL(@sql,'') + ISNULL(@something_else, '')

Posting Permissions

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