游标引起的疑问 [英] A doubt releated with cursor

问题描述

大家好

我对游标有疑问

我的程序是这样的

Hi all

I have a doubt regarding with cursor

my procedure is like this

set @fl1=1
set @fl2=41 
set @SB_Common1= 'where HR_EMP_REPORTING.BranchID =1'  
set @Month=5
set @Year=2011
set @mode='Salary'      
SET XACT_ABORT ON
BEGIN TRAN


IF @mode='Salary'
BEGIN
 
       DECLARE CUR_SALARY CURSOR FOR 
	Select HR_EMP_MASTER.Emp_Id,HR_EMP_MASTER.Emp_Code,HR_EMP_MASTER.First_Name from HR_EMP_MASTER INNER JOIN HR_EMP_REPORTING on HR_EMP_MASTER.Emp_Id=HR_EMP_REPORTING.emp_id <big>+ @SB_Common1</big>
    
		OPEN CUR_SALARY
		FETCH NEXT FROM CUR_SALARY INTO  @Emp_Id,@EmpCode,@EmployeeName

我的问题发生在我将"where"条件添加到选择语句中时.我想根据前端发生的选择添加where条件.但是当我添加如下所示的stsement错误时..

My problem happens when i add "where" condition to select statement .i want to add where condition according to the selection happening in front end.but when i adding where stsement error like below happening..

Msg 245, Level 16, State 1, Line 47
Conversion failed when converting the varchar value ''where HR_EMP_REPORTING.BranchID =1'' to data type int.

但是我想要根据选择的数据集..怎么做

在Advance中致谢

But i want dataset according to the selection ..how to do it

Thanks in Advance

推荐答案

我认为您的整个where子句都将作为参数发送.
为什么不只发送分支ID作为参数?

I think your entire where clause is being sent as a parameter.
Why dont you just send the branch id as the parameter?

如果您分析该行
on HR_EMP_MASTER.Emp_Id=HR_EMP_REPORTING.emp_id <big>+ @SB_Common1</big>

HR_EMP_REPORTING.emp_id <big></big>是INTEGER并且@SB_Common1是VARCHAR，这会创建异常

最好的解决方案是曼弗雷德(Manfred)所说的，仅将HR_EMP_REPORTING.BranchID的值作为参数传递.
否则，您可以尝试将HR_EMP_MASTER.Emp_Id=HR_EMP_REPORTING.emp_id 解析为VARCHAR ，然后使用+ Operator


在T SQL中使用CAST/CONVERT运算符


CAST和CONVERT(Transact-SQL)

If you analyze the line
on HR_EMP_MASTER.Emp_Id=HR_EMP_REPORTING.emp_id <big>+ @SB_Common1</big>

HR_EMP_REPORTING.emp_id <big></big> is INTEGER and @SB_Common1 is VARCHAR which creates the exception

The best solution is as Manfred said pass only value of HR_EMP_REPORTING.BranchID as parameters.
else you can try parsing HR_EMP_MASTER.Emp_Id=HR_EMP_REPORTING.emp_id as VARCHAR and then use + Operator


Use CAST / CONVERT Operators in T SQL


CAST and CONVERT (Transact-SQL)

这篇关于游标引起的疑问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！