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.