Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 как добавить ключевые поля identity в таблицу с помощью SP если их значения не вводятся?  [new]
ksenya.urevna
Member

Откуда: vorkuta
Сообщений: 12
ребята у меня к вам просьба! поделитесь знаниями кто может)))
у меня такой вопрос:
есть бд, на иллюстрации ниже показано соединение
[img="D:\диск Д\для диплома\студ мой.jpg"]

мне нужно произвести процедуру зачисления студента. то есть ввести данные -
id_order, date_order, surname, name, middle_name - это новые которые появляются каждый раз, а есть данные которые вводятся пользователем - вернее выбираются из списка уже существующих в таблицах данных - типа id_reason, id_group.....
есть табличка movement в которую собираются данные о движении - то есть о зачислении - id_stud, id_order, id_group, id_reason
но сложность в том, что пользователь не вводит эти данные вручную - он вообще не знает какой там у кого id... нужно их как то получить по введенным данным, только вот как? поделитесь информацией. сделать вторую процедуру, которая вытаскивает значения id из таблиц по соответствующим им введенным данным? или запросами можно внутри процедуры? я просто совсем новичок на сервере... и пока понятия не имею как это можно сделать....
может хоть на мысли наведете или ссылками поделитесь.. на msdn пересмотрела все о написании хранимых процедур и на форуме примеров много, но то что нужно не нашла.... с SP знакома пятый день))
заранее благодарна ))
22 май 11, 18:01    [10690714]     Ответить | Цитировать Сообщить модератору
 Re: как добавить ключевые поля identity в таблицу с помощью SP если их значения не вводятся?  [new]
ksenya.urevna
Member

Откуда: vorkuta
Сообщений: 12


К сообщению приложен файл. Размер - 17Kb
22 май 11, 18:01    [10690715]     Ответить | Цитировать Сообщить модератору
 Re: как добавить ключевые поля identity в таблицу с помощью SP, их значения не вводятся?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Нифига не видно, нифига непонятно. Словно в душу плюют.
Рекомендации по оформлению сообщений в форуме
Приведите скрипты таблиц, скрипт для заливки тестовых данных. Текущую процедуру и места на которых застряли. И что надо получить (таблички результатов). И поменьше воды.

ksenya.urevna
как добавить ключевые поля identity в таблицу с помощью SP если их значения не вводятся?
Поля в таблицу добавляются командой ALTER TABLE ADD COLUMN.
Данные вводятся вводятся командой INSERT.
Вам, судя по всему, надо другое, но главное ещё разобраться в самой задаче.

INSERT	dbo.InsertTable	(... columns ...)
SELECT	T1.Col1 ... TN.ColM
FROM	           dbo.Table1	T1
	      JOIN dbo.Table2	T2 ON T2.ID = T1.ColX
	CROSS JOIN dbo.Table3	T3
	...
	LEFT  JOIN dbo.TableN	TN ON ...
WHERE	    T1.ColY = @Par1
	AND T3.ColZ = @Par2
	...

SET @ID = Scope_Identity()
Осталось подставить правильные таблицы и колонки и выстроить нужный порядок (как я понимаю вставлять надо в несколько таблиц). :)

ksenya.urevna
для диплома
22 май 11, 22:49    [10691381]     Ответить | Цитировать Сообщить модератору
 Re:как добавить ключевые поля identity в таблицу с помощью SP если их значения не вводятся  [new]
ksenya.urevna
Member

Откуда: vorkuta
Сообщений: 12
ALTER PROCEDURE [dbo].[zach1] 
	-- Add the parameters for the stored procedure here
	@id_order nchar(10),
	@date_order date,
		--по умолчанию надо поставить ид висты равным 1	
	@surname nchar(30),
	@name nchar(15),
	@middle_name nchar(30),
	@name_group nchar(20),
	@id_vista int=1
	
as 
	insert into [uchet_stud].[dbo].[order] (id_order, date_order, id_vista)
	values (@id_order, @date_order, @id_vista)
	 insert into [uchet_stud].[dbo].[student] (surname, name, middle_name)
	 values (@surname , @name , @middle_name)
	 insert into [uchet_stud].[dbo].[group] (name_group)
	 values (@name_group)
	--insert into 

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT 'Номер приказа'=@id_order , 'Дата приказа'=@date_order ,'Имя группы'=@name_group, 'Фамилия '=@surname , 'Имя'=@name , 'Отчество'=@middle_name   
пока процедура выглядит так. то есть результат таков:
в таблицу приказы занесены данные -

id_order date_order id_view
111 01.01.01 1
112 01.01.01 1


в таблицу student :
id_stud surname name middle_name
1 иванов иван иванович
2 петров петр петрович

причем id_stud проставил sql сам - поле identity

в таблице групп :
id_group name_group
1 пгс
2 ист


у меня есть табличка >>>movement<<< , которая собирает все id и формирует само движение

id_movement id_stud id_order id_group id_reason
1 1 111 1 10
2 2 111 1 10
... ... ...

и так далее...
дело в том, что в таблицах групп (id_group) и причин (id_reason) уже существуют записи из которых просто делается выбор..
то есть
ALTER PROCEDURE [dbo].[zach1] 
	-- Add the parameters for the stored procedure here
	@id_order nchar(10),
	@date_order date,
		--по умолчанию надо поставить ид висты равным 1	
	@surname nchar(30),
	@name nchar(15),
	@middle_name nchar(30),
	@name_group nchar(20),
	@id_vista int=1
	==>ВХОДНЫМИ в данном случае здесь должны быть еще и 
	==>name_reason и в случае если группа уже существует то name_group
	as 
	==>а где то здесь , если я правильно поняла, нужно чтоб был определен для  
	==>name_reason, name_group, surname его порядковый номер и все эти порядковые номера
	==>должны быть занесены в таблицу движения movemment для создания самого движения
	
	insert into [uchet_stud].[dbo].[order] (id_order, date_order, id_vista)
	values (@id_order, @date_order, @id_vista)
	 insert into [uchet_stud].[dbo].[student] (surname, name, middle_name)
	 values (@surname , @name , @middle_name)
	 insert into [uchet_stud].[dbo].[group] (name_group)
	 values (@name_group)
	 ==>а вот здесь нужно просто добавить извлеченные порядковые номера в таблицу movement
	 ==>типа вот так 
	 insert into [uchet_stud].[dbo].[movement] (id_stud, id_group, id_order, id_reason)
	 values (@id_stud, @id_order, @id_group, @id_reason)
	--insert into 

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

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

К сообщению приложен файл. Размер - 68Kb
23 май 11, 00:11    [10691626]     Ответить | Цитировать Сообщить модератору
 Re: как добавить ключевые поля identity в таблицу с помощью SP если их значения не вводятся?  [new]
ksenya.urevna
Member

Откуда: vorkuta
Сообщений: 12
извините за размер) пробовала вставить второй раз) первый неудачно))) зато теперь все четко видно)))
23 май 11, 00:13    [10691633]     Ответить | Цитировать Сообщить модератору
 Re: как добавить ключевые поля identity в таблицу с помощью SP, их значения не вводятся?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Вас просили скрипты а не картинку.
CREATE PROCEDURE [dbo].[spAddOrder]
	 @Order		NChar(10)		-- ???
	,@Date		Date
	,@Vista		Int	= 1		-- ???
	,@Group		NVarChar(20)
	,@Reason	NVarChar(20)
	,@SurName	NVarChar(30)
	,@Name		NVarChar(15)
	,@MiddleName	NVarChar(30)
--	,@@Student	Int	OUT
AS BEGIN
	SET NOCOUNT ON;
	DECLARE	 @Student	Int

--	BEGIN TRAN TAddOrder
		INSERT	dbo.[Order]	(id_order,date_order, id_vista)
		VALUES			(@Order  ,@Date     ,@Vista   )
		-- Нет поля Identity ?
		
		INSERT	dbo.Student	( SurName, Name, Middle_Name)
		VALUES			(@SurName,@Name,@MiddleName)
		SET @Student = Scope_Identity()

		INSERT	dbo.Movement	(id_stud ,id_order,  id_group,  id_reason)
		SELECT			 @Student,@Order  ,G.id_group,R.id_reason)
		FROM	           dbo.[Group]	G
			CROSS JOIN dbo.Reason	R
		WHERE	    G.name_group  = @Group
			AND R.name_reason = @Reason

