Hi - 

I want to write the stored procedure that insert the data into the sql server table. There is a vb.net code that returns the parameter value for stored procedure and I want to pass that parameter into insert statement to insert the row into the table. 

e.g, 

Create proc InsertDemo

(@p_p1 int, @p_p2 int, @p_p3 int, @p_p4 varchar(20),@p_p5 datetime)

begin

insert into xtable

(F1,F2,F3,F4,F5)

values ()

end

so, here I am going to call this proc from within the vb.net code so. parameter values will come from vb.net code and I want to add those parameter values into the 'xtable'

how can i do that? 

one way I can think of like 

create proc InsertDemo

(@p_p1 int, @p_p2 int, @p_p3 int, @p_p4 varchar(20),@p_p5 datetime)

begin

declare @v_p1 int

declare @v_p2 int

declare @v_p3 int 

declare @v_p4 varchar(20)

declare @v_p5 datetime

set @v_p1 = @p_p1

set @v_p2 = @p_p2

set @v_p3 = @p_p3

....

set @v_p5 =@p_p5 

Insert into xTable

(F1,F2,F3,F4,F5)

values (@v_p1,@v_p2....@v_p5)

but I don't think so that I can assing parameter values to the variable. 

So, how can i accomplish this in this scenario? 

Thanks.

CREATE PROCEDURE myInserter (@p1 INT, @p2 INT, @p3 INT, @p4 DATETIME, @p5 VARCHAR(20)
AS
BEGIN

INSERT INTO xTable (F1, F2, F3, F4, F5)
VALUES (@p1, @p2, @p3, @p4, @p5)

END

The values passed to the proc are referenced by the names you gave them.

Hi- 

Thanks for you reply. 

I tried that but it didn't work. 

When I tried to do that I was getting an error saying 'must declare the scalar variable "@p1"'.

That's why I tried with declaring variable and assign them parameter values and then pass the variables values into insert statement.  

THanks,

Yeah, it does.

CREATE PROCEDURE myInserter (@p1 INT, @p2 INT, @p3 INT, @p4 DATETIME, @p5 VARCHAR(20))
AS
BEGIN

INSERT INTO xTable (F1, F2, F3, F4, F5)
VALUES (@p1, @p2, @p3, @p4, @p5)

END

GO

myInserter 1,2,3,'2014-01-01','see, it works!'

GO

SELECT *
  FROM xTable

In your vb.net code you need to use the exact parameter name with stored procedure, that means when you pass the value into the stored procedure you have to use @p1, @p2, @p3, @p4 and @p5 in your code. 

Thanks.

can you copy paste your vb code and table structure here.

create proc InsertDemo
(
@p_p1 int, 
@p_p2 int, 
@p_p3 int, 
@p_p4 varchar(20),
@p_p5 datetime
)
as
begin
Insert into xTable
(F1,F2,F3,F4,F5)
values (@p_p1, @p_p2, @p_p3, @p_p4, @p_p5)
end  
go

can you copy paste your vb code and table structure here.

I am sure, you doing something wrong in you .NET code. 

You may refer the below link and see for example:

http://www.aspsnippets.com/Articles/Calling-Insert-SQL-Server-Stored-Procedures-using-ADO.Net.aspx