Loop OR dataset in Store procedure

Hello ,
I want in a store procedure in SQL to have a loop.
I asctually want to SELECT something from a table and then
for each record of the results do something (another selection etc ) is
this posible in a store procedure ?
I know how to do this in vb.net but i want it to be really fast so i am
searching for a way to do this in a store procedure
example:
Select distinct(code) from Table1
for each code
select year from table2 where code=code (of the selection
for ...
for ...
next ...
next ...
The above is an example of what i want to do in the store procedure.
Are you sure you can't use a set statemet?
If not take a look at cursors in BOL
http://sqlservercode.blogspot.com/
"savvaschr@.nodalsoft.com.cy" wrote:

> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc ) is
> this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i am
> searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
>
|||You have to lookup CURSORS in the BOL
<savvaschr@.nodalsoft.com.cy> wrote in message
news:1129813884.938532.251580@.g44g2000cwa.googlegr oups.com...
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc ) is
> this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i am
> searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
>
|||If you using SQL Server 2005,
you could do it in SP with CLR code..
<savvaschr@.nodalsoft.com.cy> wrote in message
news:1129813884.938532.251580@.g44g2000cwa.googlegr oups.com...
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc ) is
> this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i am
> searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
>
|||Perhaps. But there's nothing in the original post to indicate that that
would be necessary or desirable. The OP said "for each record ... do
something (another selection etc)". If "do something" means "select or
update some other data" then chances are the simplest and most
efficient solution is to do a join using set-based SQL code. CLR isn't
the natural place to do data retrieval and manipulation.
David Portas
SQL Server MVP
|||savvaschr@.nodalsoft.com.cy wrote:
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc )
> is this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i
> am searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
You can use a temp table and pull information from it one row at a time
in a loop.If you use a cursor, make sure it is read only, forward only,
and local.
David Gugick
Quest Software
www.imceda.com
www.quest.com
sql

Loop OR dataset in Store procedure

Hello ,
I want in a store procedure in SQL to have a loop.
I asctually want to SELECT something from a table and then
for each record of the results do something (another selection etc ) is
this posible in a store procedure ?
I know how to do this in vb.net but i want it to be really fast so i am
searching for a way to do this in a store procedure
example:
Select distinct(code) from Table1
for each code
select year from table2 where code=code (of the selection
for ...
for ...
next ...
next ...
The above is an example of what i want to do in the store procedure.Are you sure you can't use a set statemet?
If not take a look at cursors in BOL
http://sqlservercode.blogspot.com/
"savvaschr@.nodalsoft.com.cy" wrote:

> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc ) is
> this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i am
> searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
>|||You have to lookup CURSORS in the BOL
<savvaschr@.nodalsoft.com.cy> wrote in message
news:1129813884.938532.251580@.g44g2000cwa.googlegroups.com...
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc ) is
> this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i am
> searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
>|||If you using SQL Server 2005,
you could do it in SP with CLR code..
<savvaschr@.nodalsoft.com.cy> wrote in message
news:1129813884.938532.251580@.g44g2000cwa.googlegroups.com...
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc ) is
> this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i am
> searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
>|||Perhaps. But there's nothing in the original post to indicate that that
would be necessary or desirable. The OP said "for each record ... do
something (another selection etc)". If "do something" means "select or
update some other data" then chances are the simplest and most
efficient solution is to do a join using set-based SQL code. CLR isn't
the natural place to do data retrieval and manipulation.
David Portas
SQL Server MVP
--|||savvaschr@.nodalsoft.com.cy wrote:
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc )
> is this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i
> am searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
You can use a temp table and pull information from it one row at a time
in a loop.If you use a cursor, make sure it is read only, forward only,
and local.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Loop OR dataset in Store procedure

Hello ,
I want in a store procedure in SQL to have a loop.
I asctually want to SELECT something from a table and then
for each record of the results do something (another selection etc ) is
this posible in a store procedure ?
I know how to do this in vb.net but i want it to be really fast so i am
searching for a way to do this in a store procedure
example:
Select distinct(code) from Table1
for each code
select year from table2 where code=code (of the selection
for ...
for ...
next ...
next ...
The above is an example of what i want to do in the store procedure.Are you sure you can't use a set statemet?
If not take a look at cursors in BOL
http://sqlservercode.blogspot.com/
"savvaschr@.nodalsoft.com.cy" wrote:
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc ) is
> this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i am
> searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
>|||You have to lookup CURSORS in the BOL
<savvaschr@.nodalsoft.com.cy> wrote in message
news:1129813884.938532.251580@.g44g2000cwa.googlegroups.com...
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc ) is
> this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i am
> searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
>|||If you using SQL Server 2005,
you could do it in SP with CLR code..
<savvaschr@.nodalsoft.com.cy> wrote in message
news:1129813884.938532.251580@.g44g2000cwa.googlegroups.com...
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc ) is
> this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i am
> searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
>|||Perhaps. But there's nothing in the original post to indicate that that
would be necessary or desirable. The OP said "for each record ... do
something (another selection etc)". If "do something" means "select or
update some other data" then chances are the simplest and most
efficient solution is to do a join using set-based SQL code. CLR isn't
the natural place to do data retrieval and manipulation.
--
David Portas
SQL Server MVP
--|||savvaschr@.nodalsoft.com.cy wrote:
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc )
> is this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i
> am searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
You can use a temp table and pull information from it one row at a time
in a loop.If you use a cursor, make sure it is read only, forward only,
and local.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com

Lookup table structure

I have another lookup table question

so say i have an employee table, and a department table. all the department table will do is store the names of the different departments so how should this be set up using MSSQL

Employee
EmployeeId
FN
LN
DeptName (fk)

Department
DeptId (pk) unique
DeptName unique

