Must declare a scalar variable in sql procedure

Question

Please what's wrong with the procedure instruction below.

DECLARE @result int -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here set @result = (select COUNT(*) from populate) if (@result > 1) Begin insert into populate (brch, terminal_id) values(@branch, @atmid) end 

---

  SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE insertion @id varchar(50), @brch varchar(50) -- Add the parameters for the stored procedure here AS BEGIN DECLARE @result int -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here set @result = (COUNT(*) from populate) if (@result > 1) Begin insert into populate (brch, terminal_id) values(@id, @brch) end END GO 

Answer 1

It seems like you are confusing things by first sending a piece of code that gives the error Msg 137, Level 15, State 2, Line 11 Must declare the scalar variable "@branch". and then later adds the complete procedure, which gives the error Msg 156, Level 15, State 1, Procedure insertion, Line 13 Incorrect syntax near the keyword 'from'.

Please make sure that you publish the real code that you are using and the full error message, otherwise people cannot help you.

In any case, I ignored the code snippet and looked only at the procedure, and as ABFORCE said, the problem is where you fill in @result , because your syntax is incorrect. This procedure code parses without errors in SQL Server 2008:

  CREATE PROCEDURE insertion @id varchar(50), @brch varchar(50) -- Add the parameters for the stored procedure here AS BEGIN DECLARE @result int -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here select @result = COUNT(*) from populate if (@result > 1) Begin insert into populate (brch, terminal_id) values(@id, @brch) end END GO 

You might want to look at the documentation of variable assignment and SET .

Answer 2

The problem, as the error message says, is that @branch and @atmid not mentioned anywhere before they are used.

You declared @result and set its value, so you need to do the same for @branch and @atmid , the system cannot clear the values ​​for you.

Answer 3

try it

 select @result =COUNT(*) from populate