Post reply

Confusing set of strings!

  • December 9, 2009 at 12:03 pm

    #74576

    I am trying to assign this SQL below(it is a snippet of the whole thing) into a variable so that I can pass it to the sp_send_dbmail sp so that it will run the query and send the results thru email. My problem is all the single quotes and double quotes that has to be taken care of as you can see. I've tried adding an extra quote to each single quote, but constantly get errors in different parts of the query. I've spent 20mins doing this, but I keep running into errors. Do you know of an easier way to do this? Thanks.

    select coalesce(H.firstname+' '+H.lastname, I.firstname+' '+I.lastname) as appname, 'Too many Responses for Completed Application' as "Issue Description",D._name as "Client", A.applicant_id as "Object ID", 'AccountID' as "ID Type", 'declare @assessment_uid uniqueidentifier; exec Database1.dbo.'+C.ScoringSP+' '''+A.applicant_id+''','+convert(varchar(10),C.id)+', @assessment_uid output' as "How To Fix", 'select case when count(*)>0 then ''Issue Still Exists'' else ''Issue is fixed'' end

    from Database1.dbo.Assessment A with(nolock) inner join

    Database1.dbo.Assessment_Output B with(nolock) on A.uid=B.Assessment_uid inner join

    Database1.dbo.Client_Instrument C with(nolock) on C.id=A.Client_Instrument_id inner join

    Database1.dbo.Client D with(nolock) on D.id=C.Client_id left join

    Database2.dbo.AccountApplicants E on (convert(varchar(20),E.AccountId)=A.applicant_id and D._name=''Client2'') left join

    Database3.dbo.AccountApplicants F on (convert(varchar(20),F.AccountId)=A.applicant_id and D._name=''Client3'')

    where B.Instrument_NormStep_id is null

    and (E.AccountId is not null or F.AccountId is not null)

    and A.applicant_id='''+A.applicant_id+'''' as "How To Verify", A.created as "IssueDate"

    from Database1.dbo.Assessment A with(nolock) inner join....

  • December 9, 2009 at 11:10 pm

    #1090359

    Step 1... make sure the code works as is.

    Step 2... do a simple replace of ' with '' (two single quotes)

    Step 3... add a ' to the very begining and the very end

    Step 4... assign quoted data to long VARCHAR variable

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 10, 2009 at 4:40 am

    #1090447

    Another approach.

    Seems simpler to me.

    Step 1... make sure the code works as is.

    Step 2... wrap the code into stored procedure

    Step 3... make the query like this: 'EXEC dbo.StoredProcedure' [+ parameters]

    _____________
    Code for TallyGenerator

  • December 21, 2009 at 12:36 pm

    #1095011

    Thanks. I already knew the script worked. I was still running into problems so I just created a temp table to insert the data there and to create the aliases there and in turn, remove the aliases from the query. It then started running ok.

  • December 21, 2009 at 5:48 pm

    #1095194

    So, you're all set then?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 22, 2009 at 6:28 am

    #1095357

    Yes. Thanks again for your help!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply