Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
parrot
Posting Yak Master
132 Posts |
Posted - 2010-10-28 : 16:32:33
|
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 BurialWHERE 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
5072 Posts |
Posted - 2010-10-28 : 16:39:13
|
use two parameters for your hi and low valuesAND DeathYear BETWEEN @LowYear AND @HighYear |
|
|
parrot
Posting Yak Master
132 Posts |
Posted - 2010-10-28 : 18:52:18
|
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. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-10-28 : 23:05:15
|
Welcome. Glad to help |
|
|
kailiyu
Starting Member
1 Post |
Posted - 2010-11-01 : 03:44:46
|
unspammed |
|
|
|
|
|
|
|