Procedure with variables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Procedure with variables

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-11-29 : 06:35:32

Hi i've declared a variable within my stored procedure which i want to use on a asp page. I'de just like to know if i need to set the @Sup value to ??

Declare @sup char(10)

I then have a select statement and i want to use the declared variable within a where statement

Select product.[sup-code],price.[p-cost], price.[p-sell]
From product,price
where price.[p-code]= product.[p-code]
AND product.[sup-code] = @Sup

Do i need to set my @Sup and if so how do i do it ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-29 : 06:40:15

You need to create @Sup as parameter of the stored procedure.

Create Proc foo
(
@sup char(10)
)
as
....
Select product.[sup-code],price.[p-cost], price.[p-sell] 
From product,price
where price.[p-code]= product.[p-code]
AND product.[sup-code] = @Sup 
GO

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-11-29 : 07:03:25

ok, i've done that now on my stored procedure and tested it and it works. Can i ask your opinion on the call to the stored procedure in the asp code

cmd.CommandType = adCmdStoredProc
cmd.CommandText = "Supplier"

cmd.Parameters.Append cmd.CreateParameter("Supplier",adVarChar,adParamInput,10,sChangeSupplier)

Is this correct

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources