编写SQL存储过程时出现问题 [英] Problem in writing SQL stored procedure

问题描述

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'

< b>我尝试了什么：



尝试追加N即SET @ strSql = N'Declare

但没有work

What I have tried:

Tried Appending N i.e. SET @strSql=N'Declare
but didn't work

推荐答案

SET @strSql = @strSql +'AND TesterName ='+ @ TesterName



应该是


SET @strSql = @strSql +'和TesterName ='''+ @ TesterName +''''

SET @strSql = @strSql +' AND TesterName='+@TesterName

should be

SET @strSql = @strSql +' AND TesterName='''+@TesterName + ''''

这篇关于编写SQL存储过程时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！