I am trying to Execute a stored procedure that requires to variables be passing into to it. One is a static, the other is a dynamic variable.
DECLARE @Filt DATETIME
SET @Filt = (SELECT DISTINCT MAX(Date) FROM Data.db.Staging)
SELECT * INTO #tempData FROM OPENROWSET('SQLNCLI', 'Server=ISR14 \MSSQL2012;Trusted_Connection=yes;', 'EXEC GetData.db.Staging @Mode = ''Date'' @Filt ')
but that doesn't work, got the error back "Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '@Filt'."
I'm guessing it is because Filt is dynamic statement. So I tried this
DECLARE @FilterData DATETIME
DECLARE @sql VARCHAR(200)
SET @Filt = (SELECT DISTINCT MAX(AsOfDate) FROM Data.db.Staging)
SET @sql = 'EXEC GetData.db.Staging @Mode = ''Date'' @Filt = ' + @Filt
SELECT * INTO #tempData FROM OPENROWSET('SQLNCLI', 'Server=ISR14\MSSQL2012;Trusted_Connection=yes;',
@sql)
But I get the message back
"Msg 102, Level 15, State 1, Line 24 Incorrect syntax near '@sql'."
It seems that OPENROWSET can only accept strings. But I want to pass a variable that is dynamic.
3条答案
按热度按时间ogsagwnx1#
You have to put the whole statement into a variable and run it, and convert @FilterData to a varchar to concatenate it.
You can't use variables with openquery/openrowset.
Try this and check the print output... if it works and looks ok, then EXEC(@sql2)
hsvhsicv2#
You need to make the whole query dynamic, not sure if I got it nailed down, but something like:
zz2j4svz3#
I got error Incorrect syntax near '' when executing @sql. In my case there was invalid character at the beginning of NVARCHAR. This helped me: