Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 BETWEEN command not working
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

parrot
Posting Yak Master

USA
132 Posts

Posted - 10/28/2010 :  16:32:33  Show Profile  Reply with Quote
When I try to execute an SQL command using sp_executesql I get an error message "Incorrect syntax near the keyword ' AND'". It is referring to the use of the BETWEEN command which I cannot get to work using DECLARE variables. All other variables work for me. Below is the sql string used for the sql command. @SearchNameparm is the variable that gives the error. If anyone can see why this BETWEEN command is not working, please reply. I cannot get it to work in SQL Management Studio either. The code is below:

strSQL "DECLARE @SearchNameparm varchar(20);DECLARE @Cemeterycode varchar(6);DECLARE @type varchar(1);
DECLARE @SQLString nvarchar(500); DECLARE @ParmDefinition nvarchar(500);
SET @SQLString = N'SELECT Block, Lot, Grave, LastName, FirstName,
MaidenName, Spouse, DeathYear, DeathDate, Age FROM Burial
WHERE CemeteryCode = @Cemeterycode AND (DeathYear > @type OR DeathDate > @type)AND DeathYear BETWEEN @SearchNameparm
ORDER BY DeathYear, Lastname, FirstName';
SET @ParmDefinition = N'@SearchNameparm nvarchar(20),
@Cemeterycode nvarchar(6), @type nvarchar(1)';SET @SearchNameparm = '1970' AND '1990';SET @Cemeterycode = '000001';SET @type = '0';
EXECUTE sp_executesql @SQLString, @ParmDefinition, @SearchNameparm, @Cemeterycode, @type

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 10/28/2010 :  16:39:13  Show Profile  Visit russell's Homepage  Reply with Quote
use two parameters for your hi and low values

AND DeathYear BETWEEN @LowYear AND @HighYear
Go to Top of Page

parrot
Posting Yak Master

USA
132 Posts

Posted - 10/28/2010 :  18:52:18  Show Profile  Reply with Quote
Russell;
Thanks for your quick reply. Your suggestion worked. I don't think I ever would have found that answer in SQL Books Online. I appreciate your support.
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 10/28/2010 :  23:05:15  Show Profile  Visit russell's Homepage  Reply with Quote
Welcome. Glad to help
Go to Top of Page

kailiyu
Starting Member

1 Posts

Posted - 11/01/2010 :  03:44:46  Show Profile  Reply with Quote
unspammed
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000