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 CPU load of 85% - performance bottleneck?

Hi all,
we have been having performance problems lately. Our CPU load shows about
85% steady. Is this a level that we should be concerned about?
Thanks
Andreasyes.
Greg Jackson
PDX, Oregon|||What would be a reasonable load level? Do you have any links I could read up
on recommended load levels? I googled before but can't find specific numbers.
Thanks
Andreas
"pdxJaxon" wrote:
> yes.
>
> Greg Jackson
> PDX, Oregon
>
>|||general rule of thumb is below 80%
If you see spikes up to 80% or 100% that is totally healthy.
But if the CPU stays above even 50% (in my opinion) for sustained periods,
that is a red flag that more power is needed.
I probably would not run out and buy CPU's if we were constantly at 50% but
at 80% I"d be filling out the P.O.
GAJ

Average CPU load of 85% - performance bottleneck?

Hi all,
we have been having performance problems lately. Our CPU load shows about
85% steady. Is this a level that we should be concerned about?
Thanks
Andreas
yes.
Greg Jackson
PDX, Oregon
|||What would be a reasonable load level? Do you have any links I could read up
on recommended load levels? I googled before but can't find specific numbers.
Thanks
Andreas
"pdxJaxon" wrote:

> yes.
>
> Greg Jackson
> PDX, Oregon
>
>
|||general rule of thumb is below 80%
If you see spikes up to 80% or 100% that is totally healthy.
But if the CPU stays above even 50% (in my opinion) for sustained periods,
that is a red flag that more power is needed.
I probably would not run out and buy CPU's if we were constantly at 50% but
at 80% I"d be filling out the P.O.
GAJ

Average CPU load of 85% - performance bottleneck?

Hi all,
we have been having performance problems lately. Our CPU load shows about
85% steady. Is this a level that we should be concerned about?
Thanks
Andreasyes.
Greg Jackson
PDX, Oregon|||What would be a reasonable load level? Do you have any links I could read up
on recommended load levels? I googled before but can't find specific numbers
.
Thanks
Andreas
"pdxJaxon" wrote:

> yes.
>
> Greg Jackson
> PDX, Oregon
>
>|||general rule of thumb is below 80%
If you see spikes up to 80% or 100% that is totally healthy.
But if the CPU stays above even 50% (in my opinion) for sustained periods,
that is a red flag that more power is needed.
I probably would not run out and buy CPU's if we were constantly at 50% but
at 80% I"d be filling out the P.O.
GAJ