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 |
jmadden7534
Starting Member
3 Posts |
Posted - 2012-11-16 : 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) ASDECLARE @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.IronFROM Recipes INNER JOIN RecipeIngredient ON Recipes.RecipeID = RecipeIngredient.RecipeID INNER JOIN NutritionDetails ON Recipes.RecipeID = NutritionDetails.RecipeIDWHERE (Recipes.AccountId = @accountId) 'IF @recipeName IS NOT NULL SET @sqlQuery = @sqlQuery + ' AND (Recipes.Name = @recipeName)'If @preparationTimeMin IS NOT NULLSET @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 @sqlQueryEXECUTE 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
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-11-16 : 21:07:22
|
Do away with the dynamic sql. Just change your where clause to this:WHERE Recipes.AccountId = @accountIdAND 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 OptimizerTG |
|
|
jmadden7534
Starting Member
3 Posts |
Posted - 2012-11-16 : 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) ASBEGINSELECT 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.IronFROM Recipes INNER JOIN RecipeIngredient ON Recipes.RecipeID = RecipeIngredient.RecipeID INNER JOIN NutritionDetails ON Recipes.RecipeID = NutritionDetails.RecipeIDWHERE (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
52326 Posts |
Posted - 2012-11-16 : 22:35:49
|
you can remove last condition as you've specified same condition twice------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-11-16 : 23:52:55
|
similar but different: cuisine vs course.BTW - Congrats Visakh!Be One with the OptimizerTG |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-11-16 : 23:56:05
|
yw jmadden7534, thanks for the follow up.Be One with the OptimizerTG |
|
|
jmadden7534
Starting Member
3 Posts |
Posted - 2012-11-17 : 00:45:18
|
Cuisine is a characteristic style of cooking practices and traditions,often associated with a specific culture. Like South AmericaFood Course is like appetizersvisakh16 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. |
|
|
|
|
|
|
|