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

  • 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'))

  • 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]

  • 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.

  • 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'

  • 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
  • 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

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

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