Error: Must Declare scalar variable "@accountId".

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Error: Must Declare scalar variable "@accountId".

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
)

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
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 = @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

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
)

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

52326 Posts

Posted - 2012-11-16 : 22:35:49

you can remove last condition as you've specified same condition twice

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 Optimizer
TG

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 Optimizer
TG

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 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources