p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Server 2005 (http://p2p.wrox.com/forumdisplay.php?f=220)
-   -   how to pass value to stored procedure? (http://p2p.wrox.com/showthread.php?t=54448)

hertendreef February 22nd, 2007 06:15 PM

how to pass value to stored procedure?
 
Hi,

i need to insert a record 1 or more times, depending of a variable in code-behind:
dim amount as integer
amount= value (e.g. 3)

My problem is: how to pass that variable to the stored procedure?
I tried with this but nothing happens:

comd.Parameters.Add("@amount", SqlDbType.NVarChar, 10).Value = amount_of_details

Maybe is my stored procedure wrong?
Thanks
H.

Here is it:
----------

ALTER PROCEDURE dbo.insert_table
 (
@field1 nvarchar(10)
,...
)
AS
Declare @iLoopNumber int
Declare @amount int
BEGIN TRAN

SET @iLoopNumber = 1

SET @amountr


While (@iLoopNumber <= @amount)

BEGIN

INSERT INTO table(field1,...)

VALUES (....))

 SET @iLoopNumber = @iLoopNumber +1

End


COMMIT TRAN




GeertVerhoeven February 23rd, 2007 03:21 AM

Hi,

Did you set the CommandType property of your SqlCommand to StoredProcedure?

Greetz,

Geert

http://geertverhoeven.blogspot.com

hertendreef February 23rd, 2007 06:04 AM

Hi, thanks for replying..
yes, i did.

Actually, my question is: can i assign the value of variable 'amount' to a Declare @var with the command "comd.Parameters.Add("@amount", SqlDbType.NVarChar, 10).Value = amount" or this is only for assigning a parameter within the 'Values' of the 'Insert' statement in the stored procedure?

If not, how to import 'amount' into the SP?

I guess i have also to make a loop in the code-behind for the parameters like this:

amount = 3 'example
comd.Parameters.Add("@amount", SqlDbType.Int).Value = amount
For i = 1 to amount
comd.Parameters.Add("@field1", SqlDbType.Int).Value = var1
Next



hertendreef February 23rd, 2007 11:43 AM

I found it.I must put @amount with the other paramaters, not with Declare ...



All times are GMT -4. The time now is 05:12 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.