Question of passing parameters

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 2000 Forums

Transact-SQL (2000)

Question of passing parameters

Author

Topic

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-02-02 : 14:14:02

I've created a stored procedure that takes a parameter, example below:

[CODE]

create procedure
@param1 int
as
select @param1
from db1

[/CODE]

This is what I don't know how to do:

I want to get information for @param1 to pass to the stored procedure from a select statement, example:

@param1 = select anumber from db1

Can someone explain to me now I do this, and have it execute?

thank you

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-02 : 14:23:33

select @param1 = anumber from db1

Tara Kizer
aka tduggan

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-02-02 : 14:39:58

I get an error when I try that!


create procedure 
 @param1 int
as
 select @param1 = anumber from db1, name
 from db1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-02 : 14:47:47

Why have you included ", name" in the code? Please post the error.

Also, you need to pass @param1 into the stored procedure, not get the value while inside it.

Tara Kizer
aka tduggan

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-02-02 : 14:50:09

Can you please tell me how I pass the parameter into the stored procedure? I believe that is what I don't understand-- exactly how to pass a parameter to a stored procedure.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-02 : 15:01:58

Via Query Analyzer, it would be:

EXEC StoredProcedureName @param1 = 'ValueToPassIn'

Tara Kizer
aka tduggan

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-02-02 : 15:05:04

Tara,

This is really the part that I don't quite get, I want to get many values to pass in. Can I do something along these lines

EXEC StoredProcedureName @param1 = 'Select ValueToPassIn from db1'

How do I populate the @param1 with the results of a query?

cc

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-02 : 15:09:39

If you are going to pass multiple values, then:
http://www.sqlteam.com/item.asp?ItemID=11499

Typically a parameter contains one value, but if you need multiples then you should pass them in comma separated. Then use that article for how to get the values out.

Tara Kizer
aka tduggan

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-02-02 : 23:34:22

How do you store the results from a call to another procedure into a local variable?


declare @param varchar(1000)
set @param = 'select info from Different Procedure'

I tried executing a stored procedure which contained the code above and got the following error:

Conversion failed when converting the varchar value 'select * from DifferentProcedure' to data type int.

Is the code snippet above the correct way to retrieve information from a call to a stored procedure and store it in a local variable to use in a different stored procedure?

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-03 : 00:01:46

Hi,
You need to declare an output parameter in your Sp which is going to return a value

Create Proc TestProc(@inparameter varchar(10), @outparameter varchar(100) output)
as
Select @outparamer = column1 from table
where column2 = @inparameter

---Now you can exucute the procedure as
Declare @inparam varchar(10)
Declare @outparam varchar(100)
set @inparam = 'Value1' -- setting value in input parameter
exec testproc @inparam , @outparam output

print @outparam -- output from procedure

Refer BOL for more info

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-03 : 09:28:21

If you want to use the result of a procedure to other procedure, then

Create Procedure......
as

Create table #t(......)
Insert into #t(columns) EXEC yourProc
.
.
.

Then use that table to access data inside the procedure

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources