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 used
Cast ()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 +.