December 9, 2009 at 12:03 pm
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
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
Change is inevitable... Change for the better is not.
December 10, 2009 at 4:40 am
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
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
So, you're all set then?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 22, 2009 at 6:28 am
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