таблицы и колумны в качастве параметров?

AlexeyU

Дата: 16.10.2003 02:24:35

hi

вот есть курсор

DECLARE cur CURSOR LOCAL FAST_FORWARD
FOR
SELECT
T1.fld1,s1.fld2
FROM
T1
GROUP BY
T1.fld1,s1.fld2
HAVING
count(T1.fld1)>1 AND count(isnull(T1.fld2,''))>1

Такие курсоры будут созданы для нескольких таблиц, меняются ТОЛЬКО имена таблиц и столбцов.

Хотелось бы создать курсор ОДИН раз.
А перед OPEN курсора просто задовать в переменных имена таблиц и столбцов.

Как это можно реализовать?

--
---Alexey

Павел Воронцов

Дата: 16.10.2003 06:54:37

declare @cursordecl nvarchar(4000)

set @cursordecl = N'DECLARE cur CURSOR LOCAL FAST_FORWARD ' +

N'FOR SELECT T1.fld1,T1.fld2 FROM T1 ' +

N'GROUP BY T1.fld1,T1.fld2 HAVING ' +

N'count(T1.fld1)>1 AND count(isnull(T1.fld2,''''))>1'

OPEN cur

-- Do whatever you want with cur


CLOSE cur

DEALLOCATE cur

set @cursordecl = N'DECLARE cur CURSOR LOCAL FAST_FORWARD ' +

N'FOR SELECT T2.fld21,T2.fld22 FROM T2 ' +

N'GROUP BY T2.fld21,T2.fld22 HAVING ' +

N'count(T2.fld21)>1 AND count(isnull(T21.fld22,''''))>1'

OPEN cur

-- Do whatever you want with cur


CLOSE cur

DEALLOCATE cur

Павел Воронцов

Дата: 16.10.2003 07:11:21

А, ну да, самое-то главное забыл...

declare @cursordecl nvarchar(4000)

set @cursordecl = N'DECLARE cur CURSOR LOCAL FAST_FORWARD ' +

N'FOR SELECT T1.fld1,T1.fld2 FROM T1 ' +

N'GROUP BY T1.fld1,T1.fld2 HAVING ' +

N'count(T1.fld1)>1 AND count(isnull(T1.fld2,''''))>1'

sp_executesql @cursordecl

OPEN cur

-- Do whatever you want with cur


CLOSE cur

DEALLOCATE cur

set @cursordecl = N'DECLARE cur CURSOR LOCAL FAST_FORWARD ' +

N'FOR SELECT T2.fld21,T2.fld22 FROM T2 ' +

N'GROUP BY T2.fld21,T2.fld22 HAVING ' +

N'count(T2.fld21)>1 AND count(isnull(T21.fld22,''''))>1'

sp_executesql @cursordecl

OPEN cur

-- Do whatever you want with cur


CLOSE cur

DEALLOCATE cur

iSestrin

Дата: 16.10.2003 10:05:20

>
sp_executesql @cursordecl
OPEN cur
<
бррр, это где такое возможно?

хотя ответ несомненно соответствует вопросу:))

Павел Воронцов

Дата: 16.10.2003 10:22:58

iSisterin
RTFM %) Имена курсоров глобальны

iSestrin

Дата: 16.10.2003 10:30:24

не всегда глобальны, у тебя именно локальный объявлен

Glory

Дата: 16.10.2003 10:36:00

BOL
"If neither GLOBAL or LOCAL is specified, the default is controlled by the setting of the default to local cursor database option. In SQL Server version 7.0, this option defaults to FALSE to match earlier versions of SQL Server,"

Павел Воронцов

Дата: 16.10.2003 10:58:56

По барабану

LOCAL

Specifies that the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created. The cursor name is only valid within this scope. The cursor can be referenced by local cursor variables in the batch, stored procedure, or trigger, or a stored procedure OUTPUT parameter. An OUTPUT parameter is used to pass the local cursor back to the calling batch, stored procedure, or trigger, which can assign the parameter to a cursor variable to reference the cursor after the stored procedure terminates. The cursor is implicitly deallocated when the batch, stored procedure, or trigger terminates, unless the cursor was passed back in an OUTPUT parameter. If it is passed back in an OUTPUT parameter, the cursor is deallocated when the last variable referencing it is deallocated or goes out of scope.

В рамках процедуры он у вас виден. Впрочем, надо попробовать, может вы и правы. Но я такие феньки делал когда-то. Давно правда.....

Glory

Дата: 16.10.2003 11:02:40

Ну так
"Specifies that the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created."

А sp_executesql и есть batch