*** Or could i do this to make the employee record more meaningful without needing a join. ***

Employee
EmployeeId
FN
LN
DeptName (fk)

Department
DeptId (pk) unique
DeptName unique

*** Or should i just do this and get rid of the Id Field***
Employee
EmployeeId
FN
LN
DeptName (fk)

Department
DeptName (pk) unique

thanks all

I would use DeptID as the FK in Employees -NOT DeptName.

|||+1 I can second that.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Looking for table design pattern for different value types

Hi,

I need to store a list of parameters in a database. Each parameter has a name, description, comment and a value. Easy so far.

However the values are of different types. Each individual parameter has a value which may be of type int, decimal, string, boolean, custom type etc.

Which table design pattern is most appropriate?
We have a heated in-house discussion and I need supporting arguments.

Options explored so far:

1) (De-)serializing the value to a string-type.
2) Adding a column for each type, using only one column at a time.
3) Adding extra value-tables, one table for each type.
The disadvantages for each option are obvious and the basis for our discussion.

Your help in this matter will be appreciated.
Regards, Tonn

Tonn:

Can you take advantage of the SQL_VARIANT data type? Something like:

drop table dbo.parameter
go

create table dbo.parameter
( parmName varchar (40),
parmDescription varchar (80),
parmType tinyint,
parmValue sql_variant
)
go

insert into parameter values ('Integer Parm', 'Just an integer parameter', 1, 1)
insert into parameter values ('Numeric (9,2) parm', 'Yeah', 2, cast (17.50 as numeric (9,2)))
insert into parameter values ('Varchar parm', 'A varchar parm', 3, 'Yes, a varchar')
select * from parameter

-- Sample Output:

-- Warning: The table 'parameter' has been created but its maximum row size (8164) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

-- parmName parmDescription parmType parmValue
-- -- -- --
-- Integer Parm Just an integer parameter 1 1
-- Numeric (9,2) parm Yeah 2 17.50
-- Varchar parm A varchar parm 3 Yes, a varchar

|||

Hi Waldrop,

Thanks for your reply.
It most certainly is an interesting suggestion. It's like option no.1 except that the serialization has been moved to the database.

Is this the preferred way of handling this type of problem? We're in the financial market and this is a common problem for us, but there seems to be no documentation, best practices etc. available so our individual programmers tend to insist on their personal preference.

|||

I think that your purpose is the type of application that the SQL_VARIANT datatype is intended to address. It would be helpful if you could provide some sample update or select statements where you might use your proposed column. I don't think I would want to have a column for each different potential datatype. Below is an example of how you might load the data into variables. Note the need to CAST the data:

declare @.numericVar numeric (9,2)
declare @.varcharVar varchar (80)
declare @.intVar integer

set @.numericVar = (select cast (parmValue as numeric (9,2)) from parameter where parmName = 'Numeric (9,2) parm')
set @.varcharVar = (select cast (parmValue as varchar (80)) from parameter where parmName = 'Varchar parm')
set @.intVar = (select cast (parmValue as integer) from parameter where parmName = 'Integer Parm')

select @.numericVar as [@.numericVar],
@.varcharVar as [@.varcharVar],
@.intVar as [@.intVar]

-- S A M P L E O U T P U T :

-- @.numericVar @.varcharVar @.intVar
-- -- --
-- 17.50 Yes, a varchar 1

Dave

|||

Hi Dave,
I think you've already made your point and put your suggestion at the top of the list.

It's no use sending an example since every proposed alternative so far works. I'm actually looking for some form of authority to stop spending all those man-hours debating about it.
In the past I've had success regarding programming issues by referencing Gamma's "Design Patterns". I was hoping to do the same for database issues, but I found out that it's not going to be that easy.

Looking for sample code for doing store procedures

I'm looking for any good samples that anyone might be aware of on how to write a SQL Server 2005 stored procedure with error handling (using TRY-CATCH blocks) that I can use as a model.

I did find the article "Detecting and Reporting Errors in Stored Procedures - Part 2: SQL Server 2005 TRY-CATCH Blocks" by Rob Garrison. It's pretty good. The article was written based upon an early beta so the author (understandable) wasn't sure about some possible features. I also was disappointed that the examples didn't flow back to showing how the application handle the resulting errors.

If you know of any good article or samples, please let me know.

TIA,

Richard Rosenheim
Please refer to the INSTAWDB.sql script installed with the samples. It has several SP's which use the new TRY...CATCH syntax.|||

Please take a look at the TRY...CATCH topics in Books Online. They also contains lot of examples.

TRY...CATCH (Transact-SQL)
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/248df62a-7334-4bca-8262-235a28f4b07f.htm

Using TRY...CATCH in Transact-SQL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-53645181bc40.htm

Looking for opinions.....want to use SQL Server to store images

I have a client who wants to be able to upload images to his website for his customers to access. It will probably max out at 100 images a month...so not a huge amount of data. I am using asp.net 2.0 and SQL Server 2005.

Does anyone have thoughts or opinions on why I should or should not take this approach?

You could store images on disk or you could store images in SQL server.
It would seem that SQL would require more overhead than Disk so...

Does SQL offer you something that you require that disk storage does not?

If it does, then use it. Pulling images from a db is not an uncommon practice. The small performance drop could be mitigated by caching the images (in-memory for small images or disk for large images).

If nothing you are doing actually requires SQL server though, i would stick to disk.

|||I would use SQL Server. It simplifies quite a bit, and you don't have to give users write permissions any where on the web server making it more secure.|||

I am already using sql server for other reasons so I wouldn't be setting it up specifically for this. Also I would think that sql server would organize the files for you, where they live and whatnot, thus less that I would have to deal with from a programmatic standpoint, not to mention the directory permissions as mentioned above. The images that will be stored will be a little larger than a business card.

Thanks for your responses..