How to assign a query result to a stored procedure
-
Hi, I my stored procedure I have an insert query. For this I have to get one value from a sub query. But it is not accepting that query. So I tried to declare a separate variable and assign the subquery value to this and insert it in the table. Create Procedure [dbo].[AddOrRemoveFavourites] ( @UserId bigint, @VideoName varchar(50), ) as Declare @VideoId int Set @VideoId = Select VideoId from Videos where Title='Monkeydog' Insert into Favourites(UserId,VideoId) values(@UserId,@VideoId) but in this I am getting error like "Incorrect syntax near the keyword 'Select'." If anyone have any idea to solve this please reply me. Thanks in advance.
-
Hi, I my stored procedure I have an insert query. For this I have to get one value from a sub query. But it is not accepting that query. So I tried to declare a separate variable and assign the subquery value to this and insert it in the table. Create Procedure [dbo].[AddOrRemoveFavourites] ( @UserId bigint, @VideoName varchar(50), ) as Declare @VideoId int Set @VideoId = Select VideoId from Videos where Title='Monkeydog' Insert into Favourites(UserId,VideoId) values(@UserId,@VideoId) but in this I am getting error like "Incorrect syntax near the keyword 'Select'." If anyone have any idea to solve this please reply me. Thanks in advance.
-
Hi, I my stored procedure I have an insert query. For this I have to get one value from a sub query. But it is not accepting that query. So I tried to declare a separate variable and assign the subquery value to this and insert it in the table. Create Procedure [dbo].[AddOrRemoveFavourites] ( @UserId bigint, @VideoName varchar(50), ) as Declare @VideoId int Set @VideoId = Select VideoId from Videos where Title='Monkeydog' Insert into Favourites(UserId,VideoId) values(@UserId,@VideoId) but in this I am getting error like "Incorrect syntax near the keyword 'Select'." If anyone have any idea to solve this please reply me. Thanks in advance.
Hi, You need to enclose your select in parenthesis and include a top(1) clause, like this:
Set @VideoId = ( Select top(1) VideoId from Videos where Title='Monkeydog' )
Regards,
Syed Mehroz Alam My Blog | My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein -
Hi, I my stored procedure I have an insert query. For this I have to get one value from a sub query. But it is not accepting that query. So I tried to declare a separate variable and assign the subquery value to this and insert it in the table. Create Procedure [dbo].[AddOrRemoveFavourites] ( @UserId bigint, @VideoName varchar(50), ) as Declare @VideoId int Set @VideoId = Select VideoId from Videos where Title='Monkeydog' Insert into Favourites(UserId,VideoId) values(@UserId,@VideoId) but in this I am getting error like "Incorrect syntax near the keyword 'Select'." If anyone have any idea to solve this please reply me. Thanks in advance.
Hi,
Set @VideoId = Select VideoId from Videos where Title='Monkeydog'is wrong useSelect @VideoId = VideoId from Videos where Title='Monkeydog'
The reason is if we want to assign values to more than one variable at a time then go with Select. SET allows you to assign data to only one variable at a time. For more information visit here http://vyaskn.tripod.com/differences_between_set_and_select.htm[^] Also searching in google will reveal more info :)
Niladri Biswas