I am sending query in a string from linq to sql
string _ObjCocktail = "declare @max_id int Insert into dbo.Cocktails(name,cocktail_category_id,instrustions) values ('Mojto',1,'Instruction of the cocktail') select @max_id = MAX(dbo.Cocktails.id) from dbo.Cocktails Insert into RecipeDetails(cocktail_id,mixers_id,liquor_id,quantity,unit_id,other_ingredients_id) values ( @max_id, null,2,null,1,null) Insert into RecipeDetails(cocktail_id,mixers_id,liquor_id,quantity,unit_id,other_ingredients_id) values ( @max_id, null,7,null,1,null)";
public void InsertCocktail()
{
try
{
_DataContext.InsertCocktails(_ObjCocktail);
}
catch (Exception ex)
{
}
and
i am handling that string in sql like this.
ALTER procedure [dbo].[InsertCocktails]
(
@query varchar(max)
)
as
begin
exec @query
end
It is going to Exception with this error.
The name 'declare @max_id int Insert into dbo.Cocktails(name,cocktail_category_id,instrustions) values ('Mojto',1,'Instruction of the cocktail') select @max_id = MAX(dbo.Cocktails.id) from dbo.Cocktails Insert into RecipeDetails(cocktail_id,mixers_id,liquor_id,quantity,unit_id,other_ingredients_id) values ( @max_id, null,2,null,1,null) Insert into RecipeDetails(cocktail_id,mixers_id,liquor_id,quantity,unit_id,other_ingredients_id) values ( @max_id, null,7,null,1,null)' is not a valid identifier.
When i copy paste this query string in sql it is running fine.