--		IF (@@RowCount = 0) BEGIN
--			ROLLBACK
--			RAISERROR('Не найдены указанная группа (%s) или причина (%s)',18,1,@Group,@Reason)
--			RETURN @@Error
--		END

--	COMMIT TRAN TAddOrder
END
GO
Или лучше:
		INSERT	dbo.Movement	(id_stud ,id_order,  id_group,  id_reason)
		VALUES	(@Student
			,@Order
			,(SELECT id_group  FROM dbo.[Group] WHERE name_group  = @Group )
			,(SELECT id_reason FROM dbo.Reason  WHERE name_reason = @Reason))
Тогда IF (@@RowCount = 0) не нужен и тупо вывалится в ошибку само.
Над транзакциями подумайте.
23 май 11, 11:30    [10692978]     Ответить | Цитировать Сообщить модератору
 Re: как добавить ключевые поля identity в таблицу с помощью SP если их значения не вводятся?  [new]
ksenya.urevna
Member

Откуда: vorkuta
Сообщений: 12
Mnior,
спасибо вам большое)) order стоит строковым потому что номер приказа бывает 1-л например. а id_vista означает что происходит процедура зачисления) для остальных типов просто пропишу такие же, только на удаление или перемещение записи в другие таблицы))
да, в таблице order пока нет ключевого поля с identity, но как я поняла его необходимо будет сделать, ну а получить я уже знаю как)) благодаря Вам)))
а насчет транзакций - для меня это пока темный лес совсем)) да и времени нет на реализацию совсем - диплом скоро защищать нужно... но в будущем надеюсь разберусь!! еще раз спасибо за помощь!!!
23 май 11, 13:09    [10693887]     Ответить | Цитировать Сообщить модератору
 Re:как добавить ключевые поля identity в таблицу с помощью SP если их значения не вводятся  [new]
ksenya.urevna
Member

Откуда: vorkuta
Сообщений: 12
сначала показалось что все написано правильно и получится но начала делать и в ступор)) выдает ошибки))
пишу так
USE [uchet_stud]
GO
/****** Object:  StoredProcedure [dbo].[zachislenie_sush]    Script Date: 05/26/2011 11:52:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[zachislenie_sush]
	-- Add the parameters for the stored procedure here
	@id_order nchar(10),
	@date_order date,
	@group nchar(30),
	@reason nchar(30),
	@surname nchar(30),
	@name nchar(15),
	@middle_name nchar(30),
	@id_vista int=1
	
	
AS BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	declare @id_stud int
	declare @id_zapisi int
	
	insert [uchet_stud].[dbo].[order] (id_order, date_order, id_vista)
	==>values (@id_order, @date_order, @id_vista)
	set @id_zapisi=SCOPE_IDENTITY()
	
	insert [uchet_stud].[dbo].[student] (surname, name, middle_name)
	values (@surname , @name, @middle_name)
	set @id_stud=SCOPE_IDENTITY()
	
	insert [uchet_stud].[dbo].[movement] (id_stud , id_group , id_zapisi , id_reason )
	values (@id_stud, @id_zapisi, 
	(select id_group from [uchet_stud].[dbo].[group] where name_group = @group),
	(select  id_reason from [uchet_stud].[dbo].[reason] where name_reason = @reason))
    -- Insert statements for procedure here
	
END


при запуске выдает ошибку

Сообщение 547, уровень 16, состояние 0, процедура zachislenie_sush, строка 33
Конфликт инструкции INSERT с ограничением FOREIGN KEY "FK_movement_group". Конфликт произошел в базе данных "uchet_stud", таблица "dbo.group", column 'id_group'.
Выполнение данной инструкции было прервано.

со связями все нормально. строка 33 вообще показывает на выделенную


решила проверить. когда делаю запрос
select id_group from [uchet_stud].[dbo].[group] where name_group ='пгс08'
работает и выдает номер id от этой группы

сделала простую процедуру:
ALTER PROCEDURE [dbo].[proverka]
	-- Add the parameters for the stored procedure here
	@id_stud int,
	@id_zapisi int,
	@id_reason int,
	@group nchar(30)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	insert [uchet_stud].[dbo].[movement] (id_stud , id_zapisi , id_reason , id_group )
	values (@id_stud , @id_zapisi , @id_reason , 
	(select id_group from [uchet_stud].[dbo].[group] where name_group = id_group ))
END

при вызове процедуры говорит
Сообщение 245, уровень 16, состояние 1, процедура proverka, строка 19
Ошибка преобразования значения nvarchar "пгс08               " в тип данных int.


в чем ошибка в первой большой процедуре? как указать тип данных чтоб он воспринимал? я уже всю голову сломала((( помогите, если кто знает, пожалуйста.....
26 май 11, 12:04    [10711973]     Ответить | Цитировать Сообщить модератору
 Re: как добавить ключевые поля identity в таблицу с помощью SP если их значения не вводятся?  [new]
Glory
Member

Откуда:
Сообщений: 104751
Сообщение 245, уровень 16, состояние 1, процедура proverka, строка 19
Ошибка преобразования значения nvarchar "пгс08 " в тип данных int.

Ну так а что вы ждете от сравнения where name_group = id_group ?
26 май 11, 12:09    [10712023]     Ответить | Цитировать Сообщить модератору
 Re: как добавить ключевые поля identity в таблицу с помощью SP если их значения не вводятся?  [new]
Glory
Member

Откуда:
Сообщений: 104751
ksenya.urevna

в чем ошибка в первой большой процедуре?

Вы пытаетесь добавить в таблицу "dbo.group" запись, которая нарушает "ограничение FOREIGN KEY "FK_movement_group""
И эта команда insert [uchet_stud].[dbo].[movement], судя по "FK_movement_group"
26 май 11, 12:13    [10712055]     Ответить | Цитировать Сообщить модератору
 Re:как добавить ключевые поля identity в таблицу с помощью SP если их значения не вводятся  [new]
ksenya.urevna
Member

Откуда: vorkuta
Сообщений: 12
Glory,

я не сравниваю)) мне вот именно что нужно вытащить id_group из таблицы group по введенному значению name_group. то есть пользователь вводит имя группы, она уже существует, а в другую таблицу мне надо занести ее id. вот в чем сложность. мне написал пример выше, потому что я не знала как это сделать... но и так не получается((
как правильно вытащить id соответствующий введенному значению поля? в моем случае name_group???
26 май 11, 12:29    [10712177]     Ответить | Цитировать Сообщить модератору
 Re: как добавить ключевые поля identity в таблицу с помощью SP если их значения не вводятся?  [new]
Glory
Member

Откуда:
Сообщений: 104751
ksenya.urevna
Glory,

я не сравниваю))

Т.е. кто-то посторонний пришел и вписал в ваш запрос where name_group = id_group ?
26 май 11, 12:30    [10712186]     Ответить | Цитировать Сообщить модератору
 Re:как добавить ключевые поля identity в таблицу с помощью SP если их значения не вводятся  [new]
ksenya.urevna
Member

Откуда: vorkuta
Сообщений: 12
нет) никто посторонний, только мои руки)) косяк в запросе исправила) нашла ошибку, теперь выполняется
там вместо id_group нужно было @group просто... запуталась.
тогда я совсем не понимаю почему в первой процедуре не работает... там разрешено нулевое значение и в саму таблицу group по процедуре я ничего не вставляю... я из нее должна извлечь.. значение идентификатора.. по логике понимаю запрос.. и вроде правильный. может еще что-то нужно указать? схема если что там выше есть.....
26 май 11, 12:43    [10712309]     Ответить | Цитировать Сообщить модератору
 Re:как добавить ключевые поля identity в таблицу с помощью SP если их значения не вводятся  [new]
ksenya.urevna
Member

Откуда: vorkuta
Сообщений: 12
может нужно сначала вытащить а потом заносить уже в базу поэтому он ругается? только как это сделать? там же надо из запроса.... получить? какой конструкцией?
26 май 11, 12:51    [10712365]     Ответить | Цитировать Сообщить модератору
 Re:как добавить ключевые поля identity в таблицу с помощью SP если их значения не вводятся  [new]
ksenya.urevna
Member

Откуда: vorkuta
Сообщений: 12
ура!!! все работает! там ошибка была в занесении данных о группе до того как ее значение нашлось)))!!! нашла!!!
26 май 11, 13:03    [10712445]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить