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 2008 Forums
 Transact-SQL (2008)
 Error: Must Declare scalar variable "@accountId".
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jmadden7534
Starting Member

USA
3 Posts

Posted - 11/16/2012 :  19:57:43  Show Profile  Reply with Quote
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
6062 Posts

Posted - 11/16/2012 :  21:07:22  Show Profile  Reply with Quote
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
Go to Top of Page

jmadden7534
Starting Member

USA
3 Posts

Posted - 11/16/2012 :  21:43:00  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/16/2012 :  22:35:49  Show Profile  Reply with Quote
you can remove last condition as you've specified same condition twice

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 11/16/2012 :  23:52:55  Show Profile  Reply with Quote
similar but different: cuisine vs course.

BTW - Congrats Visakh!

Be One with the Optimizer
TG
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 11/16/2012 :  23:56:05  Show Profile  Reply with Quote
yw jmadden7534, thanks for the follow up.

Be One with the Optimizer
TG
Go to Top of Page

jmadden7534
Starting Member

USA
3 Posts

Posted - 11/17/2012 :  00:45:18  Show Profile  Reply with Quote
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.
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.1 seconds. Powered By: Snitz Forums 2000