Processing of special symbols in Stored Procedures-SQL statements as parameter stored procedures-complex queries

I encountered a very tangled problem when doing a small project. I would like to share with you the hope to help you with the same problem.

Problem: there is a complex query.

In the layer-3 architecture, this is a very tangled problem. In the end, I felt that all the parameters in the interface layer were encapsulated into an object class and three object classes were passed as parameters,

''' <Summary>
''' Combined Query condition entity
''' </Summary>
Public class condition

''' <Summary>
''' Field name
''' </Summary>
Private m_filename as string
''' <Summary>
''' Operator
''' </Summary>
Private m_operate as string
''' <Summary>
''' Field Value
''' </Summary>
Private m_filevalue as string
''' <Summary>
''' Link character
''' </Summary>
Private m_relation as string

''' <Summary>
''' Field name
''' </Summary>
Public property filename () as string
Get
Return m_filename
End get
Set (byval value as string)
M_filename = Value
End set
End Property

''' <Summary>
''' Operator
''' </Summary>
Public property operate () as string
Get
Return m_operate
End get
Set (byval value as string)
M_operate = Value
End set
End Property

''' <Summary>
''' Link character
''' </Summary>
Public property relation () as string
Get
Return m_relation
End get
Set (byval value as string)
M_relation = Value
End set
End Property

''' <Summary>
''' Field Value
''' </Summary>
Public property filevalue () as string
Get
Return m_filevalue
End get
Set (byval value as string)
M_filevalue = Value
End set
End Property

End Class 'condition

Next, there was another problem. I wanted to write SQL statement queries as a stored procedure, which was a headache because of the single quotation marks and spaces. Here I will share the final solution.

Alter procedure [DBO]. [proc_condition_query]
@ Filename1 varchar (10), @ operator1 varchar (1), @ filevalue1 varchar (10), @ relation1 varchar (10 ),
@ Filename2 varchar (10), @ operator2 varchar (1), @ filevalue2 varchar (10), @ relation2 varchar (10 ),
@ Filename3 varchar (10), @ operator3 varchar (1), @ filevalue3 varchar (10), @ infoname varchar (10) Table Name
As
Declare @ tempsql varchar (500) -- temporarily store SQL statements
Begin
Set @ tempsql = 'select * from' + @ infoname + 'where' + @ filename1 + @ operator1 + char (39) + @ filevalue2 + char (39) -- concatenate an SQL string, use assci value for single quotes
If (@ relation1 is not null)
Begin
If (@ relation2 is null)
Begin
Set @ tempsql = @ tempsql + space (1) + @ relation1 + space (1) + @ filename2 + space (1) + @ operator2 + space (1) + char (39) + @ filevalue2 + char (39) -- space (1) is used for spaces on both sides of the operator)
Solution

End
Else
Begin
Set @ tempsql = @ tempsql + space (1) + @ relation1 + space (1) + @ filename2 + space (1) + @ operator2 + space (1) + char (39) + @ filevalue2 + char (39) + space (1) + @ relation2 + space (1) + @ filename3 + space (1) + @ operator3 + space (1) + char (39) +
@ Filevalue3 + char (39)

End
End
Exec (@ tempsql)
End

The problem is solved, and the result is finally displayed.

 

Summary: The following problems may occur when no single quotation marks are added.