Loop...

Can someone please tell me if it is possible to create variables dynamically
or with the use of a loop and if so how...
I have the following SQL statement and would like to cut down the number of
variables by using some kind of loop to generate the variables and update
statement!
Thanks...
CREATE PROCEDURE dbo.MenuUpdate
(
@.ItemID Int,
@.Date SmallDateTime,
@.mItem1 nvarchar (255),
@.mItem2 nvarchar (255),
@.mItem3 nvarchar (255),
@.mItem4 nvarchar (255),
@.mItem5 nvarchar (255),
@.mItem6 nvarchar (255),
@.mItem7 nvarchar (255),
@.mItem8 nvarchar (255),
@.mItem9 nvarchar (255),
@.mItem10 nvarchar (255),
@.mPrice1 nvarchar (255),
@.mPrice2 nvarchar (255),
@.mPrice3 nvarchar (255),
@.mPrice4 nvarchar (255),
@.mPrice5 nvarchar (255),
@.mPrice6 nvarchar (255),
@.mPrice7 nvarchar (255),
@.mPrice8 nvarchar (255),
@.mPrice9 nvarchar (255),
@.mPrice10 nvarchar (255)
)
AS
UPDATE tblRestaurant
SET
DateCreated = @.Date,
MenuItem = @.mItem1,
ItemPrice = @.mPrice1
WHERE itemID = @.itemID
GOHave a look at
http://www.sommarskog.se/arrays-in-sql.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Tim::.." <myatix_at_hotmail.com> wrote in message
news:099D2D78-9B32-4EE0-AB1C-103A828F8E54@.microsoft.com...
> Can someone please tell me if it is possible to create variables
> dynamically
> or with the use of a loop and if so how...
> I have the following SQL statement and would like to cut down the number
> of
> variables by using some kind of loop to generate the variables and update
> statement!
> Thanks...
>
> CREATE PROCEDURE dbo.MenuUpdate
> (
> @.ItemID Int,
> @.Date SmallDateTime,
> @.mItem1 nvarchar (255),
> @.mItem2 nvarchar (255),
> @.mItem3 nvarchar (255),
> @.mItem4 nvarchar (255),
> @.mItem5 nvarchar (255),
> @.mItem6 nvarchar (255),
> @.mItem7 nvarchar (255),
> @.mItem8 nvarchar (255),
> @.mItem9 nvarchar (255),
> @.mItem10 nvarchar (255),
> @.mPrice1 nvarchar (255),
> @.mPrice2 nvarchar (255),
> @.mPrice3 nvarchar (255),
> @.mPrice4 nvarchar (255),
> @.mPrice5 nvarchar (255),
> @.mPrice6 nvarchar (255),
> @.mPrice7 nvarchar (255),
> @.mPrice8 nvarchar (255),
> @.mPrice9 nvarchar (255),
> @.mPrice10 nvarchar (255)
> )
> AS
> UPDATE tblRestaurant
> SET
> DateCreated = @.Date,
> MenuItem = @.mItem1,
> ItemPrice = @.mPrice1
> WHERE itemID = @.itemID
> GO
>|||OK...
That just blew my mind...
Is there not a way of just adding a loop in a sp or something to generate
the parameters and Update statemet?
Thanks anyway...
"Roji. P. Thomas" wrote:

> Have a look at
> http://www.sommarskog.se/arrays-in-sql.html
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "Tim::.." <myatix_at_hotmail.com> wrote in message
> news:099D2D78-9B32-4EE0-AB1C-103A828F8E54@.microsoft.com...
>
>|||>Is there not a way of just adding a loop in a sp or something to generate
>the parameters
It seems that you are passing the values for these parameters from your
client application.
Then how can you just loop inside the SP and get the values?
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Tim::.." <myatix_at_hotmail.com> wrote in message
news:BCADDEAA-5591-4D1F-81FD-C83AC4D2DAA9@.microsoft.com...
> OK...
> That just blew my mind...
> Is there not a way of just adding a loop in a sp or something to generate
> the parameters and Update statemet?
> Thanks anyway...
>
> "Roji. P. Thomas" wrote:
>|||No. I know, this can be frustrating sometimes when you're used to working in
a Real programming language, declaring variables on the fly, arrays and
loops.
But SQL server is not a programming language.
Use the techniques in the link Roji told you.
I use the Iterative Method in a few procedures.
A bit of work the first time you use it but the second time around will be a
lot easier.
In the example you give you would have only 3 or 4 parameters (depending on
how you set it up) and 1 update statement.
And you could pass as many or as few mItem/mPrice as you want and the
procedure would handle it.
"Tim::.." <myatix_at_hotmail.com> wrote in message
news:BCADDEAA-5591-4D1F-81FD-C83AC4D2DAA9@.microsoft.com...
> OK...
> That just blew my mind...
> Is there not a way of just adding a loop in a sp or something to generate
> the parameters and Update statemet?
> Thanks anyway...
>
> "Roji. P. Thomas" wrote:
>|||> Is there not a way of just adding a loop in a sp or something to generate
> the parameters and Update statemet?
No. You can create a function, once, to deal with this, and you shouldn't
have to think hard about it again. I have one for integers and one for
varchar listed at http://www.aspfaq.com/2248 .. they couldn't be much easier
to use.