Using Local Variables for comparing data in the Excel being read in SQL Server

Question

I am trying to read data from an Excel using the Following statement which is working absolutely fine

Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=d:\input.xls','select MyColum from [Sheet1$]')

the XLS inpout.xls has two columns ID and NAME. Now I want the above statement to return only ONE value/row where ID = x and assign the returned value to a local variable as shown below -

DECLARE @dbValue varchar(20)
DECLARE @I int

SET @I = 1

Select @dbValue = (Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=d:\input.xls','select MyColum from [Sheet1$] where [ID]=@I'))

this returns an error

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "No value given for one or more required parameters.".
Msg 7320, Level 16, State 2, Line 3
Cannot execute the query "select MyColum from [Sheet1$] where [ID]=@I" against OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Please suggest.

Something like `... where [ID]='+Convert(varchar,@I)` would be the Dynamic SQL way, but someone will come along and warn about SQL injection vulns :) However, you may need to create the query string in advance : http://stackoverflow.com/questions/13831472/using-a-variable-in-openrowset-query — AjV Jsy, May 11 '14 at 17:29

score 0 · Accepted Answer · answered May 12 '14 at 04:01

Maybe something like this will work. But your question is confusing, since your query on the Excel file selects MyColumn, but you say that file has columns ID and NAME.

DECLARE @dbValue varchar(20)
DECLARE @I int

SET @I = 1

Select @dbValue = NAME FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=d:\input.xls','select ID from [Sheet1$]') as T
WHERE ID=@I;

Using Local Variables for comparing data in the Excel being read in SQL Server

1 Answers1