SQL Server Forums
Profile | Register | 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
 New Topic  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
5071 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
5071 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  
 New 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.08 seconds. Powered By: Snitz Forums 2000