passing variables to functions in openquery

1 Jul 2005 6:36 AM

Kavie

how can i pass a declared variable like @myVar to a function which is inside
a package in an Oracle DB
I am running the script from "SQL Query Analyzer"

Part of the SQL server script is like this:

set @stat = (select * from openquery(MYLINKEDSERVER, 'select
MYPACKAGE.MAYFUNCTION(' + @myVar + ') from MYTABLE where MYCOLMN = 1'))

I get error msg 170 " Incorrect syntax near '+' "
I have tried ''' + @myVar + ''' but it still gives the same error
If I don't use + signs the string literal @myVar would be passed which is
not what i want.
Would you please help?

1 Jul 2005 6:53 AM

Tibor Karaszi

AFAIK, you can't. Build the whole query in a variable and execute that variable.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Show quote

"Kavie" <Ka***@discussions.microsoft.com> wrote in message
news:E1F11274-02FC-4CC4-8114-42A752CD5D07@microsoft.com...
> how can i pass a declared variable like @myVar to a function which is inside
> a package in an Oracle DB
> I am running the script from "SQL Query Analyzer"
>
> Part of the SQL server script is like this:
>
> set @stat = (select * from openquery(MYLINKEDSERVER, 'select
> MYPACKAGE.MAYFUNCTION(' + @myVar + ') from MYTABLE where MYCOLMN = 1'))
>
> I get error msg 170 " Incorrect syntax near '+' "
> I have tried ''' + @myVar + ''' but it still gives the same error
> If I don't use + signs the string literal @myVar would be passed which is
> not what i want.
> Would you please help?

1 Jul 2005 2:30 PM

JosephPruiett

Put your variable in double single quotes on before and after + sign and that
should do it as long as you are referencing a real linkedserver and table and
column.

declare @stat nvarchar(4000)

set @stat = (select * from openquery(MYLINKEDSERVER, 'select
MYPACKAGE.MAYFUNCTION('' + @myVar + '') from MYTABLE where MYCOLMN = 1'))

Show quote

"Kavie" wrote:

> how can i pass a declared variable like @myVar to a function which is inside
> a package in an Oracle DB
> I am running the script from "SQL Query Analyzer"
>
> Part of the SQL server script is like this:
>
> set @stat = (select * from openquery(MYLINKEDSERVER, 'select
> MYPACKAGE.MAYFUNCTION(' + @myVar + ') from MYTABLE where MYCOLMN = 1'))
>
> I get error msg 170 " Incorrect syntax near '+' "
> I have tried ''' + @myVar + ''' but it still gives the same error
> If I don't use + signs the string literal @myVar would be passed which is
> not what i want.
> Would you please help?