avoid cursor to get variables

Hi all,
I would like to know if there is any other way of doing the following
without a cursor.
Normally I need to return a single record and load of variables with it (the
query is very costly, so I cannot afford running it again and again.
At the moment it is done with a cursor to retrieve the parameters in the
select clause, and right away it is closed. I thought it could be faster to
do directly a select statement and assign the values to the variables
directly, but as the query is complex and has parameters in the from part I
cannot do it.
The query is normally built on a string and accessed through a cursors:
@.sql = "declare c cursor fast_forward for"
@.sql = @.sql + "select field1, field2,... fieldN"
@.sql = @.sql + " from " @.tableParameter
@.sql = @.sql + "where condition1 =" @.condition1Parameter
exec(@.sql)
open c
fetch next from c into @.field1,
@.field2,
…
@.fieldn
close c
deallocate c
What I would like to do is something of the like this:
@.sql = “select @.field1 = field1, @.field2 = field2 … from @.tableParameter
where condition1 = @.condition1Parameter “
But it always fails unless I declare the variables within the string as
well, but in that case I cannot use that variables in the scrip further.
I have also tried to enclose this logic in a stored procedure, but it would
only work if the query to be executed has only the parameters in the select
part, and not the from part.
Anyone has any idea?
Thanks in advance,
TristanHi
You should be able to use sp_executesql to do this.
DECLARE @.sql nvarchar(4000)
DECLARE @.field1 varchar(10)
DECLARE @.field2 varchar(10)
DECLARE @.condition1Parameter varchar(10)
SET @.sql = N'select @.field1 = field1, @.field2 = field2 … from ' +
QUOTENAME(@.tableParameter) + N'where condition1 = @.condition1Parameter'
EXEC sp_executesql @.@.sql,
N'@.field1 vachar(10) OUTPUT, @.field2 vachar(10) OUTPUT,
@.condition1Parameter vachar(10)',
@.field1 OUTPUT, @.field2 OUTPUT, @.condition1Parameter
GO
See books online and http://www.sommarskog.se/dynamic_sql.html#sp_executesql
for other examples.
John
"Tristan" wrote:

> Hi all,
> I would like to know if there is any other way of doing the following
> without a cursor.
> Normally I need to return a single record and load of variables with it (t
he
> query is very costly, so I cannot afford running it again and again.
> At the moment it is done with a cursor to retrieve the parameters in the
> select clause, and right away it is closed. I thought it could be faster t
o
> do directly a select statement and assign the values to the variables
> directly, but as the query is complex and has parameters in the from part
I
> cannot do it.
> The query is normally built on a string and accessed through a cursors:
> @.sql = "declare c cursor fast_forward for"
> @.sql = @.sql + "select field1, field2,... fieldN"
> @.sql = @.sql + " from " @.tableParameter
> @.sql = @.sql + "where condition1 =" @.condition1Parameter
> exec(@.sql)
> open c
> fetch next from c into @.field1,
> @.field2,
> …
> @.fieldn
> close c
> deallocate c
>
> What I would like to do is something of the like this:
> @.sql = “select @.field1 = field1, @.field2 = field2 … from @.tableParamet
er
> where condition1 = @.condition1Parameter “
> But it always fails unless I declare the variables within the string as
> well, but in that case I cannot use that variables in the scrip further.
> I have also tried to enclose this logic in a stored procedure, but it woul
d
> only work if the query to be executed has only the parameters in the selec
t
> part, and not the from part.
> Anyone has any idea?
> Thanks in advance,
> Tristan
>|||Thanks a lot John, that did the job :-) !!!
"John Bell" wrote:
> Hi
> You should be able to use sp_executesql to do this.
> DECLARE @.sql nvarchar(4000)
> DECLARE @.field1 varchar(10)
> DECLARE @.field2 varchar(10)
> DECLARE @.condition1Parameter varchar(10)
> SET @.sql = N'select @.field1 = field1, @.field2 = field2 … from ' +
> QUOTENAME(@.tableParameter) + N'where condition1 = @.condition1Parameter'
> EXEC sp_executesql @.@.sql,
> N'@.field1 vachar(10) OUTPUT, @.field2 vachar(10) OUTPUT,
> @.condition1Parameter vachar(10)',
> @.field1 OUTPUT, @.field2 OUTPUT, @.condition1Parameter
> GO
> See books online and [url]http://www.sommarskog.se/dynamic_sql.html#sp_executesql[/ur
l]
> for other examples.
> John
>
> "Tristan" wrote:
>|||You really need to re-think your entire approach. A table variable is
your way of tellignthe world that your code lacks any cohesion
(remember that term from Software Engineering 101?). This procedure
might be for automobiles, might be squid, or who knows? Well, any
random future user is a better judge and designer than the programmer
who did this.
I will not even remark on using a cursor and the way that you seemto
confuse fields and columns.
You are not yet writing SQL; you are using SQL to fake 1950's
procedural language that you already know.
It may take you years to un-learn . your old habits. But when you do,
your code will run1 to 3 orders of magnitude faster, port to new
platforms, be readable and take up a fraction of the space you are
using now.

