Petelai project experience small set (2) ___ Combined Query stored procedure, error "failed to convert varchar JBID ='' to data type int ",
The storage process for writing a composite query encountered the following problem:Failed to convert varchar value 'select * FROM View_DLS_WXJD_Customer WHERE 1 = 1 and JBID = ''to data type int. Error details:Baidu: string variables and Integer Variables cannot be connected with +. So I usedCast ()The function converts the integer variable DLSJB to a string, which solves the problem. The correct code is as follows:
Alter procedure [dbo]. [Proc_SH_WXJDList] @ DH varchar (50), -- Ticket No. @ DLSJB int, -- Agent level ID @ DLSName varchar (100) -- Agent Name ASBEGINDeclare @ sqlStr varchar (800) -- store the SQL statement Set @ sqlStr = 'select * FROM View_DLS_WXJD_Customer WHERE 1 = 1' -- Synthesize the SQL statement. if no conditions are input, SELECT all if (@ DH! = '') -- Determines whether the write order number is set @ sqlStr = @ sqlStr + 'and DH like'' %' + @ DH + '% ''' if (@ DLSJB! =-1) -- determine whether to select the agent Level set @ sqlStr = @ sqlStr + 'and JBID =' + ''' + cast (@ DLSJB as varchar (5 )) + ''' if (@ DLSName! = '') -- Determine whether to select the agent name set @ sqlStr = @ sqlStr + 'and DLSName like' % '+ @ DLSName +' % ''EXEC (@ sqlStr + 'order BY DH DESC ') END
Cast () is a conversion function. Like the Convert function we use in asp.net, cast () is a data type converted to another data type. This article uses the cast () function to forcibly convert an integer variable to a string type, so that the string type can be connected using +.