Доброго здоровья.
Я проектирую базу, которая будет еще в дальнейшем изменяться и расти, по мере поступления требований от заказчика.
(Может, требования и не поступят, но лучше на это заложиться)
Каждая таблица имеет поле [id] которое primary key.

Идея моей проблемы такова.
Написать функцию (назовем её [Get_Using_Count]), которая
    получает: имя таблицы и id записи
    возвращает: кол-во применений этого id в связанных таблицах.

Что это значит. Например:
Есть таблицы [Накладные], [Товары к накладным] и [Чеки оплаты]
Случается что накладные создаются по ошибке и не имеют связанных "Товаров к накладным" и "Чеков оплаты"
То есть ни одна запись в этих таблицах не ссылается на "нашу" накладную (у них есть поле накладная_id).

Выполнив запрос "SELECT *, Get_Using_Count('dbo.Накладные', [id]) FROM [Накладные]",
можно смело удалять записи у которых кол-во связанных записей 0

Следующий код формирует уже готовые запросы на выборку для каждой связанной таблицы с указанной.

На ваших базах она тоже отработает ничего не переделывая (только тип @ID, возможно)

Натравив на нее CURSOR я намеревался собрать их в одну строку и выполнить

Но вот не задача - я не могу выполнить EXEC, так чтобы он мне вернул результат не на экран а в переменную.
Допустим её можно решить через еще один CURSOR.
Заменить строчку *1* на


Но возникает вторая проблема которую я не смог побороть
Это:
Сообщение 443, уровень 16, состояние 14, процедура Get_Using_Count, строка 32
Недопустимое использование оператора "EXECUTE STRING", оказывающего побочное действие, в функции.

Возникает в строчке *2*

Вопрос:
Как обойти этот запрет? Или как решить задачу другим методом?


Это сообщение отредактировал(а) Akina - 5.2.2012, 10:23

То есть, если Вам вдруг захочется, допустим, создать к накладным список необходимых подписей, подключить его через классическое многие-ко-многим и прописать дефолт -- то вся Ваша изящная схема успешно накернится. Или, допустим, не Вам, а следующему программисту, ковыряющему эту опердень. Он будет рад, вероятно.

В общем, не выпендривайтесь. Перечислите список зависимых таблиц и полей явно. 

Если очень хочется сделать одну абстракцыю в БД, отвечающую за это -- вынесите во view. Впрочем, по-моему, дажэ в этом смысла нет: оно, скорее всего, нужно строго в одном месте -- потому делайте просто запросом, без функцый и прочих наворотов.

Цитата(tzirechnoy @  5.2.2012,  10:33 )

через классическое многие-ко-многим

Я никогда не связываю таблицы этим методом. Это плохая практика.
Если такие ситуации возникают, создается таблица для избавления от "порочных" связей
К примеру: Две таблицы [Пользователи] и [Привилегии] видим отношение многие-ко-многим.
Создаем таблицу [Назначенные_Привилегии] в которой 3 поля [id], [пользователь_id] и [привилегия_id].

В моем случаи "классическое многие-ко-многим" совсем не классическое .

Цитата(tzirechnoy @  5.2.2012,  10:33 )

не Вам, а следующему программисту, ковыряющему эту опердень.

Другого программиста не будет. У меня заказчик родной дядя. 
А если и будет, то ему просто придется немного подумать и не создавать связи многие-ко-многим

Это сообщение отредактировал(а) Gwire - 5.2.2012, 15:49

Дайте угадаю, вы накрутили  foreign keys с каскадными операциями, а теперь пытаетесь реализовать их первородную функцию? 

Добавлено @ 16:11

Отношение многие ко многим - самая чт они наесть обычная практика.
В отличии от каскадных операций на внешних ключах.

Добавлено @ 16:12

Вы описали что ни наесть классическое многие-ко-многим.  

Это сообщение отредактировал(а) Zloxa - 5.2.2012, 16:52

Цитата(Zloxa @  5.2.2012,  16:10 )

Дайте угадаю, вы накрутили  foreign keys с каскадными операциями, а теперь пытаетесь реализовать их первородную функцию?

Нет. Обычный внешний ключ, не позволяющий удалять если есть связи.

Логика такова: если запись попала в базу и к ней были добавлены связанные записи, она не может быть удалена.
Например: Таблица [Список_Товаров]. Как я могу позволить удалить какой-то товар, если есть накладные которые используют этот товар. А представляете, что будет если удалить товар каскадно.

