PROBLEM IN STORED PROCEDURE

Question

0.00/5 (No votes)

See more:

MY PROCEDURE IS THIS

SQL

ALTER proc sp_insert@test1
(
@Value2 varchar(50),
@Value3 varchar(50),
@Value4 varchar(50),
@Value5 varchar(50),
@Value6 varchar(50),
@Value7 datetime
)
as
begin
insert into dbo.test1 (Value2,Value3,Value4,Value5,Value6,Value7)
values (@ddl_Value2,@txt_Value3,@txt_Value4,@txt_Value5,@txt_Value6,@DateTimePicker_Value7)
end

AND ERROR IS
Msg 137, Level 15, State 2, Procedure sp_insert@test1, Line 14
Must declare the scalar variable "@ddl_Value2".

Posted 14-Jan-15 19:36pm

hari om singh

Updated 14-Jan-15 19:46pm

_Asif_

v2

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Asif_ · Accepted Answer · 2015-01-14T19:46:00

And the answer is

ALTER proc sp_insert@test1
(
@Value2 varchar(50),
@Value3 varchar(50),
@Value4 varchar(50),
@Value5 varchar(50),
@Value6 varchar(50),
@Value7 datetime
)
as
begin
insert into dbo.test1 (Value2,Value3,Value4,Value5,Value6,Value7)
values (@Value2,@Value3,@Value4,@Value5,@Value6,@Value7)
end

Wendelius · Accepted Answer · 2015-01-14T19:51:00

Solution 2

To clarify the situation, the variable names declared as parameters must match the ones you use later in the T-SQL code.

For example you define a parameter named @Value2 but you try to use a variable name @ddl_Value2. Such variable doesn't exist in the scope thus the error message.

Also note that it's a good practice to give the parameters meaningful names. This helps to interpret the code later and also helps the calling side to understand what values must be given to each parameter.

Posted 14-Jan-15 19:51pm

Wendelius

Comments

hari om singh 15-Jan-15 1:55am

value2 is database column name
and ddl_value2 is input parameter

Wendelius 15-Jan-15 1:57am

ddl_value2 is not an input parameter for the procedure. Have a look at your declaration:

ALTER proc sp_insert@test1
(
@Value2 varchar(50), ...

The names of the variables must match throughout the scope.

hari om singh 15-Jan-15 2:04am

thanks

Wendelius 15-Jan-15 9:28am

You're welcome :)

PROBLEM IN STORED PROCEDURE

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

PROBLEM IN STORED PROCEDURE

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us