Passing a 'variable' Variable

Passing a 'variable' Variable

Post by Robert Josep » Wed, 22 Sep 1999 04:00:00



Could somebody please tell me how to accomplish the following in a stored
procedure.

The stored procedure.....

AS
SELECT column1, column2
FROM table1

When calling the stored procedure I want to be able to pass a varying number
of parameters to one variable.  For instance...

procName txtA, txtB, txtC, txtD       - One Time
procName txtA, txtB                         - The next time

The stored procedure would evaluate on the first time to:
SELECT column1, column2
FROM table1
WHERE column2 IN(txtA, txtB, txtC, txtD)

AND The next time.....

SELECT column1, column2
FROM table1
WHERE column2 IN(txtA, txtB)

Any suggestions would be appreciated.  Thank you.

 
 
 

Passing a 'variable' Variable

Post by Buddy Ackerma » Wed, 22 Sep 1999 04:00:00


You best option is to make the parameter in your sp a string containing all
of the text that you need in your IN criteria.  Use logic in your
application to create the parameter string then pass that string to your sp.
Of course your example has the added complication of using IN clause which
will force you to create and execute a  dynamic SQL string.  Since dynamic
SQL cannot have a query plan created for it the sp will be slower that if
you knew the criteria up front and could create a normal SQL statement.

EX:



Then, in the sp, you must execute the the statement like this:


+ ')'

There is no real ability to have a variable number of parameters in a sp.
When you create the sp you must specify the parameters and they are not
dynamic other than you can pass null to them other wise if you create a sp
with on parameter then you can only pass it on parameter.  Also, there is no
way to pass it an array.

--Buddy


> Could somebody please tell me how to accomplish the following in a stored
> procedure.

> The stored procedure.....
> CREATE PROCEDURE procName

> AS
> SELECT column1, column2
> FROM table1

> When calling the stored procedure I want to be able to pass a varying
number
> of parameters to one variable.  For instance...

> procName txtA, txtB, txtC, txtD       - One Time
> procName txtA, txtB                         - The next time

> The stored procedure would evaluate on the first time to:
> SELECT column1, column2
> FROM table1
> WHERE column2 IN(txtA, txtB, txtC, txtD)

> AND The next time.....

> SELECT column1, column2
> FROM table1
> WHERE column2 IN(txtA, txtB)

> Any suggestions would be appreciated.  Thank you.


 
 
 

Passing a 'variable' Variable

Post by SQL7DBA NewYor » Wed, 22 Sep 1999 04:00:00


Hi,
I tried a few things but they do not seem to work
Perhaps the gurus here will be able to make this work with some minor
modifications, but here is my effort

CREATE PROCEDURE listprods




or ''produce'''


select categoryID, Description
 FROM categories

-- exec listprods 2
-- exec listprods 1
-- exec listprods 3





> Could somebody please tell me how to accomplish the following in a stored
> procedure.

> The stored procedure.....
> CREATE PROCEDURE procName

> AS
> SELECT column1, column2
> FROM table1

> When calling the stored procedure I want to be able to pass a varying
number
> of parameters to one variable.  For instance...

> procName txtA, txtB, txtC, txtD       - One Time
> procName txtA, txtB                         - The next time

> The stored procedure would evaluate on the first time to:
> SELECT column1, column2
> FROM table1
> WHERE column2 IN(txtA, txtB, txtC, txtD)

> AND The next time.....

> SELECT column1, column2
> FROM table1
> WHERE column2 IN(txtA, txtB)

> Any suggestions would be appreciated.  Thank you.

 
 
 

Passing a 'variable' Variable

Post by Dwight Trumbowe » Wed, 22 Sep 1999 04:00:00


You could use a temp table. I have done this in the past with good results.
The application would create the temp table and populate the values.  The
SQL would look like this:

SELECT column1, column2
FROM table1, tmptable
WHERE column2 IN(select values from tmptable)

--
Dwight Trumbower


> Could somebody please tell me how to accomplish the following in a stored
> procedure.

> The stored procedure.....
> CREATE PROCEDURE procName

> AS
> SELECT column1, column2
> FROM table1

> When calling the stored procedure I want to be able to pass a varying
number
> of parameters to one variable.  For instance...

> procName txtA, txtB, txtC, txtD       - One Time
> procName txtA, txtB                         - The next time

> The stored procedure would evaluate on the first time to:
> SELECT column1, column2
> FROM table1
> WHERE column2 IN(txtA, txtB, txtC, txtD)

> AND The next time.....

> SELECT column1, column2
> FROM table1
> WHERE column2 IN(txtA, txtB)

> Any suggestions would be appreciated.  Thank you.

 
 
 

Passing a 'variable' Variable

Post by Alejandro Mes » Thu, 23 Sep 1999 04:00:00


database: northwind

create procedure sp_test (

as
  set nocount on
  select employeeid, lastname, firstname
  from employees

  set nocount off
  return 0

 *** result ***

 exec sp_test "'1', '2'"

 employeeid  lastname             firstname
----------- -------------------- ----------
1           Davolio              Nancy
2           Fuller               Andrew

sp_test "'1', '2', '3'"

 employeeid  lastname             firstname
----------- -------------------- ----------
1           Davolio              Nancy
2           Fuller               Andrew
3           Leverling            Janet

Good luck!

Alejandro

 
 
 

Passing a 'variable' Variable

Post by SQL7DBA NewYor » Thu, 23 Sep 1999 04:00:00


Hey, That is smart!  Nice!

However, what if he wanted to pass strings?

HS

> database: northwind

> create procedure sp_test (

> as
>   set nocount on
>   select employeeid, lastname, firstname
>   from employees

>   set nocount off
>   return 0

>  *** result ***

>  exec sp_test "'1', '2'"

>  employeeid  lastname             firstname
> ----------- -------------------- ----------
> 1           Davolio              Nancy
> 2           Fuller               Andrew

> sp_test "'1', '2', '3'"

>  employeeid  lastname             firstname
> ----------- -------------------- ----------
> 1           Davolio              Nancy
> 2           Fuller               Andrew
> 3           Leverling            Janet

> Good luck!

> Alejandro


 
 
 

Passing a 'variable' Variable

Post by Listene » Thu, 23 Sep 1999 04:00:00


here is the code.

drop proc tstVariableParam
go


as
begin








end
go

you can execute the above procedure as follows:

tstVariableParam (to select all the rows)

tstVariableParam "'00778'" (pass only one parameter)

tstVariableParam "'00778', '00979'"  (pass any number of parameters)

HTH


> Hey, That is smart!  Nice!

> However, what if he wanted to pass strings?

> HS


> > database: northwind

> > create procedure sp_test (

> > as
> >   set nocount on
> >   select employeeid, lastname, firstname
> >   from employees

> >   set nocount off
> >   return 0

> >  *** result ***

> >  exec sp_test "'1', '2'"

> >  employeeid  lastname             firstname
> > ----------- -------------------- ----------
> > 1           Davolio              Nancy
> > 2           Fuller               Andrew

> > sp_test "'1', '2', '3'"

> >  employeeid  lastname             firstname
> > ----------- -------------------- ----------
> > 1           Davolio              Nancy
> > 2           Fuller               Andrew
> > 3           Leverling            Janet

> > Good luck!

> > Alejandro


 
 
 

1. Variable 'variable' names in Opal

How can I change a variable name at execution time?  Consider the code:

var name1, name2, name3 string
endvar

for i from 1 to 3
 <I want to do ("name"+string(i)) = tc.name>
  tc.skip()
endfor

I have done this for UIobjects on a form, but the syntax checker doesn't
like it for variables.  Any solutions?

2. Difference between Typ I, II and IV drivers?

3. pass variable in 'include' statement

4. on delete cascade

5. Setfilter STILL won't work with passed variables

6. Convert Statement

7. Can't pass a variable to an IN CLAUSE

8. Ways to Validate..

9. Can't pass int as variable to dynamic string in SP

10. Database trigger : bad bind variable 'NEW'

11. in WRITETEXT, 'string' + @variable not working

12. Error 50007: Must declare variable '@c76'.

13. concatenating strings into 'text' variable