SQL HELP Please how do we assign this text to a variable

Question

SQL HELP Please how do we assign this text to a variable

mw_sql_developer

SSCoach

Points: 19445
More actions
September 22, 2015 at 10:48 am

#308016

Folks,
Here is what Ia m trying to do ?
I have this big huge SQL statement ( See below ) and I am trying to see whether there is some quick magic way to asssign it to the variable @sql
Thanks a bunch.
Declare @sql NVARCHAR(MAX)
Set @sql = <????>
SELECT ClaimSurgical.FormNbr as ClaimIdentifier,ClaimSurgical.MemberTID, ClaimSurgical.Membernbr as MemberNumber, ClaimSurgical.AdmitDate AS ServiceDateYYYYMMDD,
CAST(CAST(ClaimSurgical.AdmitDate AS VARCHAR) AS DATETIME) ServiceDate,ClaimSurgical.ProviderNbr as ClaimPRVNO, ClaimSurgical.VendorNbr as ClaimTaxID,
ClaimSurgical.AssignedProvider1 as PCPPRVNO,ClaimSurgical.CAPVendor as PCPTaxID, EventCode_1.EventCodeTypeID, EventCode_1.EventCodeID, 1 AS EventSourceID,
EventCode_1.EventCode, ClaimSurgical.PlaceofService as POS,ClaimSurgical.MemberSex as Gender, DMember.MemberDateofBirth as DOB,
ClaimSurgical.GroupNbr, GETDATE() AS UpdatedDate, 'MHPDW Claims' AS UpdatedUser
FROM MHPDW.EDW.CLAIM.ClaimSurgical ClaimSurgical INNER JOIN
MHPDW.EDW.MEMBER.DMember DMember ON ClaimSurgical.MemberTID = DMember.MemberTID INNER JOIN
dbo.EventCode AS EventCode_1 ON ltrim(rtrim(ClaimSurgical.Surgical)) = ltrim(rtrim(EventCode_1.EventCode))
WHERE (ClaimSurgical.AdmitDate BETWEEN @FromDate AND @ToDate) AND (EventCode_1.EventCodeTypeID = 3) AND (ClaimSurgical.PlanCode IN ('MSA', 'MCR','MIC', 'CSH','HMP'))

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

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

Robert Klimes SSCoach Points: 17865 More actions · Answer 1

you just need to put your query within the single quotes of the assignment and then qualify all your single quotes with the query.

Declare @sql NVARCHAR(MAX)

Set @sql = '

SELECT ClaimSurgical.FormNbr as ClaimIdentifier,ClaimSurgical.MemberTID, ClaimSurgical.Membernbr as MemberNumber, ClaimSurgical.AdmitDate AS ServiceDateYYYYMMDD,

CAST(CAST(ClaimSurgical.AdmitDate AS VARCHAR) AS DATETIME) ServiceDate,ClaimSurgical.ProviderNbr as ClaimPRVNO, ClaimSurgical.VendorNbr as ClaimTaxID,

ClaimSurgical.AssignedProvider1 as PCPPRVNO,ClaimSurgical.CAPVendor as PCPTaxID, EventCode_1.EventCodeTypeID, EventCode_1.EventCodeID, 1 AS EventSourceID,

EventCode_1.EventCode, ClaimSurgical.PlaceofService as POS,ClaimSurgical.MemberSex as Gender, DMember.MemberDateofBirth as DOB,

ClaimSurgical.GroupNbr, GETDATE() AS UpdatedDate, ''MHPDW Claims'' AS UpdatedUser

FROM MHPDW.EDW.CLAIM.ClaimSurgical ClaimSurgical INNER JOIN

MHPDW.EDW.MEMBER.DMember DMember ON ClaimSurgical.MemberTID = DMember.MemberTID INNER JOIN

dbo.EventCode AS EventCode_1 ON ltrim(rtrim(ClaimSurgical.Surgical)) = ltrim(rtrim(EventCode_1.EventCode))

WHERE (ClaimSurgical.AdmitDate BETWEEN @FromDate AND @ToDate) AND (EventCode_1.EventCodeTypeID = 3) AND (ClaimSurgical.PlanCode IN (''MSA'', ''MCR'',''MIC'', ''CSH'',''HMP''))'

if you are executing this query once it is assigned you would used sp_executesql like so

exec sp_executesql @sql,N'@FromDate datetime,ToDate datetime',@FromDate = @FromDate,@ToDate = @ToDate

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

mw_sql_developer SSCoach Points: 19445 More actions · Answer 2

Thanks

Ahh.. but you still have to manually go and replace every single quote with a double single quote.

I was thinking whether there was a way to get around that easily... Looks like there isn't.

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 3

Whenever I have to do this (convert Regular SQL to doubled-quoted SQL to insert into a @variable) I cut & paste the SQL statement to a new, empty, editing window (or Notepad would do), do a Find & Replace of all single-quote to double-them-up, and then cut & paste it back again to the

@sql = 'xxx'

I find this easier than doing a FIND for the first single-quote, doubling that up, F3 to find the next one ... repeat ... which is the only other alternative that occurs to me.

But if anyone has a smarter way I'd like to hear it.

When we use OPENQUERY() where we often have to quadruple-quote we do it in two stages:

SELECT @sql = 'SELECT Col1, Col2 FROM MyTable WHERE FOO = ''BAR'' '

to get the initial SQL syntax and then

SELECT @sql =

'INSERT INTO ##TEMP_xxx

SELECT*

FROM OPENQUERY([LinkedServerName], '''

+ REPLACE(@SQL, '''', '''''')-- Double up the quotes

+ ''') AS T'

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

mw112009 (9/23/2015)
Thanks
Ahh.. but you still have to manually go and replace every single quote with a double single quote.
I was thinking whether there was a way to get around that easily... Looks like there isn't.

You can use Ctrl + H. 😉

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

nick.hinton SSC Eights! Points: 956 More actions · Answer 5

You can wrap your query in double quotes:

declare @sql varchar(max)

set quoted_identifier off

set @sql = "SELECT Col1, Col2 FROM MyTable WHERE FOO = 'BAR' "

-- remember to reset quoted identifier after running

set quoted_identifier on