Giving sprocs multiple 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 2005 Forums

Transact-SQL (2005)

Giving sprocs multiple parameters

Author

Topic

ashraff87
Starting Member

17 Posts

Posted - 2008-08-05 : 10:33:37

I have a sproc that takes in 1 parameter only im executing it like so:


EXECUTE  [myDB].[dbo].[Get_LMCost] 
   'Ford'

However I have may items that are the same as 'Ford' and i dont want to manually run the query for each one, i infact want to run the query for many different parameters, say from a list or somthing, i want it to run for 'Ford' 'BMW' etc but i dont want to have to manually enter and re-execute each time.

So im looking for two things, how you can list explicit multiple paramters and how you can use parameters that have been returned from a nested SQL statment?

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-05 : 10:41:19

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Madhivanan

Failing to plan is Planning to fail

ashraff87
Starting Member

17 Posts

Posted - 2008-08-05 : 11:20:33

Thanks, but i dont want to modify my sprocs to take in sets, im testing them but want to test them with a few cases at once without reexecuting or changing the sprocs themselves.Any ideas?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-05 : 11:33:27

Create a table add the values to it, or you can hard code each variable or do dynamic. Those are the only options I know of.



Create Table Mytable(Make varchar(40))
insert into Mytable
Select 'Ford' Union All
Select 'BMW' Union All
Select 'Infinity'

THen just referance that table in your code.

To return a value


declare @myval int
Exec @Myval = Get_LMCost

or if you are returning a table



create table T_MyResults(MyValues Varchar(20))

Insert INto T_MyResults
exec Get_LMCost

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-05 : 13:12:41

quote:
Originally posted by ashraff87

Thanks, but i dont want to modify my sprocs to take in sets, im testing them but want to test them with a few cases at once without reexecuting or changing the sprocs themselves.Any ideas?

you can have values in a table as suggested and then use a loop to call the stored procedure inside with each of values from table.

Create Table Mytable(ID int identity(1,1),Make varchar(40))
insert into Mytable (Make)
Select 'Ford' Union All
Select 'BMW' Union All
Select 'Infinity'

DECLARE @ID int,@Make varchar(40)
SELECT @ID=MIN(ID)
FROM Mytable

WHILE @ID IS NOT NULL
BEGIN
SELECT @Make=Make
FROM Mytable
WHERE ID=@ID

EXEC [myDB].[dbo].[Get_LMCost] @Make

SELECT @ID=MIN(ID)
FROM Mytable
WHERE ID>@ID
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources