# Database Discussions > MS SQL Server 7/MS SQL Server 2000 >  OPENQUERY and local variables

## Jeff Haynes

Can anyone suggest how to use local variables in OPENQUERY statements as parameters in the SELECT clause? eg: SELECT * from OPENQUERY(REMOTESERVERNAME,&#39;Select * from table1 where id = @parm1&#39 :Wink:

----------


## Mark Edgar

Try this:

declare @parm int
set @parm = 150

declare @sql varchar(300)
set @sql = &#39;select * from openquery(REMOTESERVERNAME,&#39;&#39;select * from datatbase1..table1 where col1 = &#39; + cast(@parm as varchar(5)) + &#39;&#39;&#39 :Wink: &#39;
select @sql

exec(@sql)

This builds a string (@sql) containing your entire query with the @parm variable already resolved to a specific value and then uses the exec function to execute the SQL.  The confusing part is knowing when to double up on the single quotes embedded in the sql string.  This example also assumes that your local variable is an integer and it converts it to a varchar to allow it to be concatenated into the SQL string.  The example uses the three-part table name to ensure that the remote server knows which database the table resides in.  You may be able to get away with just using the table name, depending on your environment.


------------
Jeff Haynes at 8/1/00 9:33:53 AM

Can anyone suggest how to use local variables in OPENQUERY statements as parameters in the SELECT clause? eg: SELECT * from OPENQUERY(REMOTESERVERNAME,&#39;Select * from table1 where id = @parm1&#39 :Wink:

----------


## Jeff Haynes

Thanks Mark

I did try this but still running into problems:

I constructed the following:

DECLARE

@StartDate	DateTime,
@test		varchar(50)

SET @StartDate = &#39;06/01/2000&#39;
SET @test = CONVERT(Varchar(30),@StartDate)


SELECT * From OPENQUERY(EUHORMARS,&#39;SELECT distinct MI1.CONS_SYS_REF, VOYAGES.SERVICE FROM 
			MI1 INNER JOIN VOYAGES ON MI1.SHIP_CODE = VOYAGES.SHIP_CODE AND MI1.VOYAGE_NUMBER = VOYAGES.VOYAGE_NUMBER 
			WHERE VOYAGES.USER_ACCT_MONTH = (SELECT USER_ACCT_MONTH FROM tblDateLookup WHERE ACCT_MONTH =  &#39;&#39;CONVERT(Varchar(30),@StartDate,113)&#39;  &#39 :Wink:  &#39; )


However I get the error message:

Server: Msg 241, Level 16, State 3, Line 19
Syntax error converting DATETIME from character string.

If I hard code the date (without the local variable) it works!

eg:

SELECT * From OPENQUERY(EUHORMARS,&#39;SELECT distinct MI1.CONS_SYS_REF, VOYAGES.SERVICE FROM 
			MI1 INNER JOIN VOYAGES ON MI1.SHIP_CODE = VOYAGES.SHIP_CODE AND MI1.VOYAGE_NUMBER = VOYAGES.VOYAGE_NUMBER 
			WHERE VOYAGES.USER_ACCT_MONTH = (SELECT USER_ACCT_MONTH FROM tblDateLookup WHERE ACCT_MONTH =&#39;&#39;Jun  1 2000 12:00AM&#39;&#39 :Wink: &#39 :Wink:  





------------
Mark Edgar at 8/1/00 12:20:35 PM

Try this:

declare @parm int
set @parm = 150

declare @sql varchar(300)
set @sql = &#39;select * from openquery(REMOTESERVERNAME,&#39;&#39;select * from datatbase1..table1 where col1 = &#39; + cast(@parm as varchar(5)) + &#39;&#39;&#39 :Wink: &#39;
select @sql

exec(@sql)

This builds a string (@sql) containing your entire query with the @parm variable already resolved to a specific value and then uses the exec function to execute the SQL.  The confusing part is knowing when to double up on the single quotes embedded in the sql string.  This example also assumes that your local variable is an integer and it converts it to a varchar to allow it to be concatenated into the SQL string.  The example uses the three-part table name to ensure that the remote server knows which database the table resides in.  You may be able to get away with just using the table name, depending on your environment.


------------
Jeff Haynes at 8/1/00 9:33:53 AM

Can anyone suggest how to use local variables in OPENQUERY statements as parameters in the SELECT clause? eg: SELECT * from OPENQUERY(REMOTESERVERNAME,&#39;Select * from table1 where id = @parm1&#39 :Wink:

----------


## Mark Edgar

The problem is most likely related to a single quote problem.  You may need to add or remove single quotes to get it to work.  Try displaying the results of the sql string using select (select @sql) instead of executing the string to see the exact code you are building.


------------
Jeff Haynes at 8/2/00 8:55:19 AM

Thanks Mark

I did try this but still running into problems:

I constructed the following:

DECLARE

@StartDate	DateTime,
@test		varchar(50)

SET @StartDate = &#39;06/01/2000&#39;
SET @test = CONVERT(Varchar(30),@StartDate)


SELECT * From OPENQUERY(EUHORMARS,&#39;SELECT distinct MI1.CONS_SYS_REF, VOYAGES.SERVICE FROM 
			MI1 INNER JOIN VOYAGES ON MI1.SHIP_CODE = VOYAGES.SHIP_CODE AND MI1.VOYAGE_NUMBER = VOYAGES.VOYAGE_NUMBER 
			WHERE VOYAGES.USER_ACCT_MONTH = (SELECT USER_ACCT_MONTH FROM tblDateLookup WHERE ACCT_MONTH =  &#39;&#39;CONVERT(Varchar(30),@StartDate,113)&#39;  &#39 :Wink:  &#39; )


However I get the error message:

Server: Msg 241, Level 16, State 3, Line 19
Syntax error converting DATETIME from character string.

If I hard code the date (without the local variable) it works!

eg:

SELECT * From OPENQUERY(EUHORMARS,&#39;SELECT distinct MI1.CONS_SYS_REF, VOYAGES.SERVICE FROM 
			MI1 INNER JOIN VOYAGES ON MI1.SHIP_CODE = VOYAGES.SHIP_CODE AND MI1.VOYAGE_NUMBER = VOYAGES.VOYAGE_NUMBER 
			WHERE VOYAGES.USER_ACCT_MONTH = (SELECT USER_ACCT_MONTH FROM tblDateLookup WHERE ACCT_MONTH =&#39;&#39;Jun  1 2000 12:00AM&#39;&#39 :Wink: &#39 :Wink:  





------------
Mark Edgar at 8/1/00 12:20:35 PM

Try this:

declare @parm int
set @parm = 150

declare @sql varchar(300)
set @sql = &#39;select * from openquery(REMOTESERVERNAME,&#39;&#39;select * from datatbase1..table1 where col1 = &#39; + cast(@parm as varchar(5)) + &#39;&#39;&#39 :Wink: &#39;
select @sql

exec(@sql)

This builds a string (@sql) containing your entire query with the @parm variable already resolved to a specific value and then uses the exec function to execute the SQL.  The confusing part is knowing when to double up on the single quotes embedded in the sql string.  This example also assumes that your local variable is an integer and it converts it to a varchar to allow it to be concatenated into the SQL string.  The example uses the three-part table name to ensure that the remote server knows which database the table resides in.  You may be able to get away with just using the table name, depending on your environment.


------------
Jeff Haynes at 8/1/00 9:33:53 AM

Can anyone suggest how to use local variables in OPENQUERY statements as parameters in the SELECT clause? eg: SELECT * from OPENQUERY(REMOTESERVERNAME,&#39;Select * from table1 where id = @parm1&#39 :Wink:

----------


## Jeff Haynes

Thanks again Mark

You are right in saying that I need to remove or add single quotes. In fact I have added quite a few to get it to work. See this example:

WHERE USER_ACCT_MONTH = &#39; + &#39;&#39;&#39;&#39;&#39;0000&#39;&#39;&#39;&#39;&  #39; + &#39; AND DATE_OF_SAILING  
>= DateAdd(MM,-1,DATEadd(dd,-1 * DATEPart(dd,(getDate()))+1,GETDATE()))&#39;&#39;&#  39;+&#39 :Wink: &#39;


All is now working fine thanks.


------------
Mark Edgar at 8/2/00 12:18:56 PM

The problem is most likely related to a single quote problem.  You may need to add or remove single quotes to get it to work.  Try displaying the results of the sql string using select (select @sql) instead of executing the string to see the exact code you are building.


------------
Jeff Haynes at 8/2/00 8:55:19 AM

Thanks Mark

I did try this but still running into problems:

I constructed the following:

DECLARE

@StartDate	DateTime,
@test		varchar(50)

SET @StartDate = &#39;06/01/2000&#39;
SET @test = CONVERT(Varchar(30),@StartDate)


SELECT * From OPENQUERY(EUHORMARS,&#39;SELECT distinct MI1.CONS_SYS_REF, VOYAGES.SERVICE FROM 
			MI1 INNER JOIN VOYAGES ON MI1.SHIP_CODE = VOYAGES.SHIP_CODE AND MI1.VOYAGE_NUMBER = VOYAGES.VOYAGE_NUMBER 
			WHERE VOYAGES.USER_ACCT_MONTH = (SELECT USER_ACCT_MONTH FROM tblDateLookup WHERE ACCT_MONTH =  &#39;&#39;CONVERT(Varchar(30),@StartDate,113)&#39;  &#39 :Wink:  &#39; )


However I get the error message:

Server: Msg 241, Level 16, State 3, Line 19
Syntax error converting DATETIME from character string.

If I hard code the date (without the local variable) it works!

eg:

SELECT * From OPENQUERY(EUHORMARS,&#39;SELECT distinct MI1.CONS_SYS_REF, VOYAGES.SERVICE FROM 
			MI1 INNER JOIN VOYAGES ON MI1.SHIP_CODE = VOYAGES.SHIP_CODE AND MI1.VOYAGE_NUMBER = VOYAGES.VOYAGE_NUMBER 
			WHERE VOYAGES.USER_ACCT_MONTH = (SELECT USER_ACCT_MONTH FROM tblDateLookup WHERE ACCT_MONTH =&#39;&#39;Jun  1 2000 12:00AM&#39;&#39 :Wink: &#39 :Wink:  





------------
Mark Edgar at 8/1/00 12:20:35 PM

Try this:

declare @parm int
set @parm = 150

declare @sql varchar(300)
set @sql = &#39;select * from openquery(REMOTESERVERNAME,&#39;&#39;select * from datatbase1..table1 where col1 = &#39; + cast(@parm as varchar(5)) + &#39;&#39;&#39 :Wink: &#39;
select @sql

exec(@sql)

This builds a string (@sql) containing your entire query with the @parm variable already resolved to a specific value and then uses the exec function to execute the SQL.  The confusing part is knowing when to double up on the single quotes embedded in the sql string.  This example also assumes that your local variable is an integer and it converts it to a varchar to allow it to be concatenated into the SQL string.  The example uses the three-part table name to ensure that the remote server knows which database the table resides in.  You may be able to get away with just using the table name, depending on your environment.


------------
Jeff Haynes at 8/1/00 9:33:53 AM

Can anyone suggest how to use local variables in OPENQUERY statements as parameters in the SELECT clause? eg: SELECT * from OPENQUERY(REMOTESERVERNAME,&#39;Select * from table1 where id = @parm1&#39 :Wink:

----------

