January 22, 2002 at 12:48 pm
Is it possible to write a stored procedure that accepts a parameter such as a Datetime and then passes that parm to an OpenQuery statement that is accessing data via a Linked Server?
January 22, 2002 at 2:15 pm
The only way I've found is to build a dynamic string and pass it through EXEC or sp_executesql (and this gets nasty on the single quotes for string parameters). For instance:
CREATE PROC usp_TestOpenQuery
@CustID nchar(5)
AS
DECLARE @SQL nvarchar(200)
SET @SQL = 'SELECT * FROM OPENQUERY(DEVPORT1,
''SELECT * FROM Northwind.dbo.Orders WHERE CustomerID = '''''
+ @CustID + ''''''')'
EXEC sp_executesql @SQL
GO
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 22, 2002 at 4:10 pm
You could just build the sql and then do a replace for ' with ''. I think Brian has the best idea.
Steve Jones
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply