Assign multiple values to a variable in a stored procedure

Assign multiple values to a variable in a stored procedure

Post by Vams » Tue, 18 Nov 2003 09:58:18



Hi,

I am quite new to SQL Programming.  I need to achieve the
output for a  query which is something like this:-

"Select  * from xyz where itemid in(2,3,4,5,6)"

I need to create a stored procedure.
I need to declare a variable in the stored procedure and
store all these values(2,3,4,5,6) and should be writing
the query which is like this:


How do i go about doing this?? i.e i need to store
multiple values in a variable and refer to this variable
in my query. In future if the business needs to add some
other value then it can be just done in the beginning of
the program by adding it to the variable values rather
than going into the query.

I am able to call if its a single value but how do i
assign multiple values to a single variable??

I hope i was clear.Please help me. I would appreciate any
help.
Thanks,
Vamsi

 
 
 

Assign multiple values to a variable in a stored procedure

Post by TS » Tue, 18 Nov 2003 10:47:57


In your Stored Procedure:







> Hi,

> I am quite new to SQL Programming.  I need to achieve the
> output for a  query which is something like this:-

> "Select  * from xyz where itemid in(2,3,4,5,6)"

> I need to create a stored procedure.
> I need to declare a variable in the stored procedure and
> store all these values(2,3,4,5,6) and should be writing
> the query which is like this:


> How do i go about doing this?? i.e i need to store
> multiple values in a variable and refer to this variable
> in my query. In future if the business needs to add some
> other value then it can be just done in the beginning of
> the program by adding it to the variable values rather
> than going into the query.

> I am able to call if its a single value but how do i
> assign multiple values to a single variable??

> I hope i was clear.Please help me. I would appreciate any
> help.
> Thanks,
> Vamsi


 
 
 

Assign multiple values to a variable in a stored procedure

Post by Vams » Tue, 18 Nov 2003 11:04:19


Thanks a million.Great this is working for integer
values,, How do i go about if the datatype is varchar!!.
Could you please let me know!!

Vamsi

>-----Original Message-----
>In your Stored Procedure:







message

>> Hi,

>> I am quite new to SQL Programming.  I need to achieve
the
>> output for a  query which is something like this:-

>> "Select  * from xyz where itemid in(2,3,4,5,6)"

>> I need to create a stored procedure.
>> I need to declare a variable in the stored procedure
and
>> store all these values(2,3,4,5,6) and should be writing
>> the query which is like this:


>> How do i go about doing this?? i.e i need to store
>> multiple values in a variable and refer to this
variable
>> in my query. In future if the business needs to add
some
>> other value then it can be just done in the beginning
of
>> the program by adding it to the variable values rather
>> than going into the query.

>> I am able to call if its a single value but how do i
>> assign multiple values to a single variable??

>> I hope i was clear.Please help me. I would appreciate
any
>> help.
>> Thanks,
>> Vamsi

>.

 
 
 

Assign multiple values to a variable in a stored procedure

Post by oj » Tue, 18 Nov 2003 11:19:45


Erland has written some good info regarding this...

http://www.algonet.se/~sommar/arrays-in-sql.html

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net


> Thanks a million.Great this is working for integer
> values,, How do i go about if the datatype is varchar!!.
> Could you please let me know!!

> Vamsi

> >-----Original Message-----
> >In your Stored Procedure:








> message

> >> Hi,

> >> I am quite new to SQL Programming.  I need to achieve
> the
> >> output for a  query which is something like this:-

> >> "Select  * from xyz where itemid in(2,3,4,5,6)"

> >> I need to create a stored procedure.
> >> I need to declare a variable in the stored procedure
> and
> >> store all these values(2,3,4,5,6) and should be writing
> >> the query which is like this:


> >> How do i go about doing this?? i.e i need to store
> >> multiple values in a variable and refer to this
> variable
> >> in my query. In future if the business needs to add
> some
> >> other value then it can be just done in the beginning
> of
> >> the program by adding it to the variable values rather
> >> than going into the query.

> >> I am able to call if its a single value but how do i
> >> assign multiple values to a single variable??

> >> I hope i was clear.Please help me. I would appreciate
> any
> >> help.
> >> Thanks,
> >> Vamsi

> >.

 
 
 

Assign multiple values to a variable in a stored procedure

Post by Tanuj » Tue, 18 Nov 2003 12:01:48








> Thanks a million.Great this is working for integer
> values,, How do i go about if the datatype is varchar!!.
> Could you please let me know!!

> Vamsi

> >-----Original Message-----
> >In your Stored Procedure:








> message

> >> Hi,

> >> I am quite new to SQL Programming.  I need to achieve
> the
> >> output for a  query which is something like this:-

> >> "Select  * from xyz where itemid in(2,3,4,5,6)"

> >> I need to create a stored procedure.
> >> I need to declare a variable in the stored procedure
> and
> >> store all these values(2,3,4,5,6) and should be writing
> >> the query which is like this:


> >> How do i go about doing this?? i.e i need to store
> >> multiple values in a variable and refer to this
> variable
> >> in my query. In future if the business needs to add
> some
> >> other value then it can be just done in the beginning
> of
> >> the program by adding it to the variable values rather
> >> than going into the query.

> >> I am able to call if its a single value but how do i
> >> assign multiple values to a single variable??

> >> I hope i was clear.Please help me. I would appreciate
> any
> >> help.
> >> Thanks,
> >> Vamsi

> >.

 
 
 

Assign multiple values to a variable in a stored procedure

Post by Vams » Wed, 19 Nov 2003 03:21:54


Hi Tanuja,
      Yes, got it.This is working with characters. This
was great help.Actually i have referred to many sites and
also sql help, but no where i am able to get the exact
picture i wanted. It would have taken me years if not for
your reply.

Thanks a lot once again.
Vamsi.

>-----Original Message-----






message

>> Thanks a million.Great this is working for integer
>> values,, How do i go about if the datatype is
varchar!!.
>> Could you please let me know!!

>> Vamsi

>> >-----Original Message-----
>> >In your Stored Procedure:








>> message

>> >> Hi,

>> >> I am quite new to SQL Programming.  I need to
achieve
>> the
>> >> output for a  query which is something like this:-

>> >> "Select  * from xyz where itemid in(2,3,4,5,6)"

>> >> I need to create a stored procedure.
>> >> I need to declare a variable in the stored procedure
>> and
>> >> store all these values(2,3,4,5,6) and should be
writing
>> >> the query which is like this:


>> >> How do i go about doing this?? i.e i need to store
>> >> multiple values in a variable and refer to this
>> variable
>> >> in my query. In future if the business needs to add
>> some
>> >> other value then it can be just done in the
beginning
>> of
>> >> the program by adding it to the variable values
rather
>> >> than going into the query.

>> >> I am able to call if its a single value but how do i
>> >> assign multiple values to a single variable??

>> >> I hope i was clear.Please help me. I would
appreciate
>> any
>> >> help.
>> >> Thanks,
>> >> Vamsi

>> >.

>.

 
 
 

1. Assigning the values to multiple variables in a Stored procedure

I have written a sql stored procedure (SQL server 7.0) to
assign the values to a number of variables.  It is used
when a new dependant member is added. These values will be
retrieved from the family member's record and inserted
into the dependant's record.

How can I avoid assigning values to these variables
individually by using a SET command, like I am doing in
the following example.  I would like to assign the values
to these varibles with a single sql script.
Is there a easier way to do it without creating a view or
a temporary table.  I am trying to avoid using a trigger
on the table because the web application I am developing
has to work along with the the ERP application we are
currently using.  Thanks.

--Variables











/*This is how I currently assign the values to the

procedure from ASP. */
















2. After upgrading to SQL2000 Std Retail, version still shows "Evaluation"

3. Assign variable value Stored Procedure

4. Show Listbox after Button event in DataGrid

5. Stored Procedures and Assigning value to variable

6. Did someone use DatabaseMetadata.getPrimaryKey(..) with InformixJDBC successfully?

7. assigning values of local variables in stored procedures

8. Help!Cancel a Query using NewEra!

9. Assigning multiple input values to a parameter in stored procedure

10. Assigning multiple values to a parameter in stored procedure

11. Stored procedure returning variable value, not column value?

12. How to assign a value into local variable from a query in string variable

13. exec command for assigning values into variables where tablename is a variable