Using variable in Stored procedure - help!

Hello

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]
@strfinalint as varchar(1000),
@startdate as datetime
@enddate as datetime

as

declare @insertcmd as varchar(2000)
declare @startdate as datetime
declare @enddate as datetime

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)
GO

It was working with the command

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

But I am struggling to include the WHERE part of it, I seem to have
problems making the variables work in this with appropriate quotation
marks !!

Thanks
Sunny

Re: Using variable in Stored procedure - help!

(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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Using variable in Stored procedure - help!

Erland,

Thanks, this gets me closer to what I want to achieve, but because of
the @insertcmd being a varchar or nvarchar, I am having problems
filtering the dates.

CREATE PROCEDURE [dbo].[USP_Test]
@strfinalint as varchar(1000),
@startdate as datetime,
@enddate as datetime
as

declare @insertcmd as nvarchar(2000)


set @insertcmd = 'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.
4.0'',''Excel 8.0;Database=D:/MyDrive/' + @strfinalint + ';'',
''SELECT * FROM [Sheet1$]'') Select * from VwStaff WHERE S_Date
Between '''+ @startdate + ''' AND '''+ @enddate + ''' '

EXEC (@insertcmd)
GO

Am I doing something wrong? I tried using the @PARAMS but it does not
seem to work, so I tried the above, and now it tells me

Syntax error converting datetime from character string.

Sorry, but I am not too good at this.

Thanks
Sunil

Re: Using variable in Stored procedure - help!

(sunilkes@gmail.com) writes:
> Thanks, this gets me closer to what I want to achieve, but because of
> the @insertcmd being a varchar or nvarchar, I am having problems
> filtering the dates.
>
> CREATE PROCEDURE [dbo].[USP_Test]
> @strfinalint as varchar(1000),
> @startdate as datetime,
> @enddate as datetime
> as
>
> declare @insertcmd as nvarchar(2000)
>
>
> set @insertcmd = 'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.
> 4.0'',''Excel 8.0;Database=D:/MyDrive/' + @strfinalint + ';'',
> ''SELECT * FROM [Sheet1$]'') Select * from VwStaff WHERE S_Date
> Between '''+ @startdate + ''' AND '''+ @enddate + ''' '
>
> EXEC (@insertcmd)
> GO
>
> Am I doing something wrong? I tried using the @PARAMS but it does not
> seem to work,

What you do mean with "does not seem to work"? Did you get any error
message? Did you get unexpected results? Something else? I can't assist,
if I don't know what you are doing.

> so I tried the above, and now it tells me
>
> Syntax error converting datetime from character string.

That's because SQL Server has strict rules for data-type precedence, which
says that when two different data types meet, the one with lower
precedence gets converted to the higher. And varchar has lower priority
than datetime, so it's trying to convert the SQL string to datetime.
Which of course does work out.

You need to use convert to explicitly convert the dates to character, but
using parameterised commands is better in my opinion.


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

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