Average of fields in a record

Hi.
I am looking for a simple way to average fields in a record..not rows, but fields, using a procedure. If one of the fields is null, then it needs to be excluded from the average.
Any ideas?
Help would be really appreciated!!
Thanks.
NickieOriginally posted by ngillis
Hi.

I am looking for a simple way to average fields in a record..not rows, but fields, using a procedure. If one of the fields is null, then it needs to be excluded from the average.

Any ideas?

Help would be really appreciated!!

Thanks.

Nickie

If it is permanent table - you can use system tables (syscolumns,sysobjects) for creating dynamic query and calculating.
But, I afraid, there is something wrong with db design if you need to do things like this.|||Hi Snail.

Please explain. My tables are the results of survey information that is entered online by clients. I need to calculate the averages of groups of questions that they answered. One survey is one record in the database, which includes the groups of records.
What would be a better way to do this? I can't change it now as the survey is live..but it would be helpful for future use.

I don't really know much about sysobjects. and help is not that helpful. Any ideas where I can get more information?

Thanks.

Nickie|||Whoops..where I said "One survey is one record in the database, which includes the groups of records." I meant to say the groups of questions.

Thanks.|||Hello Nickie,

an easy way to calculate averages of certain fields would be

select (coalesce(field_1, 0) + coalesce(field_2, 0))/number_of_fields
from table

BUT, this query will replace every NULL value with 0 (or every other number you put in the COALESCE statement).

Maybe this "workaround" will help out. If not post again!

Greetings,
Carsten

Autonumber...

Sorry for my bad English but I'm Italian...

I have a question for you...
I delete all record of table but I don't set autonumber field to start with
value 1...
What do you do to set a values of autonumber colomns with Query Analizer or
Enterprise Manager...?I guess you are looking for a code to reset the identity seed for a table.
Here you go:

DBCC CHECKIDENT (TableName, RESEED, 1)

Shervin

"Matrix" <wdilan_NOSPAM@.tin.it> wrote in message
news:bkp1t5$a7j$1@.grillo.cs.interbusiness.it...
> Sorry for my bad English but I'm Italian...
> I have a question for you...
> I delete all record of table but I don't set autonumber field to start
with
> value 1...
> What do you do to set a values of autonumber colomns with Query Analizer
or
> Enterprise Manager...?|||In article <bkp1t5$a7j$1@.grillo.cs.interbusiness.it>,
wdilan_NOSPAM@.tin.it says...
> What do you do to set a values of autonumber colomns with Query Analizer or
> Enterprise Manager...?

I worried about that for a long time, too. It just seemed unnatural to
have a bunch of missing numbers. But after continually resetting them
(there are a couple of ways to do it -- I see someone already is helping
with that) I finally realized that there is no reason to do so.

Think what the numbers will look like after a few weeks of production.

-- Rick|||Since you want to delete all the rows in the table you can use

TRUNCATE TABLE table_name

That will delete all rows AND reset the seed in one command.

- Jason

> "Matrix" <wdilan_NOSPAM@.tin.it> wrote in message
> news:bkp1t5$a7j$1@.grillo.cs.interbusiness.it...
> > Sorry for my bad English but I'm Italian...
> > I have a question for you...
> > I delete all record of table but I don't set autonumber field to start
> with
> > value 1...
> > What do you do to set a values of autonumber colomns with Query Analizer
> or
> > Enterprise Manager...?|||On 25 Sep 2003 10:57:39 -0700 in comp.databases.ms-sqlserver,
JayCallas@.hotmail.com (Jason) wrote:

>Since you want to delete all the rows in the table you can use
>TRUNCATE TABLE table_name
>That will delete all rows AND reset the seed in one command.

Doesn't seem to work if you have foreign key constraints.

--
A)bort, R)etry, I)nfluence with large hammer.

Autonumber...

Sorry for my bad English but I'm Italian...
I have a question for you...
I delete all record of table but I don't set autonumber field to start with
value 1...
What do you do to set a values of autonumber colomns with Query Analizer or
Enterprise Manager...?See my reply in other group. Please don't multipost.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Matrix" <wdilan_NOSPAM@.tin.it> wrote in message news:bkp1m7$kp9$1@.fata.cs.interbusiness.it...
> Sorry for my bad English but I'm Italian...
> I have a question for you...
> I delete all record of table but I don't set autonumber field to start with
> value 1...
> What do you do to set a values of autonumber colomns with Query Analizer or
> Enterprise Manager...?
>
>

