Date:         Fri, 25 Feb 2005 23:43:28 -0500
Reply-To:     Larry Bertolini <bertolini.1@OSU.EDU>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Larry Bertolini <bertolini.1@OSU.EDU>
Organization: Ohio State University
Subject:      Re: Q: use mvar in SqlServer
Content-Type: text/plain; charset=us-ascii; format=flowed

Ron,

I'm assuming that TEST_ID_NUM is defined as
a "char" or "varchar".

By default, SQL Server connections have
"SET QUOTED_IDENTIFIER ON"; one implication
is that character strings must be quoted
with single quotes ('), not double quotes (").

If you specify:
   %LET PARM=225;
   TEST_ID_NUM = "&PARM"

then SQL Server will complain.

If you specify:
   %LET PARM=225;
   TEST_ID_NUM = '&PARM'

then SAS won't resolve &PARM.

But, if you specify:
   %LET PARM='225'
   TEST_ID_NUM = &PARM

then SAS will resolve &PARM to '225',
and SQL Server will see a character string
quoted by single quotes, and harmonic
convergence will be attained.


Fehd, Ronald J. PHPPO wrote:

> A user wants to know how to pass a parameter value
> into an SqlServer query
>
> %Let Parm=225;
> proc sql;
> connect to sqlservr(server  =Abc-DEF
>                     database=TheBigOne
>                     user    =me
>                     password=XXYYZZ);
> CREATE VIEW pt AS
> SELECT *
> FROM connection to sqlservr
> (SELECT p.clia_num, score_num, labtype, test_yr
>  FROM pt_test_results AS p
>  JOIN pt_oscar_data AS o
>    ON p.clia_num = o.clia_num
>  WHERE termcd1 = '00'
>    AND TEST_ID_NUM = "&Parm.");
> disconnect from sqlservr;
>
> The above does not work;
> I replied that the mvar was not being resolved
>     not by SAS,
> but not by SqlServer.
>
> any kludges appreciated
>
> Ron Fehd
> CDC SAS site representative