You are here: Re: Using variable in Stored procedure - help! « MsSQL Server « IT news, forums, messages
Re: Using variable in Stored procedure - help!

Posted by Erland Sommarskog on 11/09/07 11:49

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

 

Navigation:

[Reply to this message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация