kedar001 Ответов: 1

Проблема при написании хранимой процедуры SQL


CREATE PROCEDURE [dbo].[sp_empInfo]
@empID int =null, 
@TesterName varchar(250)=null
as 
declare @strSql nvarchar(4000)

			SET	@strSql='Declare  users_cursor CURSOR FOR SELECT ID,TesterName FROM emp Where Status=1 '
				IF @empID IS NOT NULL AND LEN(@empID) > 0
					SET	@strSql = @strSql +' AND ID='+CAST(@empID AS NVARCHAR(10))
				IF @TesterName IS NOT NULL AND LEN(@TesterName) > 0
					SET	@strSql = @strSql +' AND TesterName='+@TesterName
		exec sp_executesql @strSql
   		open   users_cursor
				fetch next from users_cursor into @empID, @TesterName
						while @@FETCH_STATUS = 0 BEGIN
						---PROCESS
			    fetch next from users_cursor into @empID, @TesterName
	 	END
	 	close users_cursor
		deallocate users_cursor  


when i pass TesterName as Nil I'm getting Error 
Invalid column name 'Nil'


Что я уже пробовал:

Попробовал добавить N, т. е. SET @strSql=N'Declare
но не получилось

0x01AA

Может быть, просто не хватает места в ' AND ID=' вместо ' AND ID= ' а также здесь ' AND TesterName='?

kedar001

Нет, попробовал добавить пространство, но ничего не вышло
Установите @strSql = @strSql +' и TesterName= "'+@TesterName+""
это работает, но правильно ли это?

0x01AA

Я вижу. Да, это правильный путь, имея значение @TesterName в одинарных кавычках.

pt1401

Это неправильный путь, потому что он ужасно неэффективен.
Попробуй:-

Если @empID не равен NULL и LEN (@empID) > 0
Выберите ID, TesterName из emp, где Status=1 и ID=@empID
Иначе, если @TesterName не равно NULL и LEN (@TesterName) > 0
Выберите ID, TesterName из emp, где Status=1 и TesterName=@TesterName


Ах да, это не работает, когда вы предоставляете как @empID, так и @ TesterName

0x01AA

И почему вы не пишете об этом в своем ответе?

pt1401

Потому что вопрос был такой: "почему я получаю ошибку недопустимого имени столбца?" :)

0x01AA

Так же и для меня, чтобы решить "почему я ..." это "правильный путь" :)

pt1401

Поэтому я предполагаю, что курсор необходим, и там будет что-то вроде цикла процесса ?
Если бы это было так, я бы написал proc как
Создайте процедуру [dbo].[sp_empInfo]
@empID int =null,
@TesterName varchar(250)=null
АС

Объявить @emp INT
Объявите @tester VARCHAR(250)

Объявите USERS_CURSOR курсор FAST_FORWARD READ_ONLY для
Выберите @emp=ID, @tester=TesterName
От Эми
Где Status=1
И (@empID равно NULL или @empId=" или ID=@empID)
И (@TesterName равно NULL или @TesterName= " или @TesterName=TesterName)

Откройте users_cursor
FETCH NEXT FROM users_cursor INTO @emp, @tester

В ТО ВРЕМЯ КАК @@FETCH_STATUS = 0
НАЧАТЬ
-- ПРОЦЕСС
FETCH NEXT FROM users_cursor INTO @emp, @tester
КОНЕЦ

Закрыть users_cursor
Освободить users_cursor


Если курсор действительно не нужен, он сводится к
Создайте процедуру [dbo].[sp_empInfo]
@empID int =null,
@TesterName varchar(250)=null
АС

Выберите @emp=ID, @tester=TesterName
От Эми
Где Status=1
И (@empID равно NULL или @empId=" или ID=@empID)
И (@TesterName равно NULL или @TesterName= " или @TesterName=TesterName)

Есть место для динамического sql, но его лучше не использовать, если его можно избежать.
Обновление: выберите @emp=ID, @tester=TesterName
Должен быть выбран идентификатор, TesterName

kedar001

хорошо, спасибо за ответ.
но когда я добавляю строку SQL в @strSql, я получаю ошибку как недопустимое имя столбца "Nil".

как установить @strSql = @strSql +' и TesterName= "'+@TesterName+""
неэффективно, как я могу писать его эффективно.



pt1401

Вам не нужен strSql, используйте либо одно из описанных выше определений proc

kedar001

когда @TesterName имеет значение null, я не хочу включать его в запрос
вот почему я использую @strSql

pt1401

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

kedar001

Спасибо
Сделано

1 Ответов

Рейтинг:
2

pt1401

Установите @strSql = @strSql +' и TesterName= ' +@TesterName

должно быть

Установите @strSql = @strSql +' и TesterName="'+@TesterName + ""