Posted by Erland Sommarskog on 11/07/07 22:53

(sunilkes@gmail.com) writes:
> I am a newbie to this, so I would appreciate any help, I am struggling
> to get this to work
>
> CREATE PROCEDURE [dbo].[sp_test]

Don't call your procedures sp_something. The sp_ prefix is reserved
for system procedures, and SQL Server will first look for these in
the master database.

> set @insertcmd = 'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.
> 4.0'',''Excel 8.0;Database=d:\MyFolder\' + @strfinalint + ';'',
> ''SELECT * FROM [Sheet1$]'') Select * from tbltest WHERE S_Date
> Between' + @startdate + 'AND' + @enddate
>
> EXEC (@insertcmd)

When working with dynamic SQL, it's always a good idea to add:

IF @debug PRINT @sql

so that you can see what you have generated. I bet you will see the
error very quickly in this case!

However, you would have less problems if you used sp_executesql
instead, since in this case you could pass @startdate and @enddate
as parameters:

set @insertcmd = 'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.
4.0'',''Excel 8.0;Database=d:\MyFolder\' + @strfinalint + ';'',
''SELECT * FROM [Sheet1$]'') Select * from tbltest WHERE S_Date
Between @startdate AND @enddate'
SET @params = '@startdate datetime, @enddate datetime'
EXEC sp_executesql @insertcmd, @params, @startdate, @enddate

Note that you must declare @insertcmd and @params as nvarchar for this
to work.

Note also that you cannot pass @strfinalint as a parameter, but that
variable you need to interpolate into the string.


For more details on sp_executesql and dynamic SQL in general, you may
be interested in an article on my web site:
http://www.sommarskog.se/dynamic_sql.html.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]