Мне эта функция нужна для информативности. Что бы оператор понимал какие записи (накладные, товары, клиенты, адреса ...) являются "мусором". Для чистки базы от этого "мусора" (вручную. Возможно какие-то записи будут добавлены наперед, и об этом знает только оператор).

Цитата(Zloxa @  5.2.2012,  16:10 )

В отличии от каскадных операций на внешних ключах.

Как я уже сказал я не использую каскадные операций.
При удалении записи, запись не удаляется физически (если есть связи), а устанавливается поле [enable] = 0.

Цитата(Zloxa @  5.2.2012,  16:10 )

Вы описали что ни наесть классическое многие-ко-многим

Логически они существуют, как в моем примере. Но в базе нет таблиц связанных между собой методом "многие-ко-многим".
Как я уже говорил "Это плохая практика". От этих моментов нужно избавляться еще на этапе проектирования базы.



Это сообщение отредактировал(а) Gwire - 5.2.2012, 19:46

  
Вы бредите.

Ваш пример описал физическую реализацию связи многие ко многим.
Она классически реализуется физически через третью таблицу.  И это нормальная парктика. Вы себе противоречите.


Для того, чтобы вы этого не смогли и устанавливаются внешние ключи, зачем дублировать их функционал собственным велосипедом? (других версий, оправдывающих такое желание, кроме касскадов, я высосать из пальца  не смог )

Тем более что вы врядли сможете его реализовать более эффективно нежели это уже сделано на стороне СУБД. Вы не думали над тем, что в процессе вычисления вашей функции, могут измениться данные, повлиявшие на ее результат? Например вы удаляете товар: функция уже просмотрела таблицу накладных, стала смотреть в таблице остатков, а в это время другой пользователь создал накладную, которая приходует удаляемый вами товар, а к тому моменту, как функция закончит свою работу, вполне возможно, товар уже будет проведен и по остаткам. Как вы полагали избегать такой ситуации? Захватывать в монопольный доступ все объекты базы, которые используют товар? Проверка по внешним ключам, эту задачу решает куда меньшим количеством ресурсов. Пытаемся удалить запись, если попытка обламывается с ошибкой по нарушению внешнего ключа, размечаем запись как не активную - и все.

Zloxa, у меня складывается впечатление, что Вы читаете посты через слово
или не особо разбираетесь в разнице между связями таблица-таблица и сущность-сущность.
Вы о какой связи думали когда писали:

Цитата(Zloxa @  5.2.2012,  20:15 )

Цитата(Gwire @  5.2.2012,  18:06 ) Как я уже говорил "Это плохая практика". От этих моментов нужно избавляться еще на этапе проектирования базы.     Вы бредите.

По всей видимости это была связь сущность-сущность. Потому как ваша реакция, оправдана только в этом случае.
Действительно, невозможно избавится от связей многие-ко-многим между сущностями.
Но таких связей нужно избегать между таблицами, путем добавления третей, связующей, таблицы. 

Цитата(Zloxa @  5.2.2012,  20:15 )

Она классически реализуется физически через третью таблицу

Здесь, Вы уже говорите о таблицах, где я с вами солидарен, тем более, что я всегда говорил о таблицах.

Связь между не уникальным полем одной таблицы и не уникальным полем другой таблицы - это простая формулировка связи многие-ко-многим между таблицами. Именно от этих связей лучше избавляться. Именно они являются плохой практикой.

А то что, связь между сущностями многие-ко-многим - 

Цитата(Zloxa @  5.2.2012,  16:10 )

что ни наесть классическое

ни кто не спорит.

Цитата(Zloxa @  5.2.2012,  20:15 )

Вы не думали над тем, что в процессе вычисления вашей функции, могут измениться данные, повлиявшие на ее результат? Например вы удаляете товар: функция уже просмотрела таблицу накладных, стала смотреть в таблице остатков, а в это время другой пользователь создал накладную, которая приходует удаляемый вами товар, а к тому моменту, как функция закончит свою работу, вполне возможно, товар уже будет проведен и по остаткам.

Во первых: один пользователь не может удалять накладные другого, если не имеет определенных привилегий.
  А тот кто имеет привилегий будет это делать в конце рабочего дня (иначе от всех получит по шее).
Во вторых: я не собираюсь с помощью нее удалять записи.

Цитата(Gwire @  5.2.2012,  18:06 )

Мне эта функция нужна для информативности. Что бы оператор понимал какие записи (накладные, товары, клиенты, адреса ...) являются "мусором". Для чистки базы от этого "мусора" (вручную. Возможно какие-то записи будут добавлены наперед, и об этом знает только оператор).

Имелось ввиду, что оператор принимает решение, что является "мусором". Программа, со своей стороны, просто
подсвечивает несвязанные записи. И "оператор" имеется ввиду любой пользователь программы (с любыми привилегиями).

Цитата(Zloxa @  5.2.2012,  20:15 )

Пытаемся удалить запись, если попытка обламывается с ошибкой по нарушению внешнего ключа, размечаем запись как не активную

Так и реализовано. Но этот метод не подходит для того, что бы просто посмотреть "пустая" запись или нет.

Это сообщение отредактировал(а) Gwire - 6.2.2012, 05:23

  
Уважаемый Gwire, сравнение Ваших постингов на форуме с постингами Zloxa порождает навязчивое желание предложить Вам посчитать своё мнение ошибочным и прислушаться к его советам... и уж тем более не пытаться обвинять его в отсутствии знаний в тех областях, где он явно на две головы выше Вас...
Впрочем, моё мнение Вы тоже имеете право проигнорировать.

Вы заблуждаетесь, когда называете "Связь между не уникальным полем одной таблицы и не уникальным полем другой таблицы" отношением многие-ко-многим. На основании этого заблуждения вы заявили, что связи многие-ко-многим - плохая практика, которую следует избегать. Я оспариваю исключительно этот тезис. То что под связью многие-ко-многим вы имеете в виду вовсе не связь многие-ко-многим, нисколь не нивелирует абсурдности  вашего заявления.   Если это ваше, столь же уверенное, сколь и абсурдное зявление прочтет новичек, это, определенно, может сбить его с толку, пустить по ложному пути развития. Лишь по этой причине я его так настойчиво оспариваю. 


Вы еще и в запросе собираетесь эту функцию использовать?  
Что ж. Дело ваше. Лоб ваш, не мне его жалеть.   
Впрочем, может статься, вы его и не расшибете... в этот раз  

Это сообщение отредактировал(а) Zloxa - 6.2.2012, 10:23

Zloxa, прошу прощения. MS SQL действительно не умеет создавать настоящие связи многие-ко-многим между двумя таблицами. Моя ошибка не проверил. Потому как был уверен что, все СУБД это умеют (точно могут FoxPro для dos, Access 2003, и Oracle не помню версию). Теперь понятно почему у вас с Akina, мои реплики вызвали негодование. MS SQL убрав возможность создавать такие связи не могла убрать самого понятия "многие-ко-многим", и они его перефразировали.



По всей видимости tzirechnoy тоже думал о тех-же "многие-ко-многим" что и я.

Теперь не вижу проблемы.
Считаю вопрос со связями решенным.

Остается "первородный" вопрос:
  Можно ли выполнить запрос сохраненный в строке из функции?
  Если "Да" - подскажите как.

Точку зрения tzirechnoy я понял. Если совсем ничего не найдется - реализую обычным перечисление.

Те СУБД, которые это умеют, на самом деле просто умеют создать эту самую дополнительную промежуточную связующую таблицу и далеко спрятать её от конечного пользователя.

Akina, так Вы согласны, что мое мнение не было ошибочным.
И что Вы с Zloxa просто могли сказать, что MS SQL не поддерживает прямые связи такого типа
(если конечно знали что бывает по другому).

Gwire, да я вообще дилетант... но твёрдо знаю, что много-ко-много следует реализовывать через связующую таблицу, даже когда мне предлагают этот сервис в готовой форме (Аксесс-2003 не умеет, а 2007 умеет - но ничто не заставит меня этой возможностью воспользоваться). Ибо "всё, что ты не укажешь как именно делать, имеет право делаться как угодно" - а мне такого добра даром не надо. 

Постарайтесь вспомнить. Мне очень интересно.  
8,9,10,11 - не умеют создавать fk к полю, не имеющему ограничение уникальности.   


По всей видимости вы не поняли о чем говорил tzirechnoy.
Впрочем, я тоже не уверен что правильно его понял.
На сколько я понял, он  он имеет в виду возможность расширения схемы таким образом, что ваша проверка всегда будет возвращать наличие связей. В качестве примера он привел расширение атрибутов накладной списком утверждающих ее лиц, который заполняется автоматически при создании этой накладной. Ваша проверка тогда станет возвращать наличие связанных записей, при этом накладная все равно может попадать под критерий удаления. 

Даже не смотря на то, что, возможно, некие СУБД позволяют создавать связь по не уникальному полю, утверждение что отношение многие-ко-многим порочная практика, которой следует избегать, само по себе, остается абсурдным.  

Это сообщение отредактировал(а) Zloxa - 6.2.2012, 17:49

Zloxa, читайте внимательнее