| Author |
 Topic  |
|
|
jmadden7534
Starting Member
USA
3 Posts |
 Posted - 11/16/2012 : 19:57:43
|
Hi everyone ,
I am working with VB.Net 2008 Professional, and SQL Server 2005 Professional.
I am getting an error: Must Declare scalar variable "@accountId".
Here is my code:
ALTER PROCEDURE dbo.SearchForRecipes
(
--Search Recipe allow searcher to choose what fields to search
@recipeName varchar(50) = NULL,
@preparationTimeMin int = NULL,
@preparationTimeMax int = NULL,
@courseTypeID float = NULL,
@cuisineTypeID float = NULL,
@accountId int
--@debug bit = 0
)
AS
DECLARE @sqlQuery nvarchar(4000)
SET @sqlQuery = 'SELECT Recipes.RecipeID, Recipes.CourseTypeID, Recipes.CuisineTypeID, Recipes.Name, Recipes.PreparationTime, Recipes.Directions, Recipes.CookTime,
Recipes.Photo, Recipes.Temperature, Recipes.AccountId, RecipeIngredient.RecipeIngredientID, RecipeIngredient.RecipeID AS RIRecipeId,
RecipeIngredient.IngredientID, RecipeIngredient.UnitMeasurementID, RecipeIngredient.IngredientAmount, NutritionDetails.NutritionID,
NutritionDetails.RecipeID AS NDRecipeId, NutritionDetails.Calories, NutritionDetails.CaloriesFromFat, NutritionDetails.TotalFat, NutritionDetails.SaturatedFat,
NutritionDetails.TransFat, NutritionDetails.Sodium, NutritionDetails.Cholesteral, NutritionDetails.TotalCarbohydrates, NutritionDetails.DietaryFiber,
NutritionDetails.Sugar, NutritionDetails.Protein, NutritionDetails.VitaminA, NutritionDetails.VitaminC, NutritionDetails.Calcium,
NutritionDetails.Iron
FROM Recipes
INNER JOIN RecipeIngredient ON Recipes.RecipeID = RecipeIngredient.RecipeID
INNER JOIN NutritionDetails ON Recipes.RecipeID = NutritionDetails.RecipeID
WHERE (Recipes.AccountId = @accountId) '
IF @recipeName IS NOT NULL
SET @sqlQuery = @sqlQuery + ' AND (Recipes.Name = @recipeName)'
If @preparationTimeMin IS NOT NULL
SET @sqlQuery = @sqlQuery + ' AND (Recipes.PreparationTime >= @preparationTimeMin)'
If @preparationTimeMax IS NOT NULL
SET @sqlQuery = @sqlQuery + ' AND (Recipes.PreparationTime <= @preparationTimeMax)'
IF @courseTypeID IS NOT NULL
SET @sqlQuery = @sqlQuery + ' AND (Recipes.CourseTypeID = @courseTypeID)'
IF @cuisineTypeID IS NOT NULL
SET @sqlQuery = @sqlQuery + ' AND (Recipes.CuisineTypeID = @cuisineTypeID)'
--IF @debug = 1 PRINT @sqlQuery
EXECUTE sp_executesql @sqlQuery,
N'@recipeName Varchar(50)',
N'@preparationTimeMin int',
N'@preparationTimeMax int',
N'@courseTypeID Float',
N'@cuisineTypeID Float',
N'@accountId int',
@recipeName,
@preparationTimeMin,
@preparationTimeMax,
@courseTypeID,
@cuisineTypeID,
@accountId
Anyone have any ideas? Please let me know if this also is bad for SQL injection attacks. |
|
|
TG
Flowing Fount of Yak Knowledge
USA
5501 Posts |
Posted - 11/16/2012 : 21:07:22
|
Do away with the dynamic sql. Just change your where clause to this:
WHERE Recipes.AccountId = @accountId
AND Recipes.Name = coalesce(@recipeName, Recipes.Name)
and Recipes.PreparationTime >= coalesce(@preparationTimeMin, Recipes.PreparationTime)
and Recipes.PreparationTime <= coalesce(@preparationTimeMax, Recipes.PreparationTime)
and Recipes.CourseTypeID = coalesce(@courseTypeID, Recipes.CourseTypeID)
and Recipes.CuisineTypeID = coalesce(@cuisineTypeID, Recipes.CuisineTypeID)
If any of these column in your where clause allow NULLs then use this:
and coalesce(Recipes.CuisineTypeID, -1) = coalesce(@cuisineTypeID, Recipes.CuisineTypeID, -1))
But I think your error is because the sp_executeSql @params parameter should all be one value. Like:
EXECUTE sp_executesql @sqlQuery,
N'@recipeName Varchar(50), @preparationTimeMin int, @preparationTimeMax int...',
@recipeName,
@preparationTimeMin,
@preparationTimeMax,
EDIT:
regarding the nullable columns and coalesce - just make sure that your final argument matches the datatype of the column.
Be One with the Optimizer
TG |
Edited by - TG on 11/16/2012 21:08:27 |
 |