AutoNum + Exception

Hi all,
I created a table with an autonum field, with a stored procedure to
insert new record to the table. however I found that the autonum will keep
increase when some unique constraints is voliated.
I tried to use Begin Transaction, and rollback when there's error during the
insert statement, but fail to do it. Any solution to solve it? or I have
missed out something?
Thanks a lot for helping.This behavior is by design.
Even if you rollback a transaction, the generated identity will not reset.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
<Windy> wrote in message news:ONwFHC2DGHA.2956@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I created a table with an autonum field, with a stored procedure to
> insert new record to the table. however I found that the autonum will keep
> increase when some unique constraints is voliated.
> I tried to use Begin Transaction, and rollback when there's error during
> the insert statement, but fail to do it. Any solution to solve it? or I
> have missed out something?
> Thanks a lot for helping.
>|||(Windy) writes:
> I created a table with an autonum field, with a stored procedure to
> insert new record to the table. however I found that the autonum will keep
> increase when some unique constraints is voliated.
> I tried to use Begin Transaction, and rollback when there's error during
> the insert statement, but fail to do it. Any solution to solve it? or I
> have missed out something?
There are two main roads to create an surrogate id: 1) Roll your own. 2) Let
the database do it. This is not merely a question of convienence. The
IDENTITY function is designed to be scalable, so that many processes
can insert at the same time without blocking each other. For this reason,
the counter for the IDENTITY is not reset when a transaction rolled back.
Sometimes you have the business requirement that number must be contiguous,
this is typical for accounting applications. In this case, you must roll
your own. But you must then also be prepare to handled a higher degree
of blocking. To wit, process 1 gets a number, and uses it in a longer
transaction. Process 2 also needs a number, but it cannot get one until
Process 1 has completed, for the simple reason that Process 2 cannot
know which is the next number, as that depends on whether Process 1 will
commit or rollback.
The scheme for rolling your own is:
BEGIN TRANSACTION
SELECT @.nextid = coalesce(MAX(id), 0) + 1
FROM tbl WITH (HOLDLOCK, UPDLOCK)
INSERT tbl (id, ...
SELECT @.nextid...
-- More work
COMMIT TRANSACTION
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||This behavior is by design, but isn't documented very well in BOL.
It's not a good practice to rely on the contiguity of IDENTITY values for
permanent or otherwise shared tables. A surrogate key value should not add
any meaning to the row for which it is a surrogate. This means that neither
its magnitude nor its relative position with respect to other rows'
surrogate key values should be relied upon in your data model. All that is
important is that that value be different for each row. Also, since
surrogate key values should not add anything, they also cannot be used to
guarantee entity integrity. A unique constraint must also exist whose
definition doesn't include the surrogate key column.
Many outside influences can affect the values generated by IDENTITY. For
example, the administrator may reset the IDENTITY seed if an overflow is
about to occur. The IDENTITY values on a table may need to be changed in
order to facilitate replication or consolidation with other databases.
Also, gaps can occur due to rollbacks or deletes.
I'm not saying that IDENTITY is a bad thing; on the contrary: it's a
valuable tool, but it's important that it be used correctly.
I have used the IDENTITY property on table variables and local temporary
tables to facilitate sequencing and ordering, but that occurs entirely
within the body of a procedure or trigger, and because the objects are local
to the connection, there cannot be any any interaction with other
transactions.
<Windy> wrote in message news:ONwFHC2DGHA.2956@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I created a table with an autonum field, with a stored procedure to
> insert new record to the table. however I found that the autonum will keep
> increase when some unique constraints is voliated.
> I tried to use Begin Transaction, and rollback when there's error during
> the insert statement, but fail to do it. Any solution to solve it? or I
> have missed out something?
> Thanks a lot for helping.
>|||The December 2005 issue of SQL Server Magazine has an article by Itzik
Ben-Gan on creating a custom identity generating stored procedure.
http://www.windowsitpro.com/Article...8165/48165.html
You'll need a subscription to access the full article.
"Windy" wrote:

> Hi all,
> I created a table with an autonum field, with a stored procedure to
> insert new record to the table. however I found that the autonum will keep
> increase when some unique constraints is voliated.
> I tried to use Begin Transaction, and rollback when there's error during t
he
> insert statement, but fail to do it. Any solution to solve it? or I have
> missed out something?
> Thanks a lot for helping.
>
>|||Many thanks to all of you guys.
<Windy> glsD:ONwFHC2DGHA.2956@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I created a table with an autonum field, with a stored procedure to
> insert new record to the table. however I found that the autonum will keep
> increase when some unique constraints is voliated.
> I tried to use Begin Transaction, and rollback when there's error during
> the insert statement, but fail to do it. Any solution to solve it? or I
> have missed out something?
> Thanks a lot for helping.
>