|
|
jmadden7534
Starting Member
USA
3 Posts |
Posted - 11/16/2012 : 21:43:00
|
Ty very much TG. I switched from dynamic sql to normal. I wanted to stay away from dynamic sql but I didn't know another way. Here is what I changed to make it work:
ALTER PROCEDURE dbo.SearchForRecipes
(
--Search Recipe allow searcher to choose what fields to search
@recipeName varchar(50) = NULL,
@preparationTimeMin int = NULL,
@preparationTimeMax int = NULL,
@courseTypeID float = NULL,
@cuisineTypeID float = NULL,
@accountId int
)
AS
BEGIN
SELECT Recipes.RecipeID, Recipes.CourseTypeID, Recipes.CuisineTypeID, Recipes.Name, Recipes.PreparationTime, Recipes.Directions, Recipes.CookTime,
Recipes.Photo, Recipes.Temperature, Recipes.AccountId, RecipeIngredient.RecipeIngredientID, RecipeIngredient.RecipeID AS RIRecipeId,
RecipeIngredient.IngredientID, RecipeIngredient.UnitMeasurementID, RecipeIngredient.IngredientAmount, NutritionDetails.NutritionID,
NutritionDetails.RecipeID AS NDRecipeId, NutritionDetails.Calories, NutritionDetails.CaloriesFromFat, NutritionDetails.TotalFat, NutritionDetails.SaturatedFat,
NutritionDetails.TransFat, NutritionDetails.Sodium, NutritionDetails.Cholesteral, NutritionDetails.TotalCarbohydrates, NutritionDetails.DietaryFiber,
NutritionDetails.Sugar, NutritionDetails.Protein, NutritionDetails.VitaminA, NutritionDetails.VitaminC, NutritionDetails.Calcium,
NutritionDetails.Iron
FROM Recipes
INNER JOIN RecipeIngredient ON Recipes.RecipeID = RecipeIngredient.RecipeID
INNER JOIN NutritionDetails ON Recipes.RecipeID = NutritionDetails.RecipeID
WHERE (Recipes.AccountId = @accountId)
AND Recipes.Name = coalesce(@recipeName, Recipes.Name)
and Recipes.PreparationTime >= coalesce(@preparationTimeMin, Recipes.PreparationTime)
and Recipes.PreparationTime <= coalesce(@preparationTimeMax, Recipes.PreparationTime)
and coalesce(Recipes.CuisineTypeID, -1) = coalesce(@cuisineTypeID, Recipes.CuisineTypeID, -1)
and coalesce(Recipes.CourseTypeID, -1) = coalesce(@courseTypeID, Recipes.CourseTypeID, -1)
END |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 11/16/2012 : 22:35:49
|
you can remove last condition as you've specified same condition twice
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5501 Posts |
Posted - 11/16/2012 : 23:52:55
|
similar but different: cuisine vs course.
BTW - Congrats Visakh!
Be One with the Optimizer
TG |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5501 Posts |
Posted - 11/16/2012 : 23:56:05
|
yw jmadden7534, thanks for the follow up.
Be One with the Optimizer
TG |
|
|
|
jmadden7534
Starting Member
USA
3 Posts |
Posted - 11/17/2012 : 00:45:18
|
Cuisine is a characteristic style of cooking practices and traditions,often associated with a specific culture. Like South America
Food Course is like appetizers
visakh16 What do you mean by "you can remove last condition as you've specified same condition twice"
Sorry I am not following. I am still trying to learn SQL. |
|
|
| |
Topic |
|
|
|
Error: Must Declare scalar variable "@accountId".
