How to use LIKE in Stored procedure with single quotes

I'm trying to build a dynamic sql statement in a stored procedurre and can't get the like statement to work. My problem is with the single quote. for example LIKE '%@.searchvalue%'. This works fine: select * from view_searchprinters where serialnumber like '%012%' But I can't figure out how to create this in a stored procedure. What syntax should I use for the line in bold?

SET NOCOUNT ON;
DECLARE @.sn varchar(50)
SET @.sn = N'012'

DECLARE @.sql nvarchar(4000)
SELECT @.sql = 'select top 10 * from view_searchprinters'
+ ' WHERE 1 = 1 '
SELECT @.sql = @.sql + ' '
IF @.sn IS NOT NULL
SELECT @.sql = @.sql + 'and serialnumber LIKE ''' + '%' + '@.sn' + '%' + ''' '

EXEC sp_executesql @.sql, N'@.sn varchar(50)', @.sn

Just replace each single quote with two single quotes. But because it's dynamic sql, you'll need to double those as well, I think. You want the dynamic sql to have@.snreplaced withReplace(@.sn, '''', '''). So I think you'll need to replace'@.n'withReplace(@.sn, ''', '')in your SELECT statement.

You may want to consider multiple statements in your dynamic sql so that it produces: DECLARE @.value varchar(50); SELECT @.v = Replace(@.sn, '''', '''); SELECT @.sql = @.sql + 'and serialnumber LIKE ''' + '%' +'@.v' + '%' + ''' '. That might or might not make it easier to visualize.

HTH.

|||

I've tried replacing each single quote with two single quotes and it will not work. I always get a empty result set which means it isn't getting interpreted correctly.

This will not work:

IF

@.snISNOTNULL

SELECT @.sql= @.sql+'and serialnumber LIKE '''+'%'+'@.sn'+'%'+''' '

This will work for putting the wild card at the end:

IF

@.snISNOTNULLSELECT @.sql= @.sql+'and serialnumber LIKE @.sn + ''%'''

I'm not sure why it works as it doesn't have a single quote to end th text before the @.sn. And I can't find an example anywhere with wild card on both ends in a find.

Not sure what you were trying to accomplish with the replace. I tried adding it an it didn't help.

|||

Is your problem that you can't get your dynamic sql statement to execute for a string such as 012 or is it only when the serial number string contains single apostrophes? The replace was to handle the latter issue. If the problem is the former, the easiest thing to do is not to execute the dynamic sql. Just do a select on @.sql instead of execute and look at what is being generated. If you can't spot the syntax problem, paste it into a query window and try executing it to see what error you get.

If that still doesn't solve it, reply with the generated @.sql string so I can look at that.

|||

I came up with an easy solution. If you modify the parameter value prior to using it by adding '%' to the front and end of the parameter, then you can use it as is without trying to insert the wild cards into the text. For example:

DECLARE @.snvarchar(50)SET @.sn = N'012'DECLARE @.sqlnvarchar(4000)SELECT @.sql ='select * from view_searchprinters'+' WHERE 1 = 1 'IF @.snISNOT NULLSET @.sn = N'%' + @.sn + N'%'SELECT @.sql = @.sql +'and serialnumber LIKE @.sn 'EXECsp_executesql @.sql, N'@.sn varchar(50)', @.sn

|||

Actually, wouldn't it be easier to just do the following:

DECLARE @.snvarchar(50)SET @.sn = N'012'SELECT *FROM view_searchprintersWHERE serialnumberLIKE'%' +IsNull(@.sn,'') +'%'

If you're worried about performance when @.sn is null [WHERE serialnumber is like '%%'], then do the following:

DECLARE @.snvarchar(50)SET @.sn = N'012'IF @.snISNULLSELECT *FROM view_searchprintersELSE SELECT *FROM view_searchprintersWHERE serialnumberLIKE'%' +IsNull(@.sn,'') +'%'

Is there a particular need for the dynamic sql?

|||

I'm working on a search app where the user can enter part of a value in any of text boxes or drop down list and the where statement is created based on those values. I had this working in regular ADO.NET in a class but I'm converting my code so that the class uses stored procedures with passed parameters rather than passing the select statement. This means I have to create the where statement in the stored procedure rather than in my vb.net class. I need to build a long select statement so I keep appending the addition to the where statement with @.sql = @.sql + ... This is where I ran into problems. AddingLIKE'%' + to the text wouldn't work due to the single quote issue. My solution avoids it rather than solves it. I'll post my entire code as I've found no good examples anywhere on how to do something like this.

ALTER PROCEDURE [dbo].[SearchPrinters]-- Add the parameters for the stored procedure here@.SerialNumbervarchar(50), @.PrinterNamevarchar(50),@.Statusvarchar(50),@.PONumbervarchar(50),@.OrderNumbervarchar(50),@.ManufacturerIDvarchar(50),@.Modelvarchar(50),@.MaintProvvarchar(50),@.IPAddressvarchar(50),@.InvoiceNovarchar(50),@.Locationvarchar(50),@.Departmentvarchar(50),@.Regionvarchar(50)ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;-- Insert statements for procedure here -- If All Nulls passed return everythingIF (@.SerialNumberISNULL)AND (@.PrinterNameISNULL)AND (@.StatusISNULL)AND (@.PONumberISNULL)AND (@.OrderNumberISNULL)AND (@.ManufacturerIDISNULL)AND (@.ModelISNULL)AND (@.MaintProvISNULL)AND (@.IPAddressISNULL)AND (@.InvoiceNoISNULL)AND (@.LocationISNULL)AND (@.DepartmentISNULL)AND (@.RegionISNULL)BEGINSELECT [SerialNumber] ,[Model] ,[PrinterManufact] ,[ManufacturerID] ,[Status] ,[PrinterName] ,[Department] ,[Location] ,[Region] ,[StatusCode] ,[MaintProvID] ,[IPAddress] ,[NetworkDropNo] ,[OrderNumber] ,[InvoiceNumber] ,[PONumber]FROM [HWinvDB].[dbo].[View_SearchPrinters]ENDELSEBEGINDECLARE @.sqlnvarchar(4000)SELECT @.sql ='SELECT [SerialNumber] ,[Model] ,[PrinterManufact] ,[ManufacturerID] ,[Status] ,[PrinterName] ,[Department] ,[Location] ,[Region] ,[StatusCode] ,[MaintProvID] ,[IPAddress] ,[NetworkDropNo] ,[OrderNumber] ,[InvoiceNumber] ,[PONumber]FROM [HWinvDB].[dbo].[View_SearchPrinters]'+'WHERE 1 = 1'--Start building where statement based on passed parametersIF @.SerialNumberISNOT NULLBEGINSET @.SerialNumber = N'%' + @.SerialNumber + N'%'SELECT @.sql = @.sql +'AND [SerialNumber] LIKE @.SerialNumber 'ENDIF @.PrinterNameISNOT NULLBEGINSET @.PrinterName = N'%' + @.PrinterName + N'%'SELECT @.sql = @.sql +'AND [PrinterName] LIKE @.PrinterName 'ENDIF @.StatusISNOT NULLBEGINSET @.Status = N'%' + @.Status + N'%'SELECT @.sql = @.sql +'AND [StatusCode] LIKE @.Status 'ENDIF @.PONumberISNOT NULLBEGINSET @.PONumber = N'%' + @.PONumber + N'%'SELECT @.sql = @.sql +'AND [PONumber] LIKE @.PONumber 'ENDIF @.OrderNumberISNOT NULLBEGINSET @.OrderNumber = N'%' + @.OrderNumber + N'%'SELECT @.sql = @.sql +'AND [OrderNumber] LIKE @.OrderNumber 'ENDIF @.ManufacturerIDISNOT NULLBEGINSET @.ManufacturerID = N'%' + @.ManufacturerID + N'%'SELECT @.sql = @.sql +'AND [ManufacturerID] LIKE @.ManufacturerID 'ENDIF @.ModelISNOT NULLBEGINSET @.Model = N'%' + @.Model + N'%'SELECT @.sql = @.sql +'AND [Model] LIKE @.Model 'ENDIF @.MaintProvISNOT NULLBEGINSET @.MaintProv = N'%' + @.MaintProv + N'%'SELECT @.sql = @.sql +'AND [MaintProvID] LIKE @.MaintProv 'ENDIF @.IPAddressISNOT NULLBEGINSET @.IPAddress = N'%' + @.IPAddress + N'%'SELECT @.sql = @.sql +'AND [IPAddress] LIKE @.IPAddress 'ENDIF @.InvoiceNoISNOT NULLBEGINSET @.InvoiceNo = N'%' + @.InvoiceNo + N'%'SELECT @.sql = @.sql +'AND [InvoiceNumber] LIKE @.InvoiceNo 'ENDIF @.LocationISNOT NULLBEGINSET @.Location = N'%' + @.Location + N'%'SELECT @.sql = @.sql +'AND [Location] LIKE @.Location 'ENDIF @.DepartmentISNOT NULLBEGINSET @.Department = N'%' + @.Department + N'%'SELECT @.sql = @.sql +'AND [Department] LIKE @.Department 'ENDIF @.RegionISNOT NULLBEGINSET @.Region = N'%' + @.Region + N'%'SELECT @.sql = @.sql +'AND [Region] LIKE @.Region 'ENDEXECsp_executesql @.sql, N'@.SerialNumber varchar(50),@.PrinterName varchar(50),@.Status varchar(50),@.PONumber varchar(50),@.OrderNumber varchar(50), @.ManufacturerID varchar(50), @.Model varchar(50),@.MaintProv varchar(50), @.IPAddress varchar(50), @.InvoiceNo varchar(50), @.Location varchar(50),@.Department varchar(50), @.Region varchar(50)', @.SerialNumber, @.PrinterName, @.Status, @.PONumber, @.OrderNumber, @.ManufacturerID, @.Model,@.MaintProv, @.IPAddress, @.InvoiceNo, @.Location, @.Department, @.RegionENDEND

NOTE: I made sure that I passed DBNull.Value to the stored procedure rather than a string.empty:

Public Function searchPrinters(ByVal serialNoAs String, ByVal printerNameAs String, ByVal statusAs String, ByVal poNumberAs String, ByVal orderNumberAs String, ByVal manufacturerIDAs String, ByVal modelAs String, ByVal maintProvAs String, ByVal ipAddressAs String, ByVal invoiceNoAs String, ByVal locationAs String, ByVal departmentAs String, ByVal regionAs String)As DataSet Dim dsAs New DataSet Dim objConAs New SqlClient.SqlConnection(strSQLCon) Dim objSQLCmdAs New SqlClient.SqlCommand("SearchPrinters", objCon)If region = "All"Then region = String.EmptyEnd If Try objSQLCmd.CommandType = CommandType.StoredProcedure objSQLCmd.Parameters.Add(New SqlClient.SqlParameter("@.SerialNumber", SqlDbType.VarChar, 50)) objSQLCmd.Parameters.Add(New SqlClient.SqlParameter("@.PrinterName", SqlDbType.VarChar, 50)) objSQLCmd.Parameters.Add(New SqlClient.SqlParameter("@.Status", SqlDbType.VarChar, 50)) objSQLCmd.Parameters.Add(New SqlClient.SqlParameter("@.PONumber", SqlDbType.VarChar, 50)) objSQLCmd.Parameters.Add(New SqlClient.SqlParameter("@.OrderNumber", SqlDbType.VarChar, 50)) objSQLCmd.Parameters.Add(New SqlClient.SqlParameter("@.ManufacturerID", SqlDbType.VarChar, 50)) objSQLCmd.Parameters.Add(New SqlClient.SqlParameter("@.Model", SqlDbType.VarChar, 50)) objSQLCmd.Parameters.Add(New SqlClient.SqlParameter("@.MaintProv", SqlDbType.VarChar, 50)) objSQLCmd.Parameters.Add(New SqlClient.SqlParameter("@.IPAddress", SqlDbType.VarChar, 50)) objSQLCmd.Parameters.Add(New SqlClient.SqlParameter("@.InvoiceNo", SqlDbType.VarChar, 50)) objSQLCmd.Parameters.Add(New SqlClient.SqlParameter("@.Location", SqlDbType.VarChar, 50)) objSQLCmd.Parameters.Add(New SqlClient.SqlParameter("@.Department", SqlDbType.VarChar, 50)) objSQLCmd.Parameters.Add(New SqlClient.SqlParameter("@.Region", SqlDbType.VarChar, 50)) objSQLCmd.Parameters("@.SerialNumber").Value = serialNoIf serialNo = String.EmptyThen objSQLCmd.Parameters("@.SerialNumber").Value = DBNull.ValueElse objSQLCmd.Parameters("@.SerialNumber").Value = serialNoEnd If If printerName = String.EmptyThen objSQLCmd.Parameters("@.PrinterName").Value = DBNull.ValueElse objSQLCmd.Parameters("@.PrinterName").Value = printerNameEnd If If status = String.EmptyThen objSQLCmd.Parameters("@.Status").Value = DBNull.ValueElse objSQLCmd.Parameters("@.Status").Value = statusEnd If If poNumber = String.EmptyThen objSQLCmd.Parameters("@.PONumber").Value = DBNull.ValueElse objSQLCmd.Parameters("@.PONumber").Value = poNumberEnd If If orderNumber = String.EmptyThen objSQLCmd.Parameters("@.OrderNumber").Value = DBNull.ValueElse objSQLCmd.Parameters("@.OrderNumber").Value = orderNumberEnd If If manufacturerID = String.EmptyThen objSQLCmd.Parameters("@.ManufacturerID").Value = DBNull.ValueElse objSQLCmd.Parameters("@.ManufacturerID").Value = manufacturerIDEnd If If model = String.EmptyThen objSQLCmd.Parameters("@.Model").Value = DBNull.ValueElse objSQLCmd.Parameters("@.Model").Value = modelEnd If If maintProv = String.EmptyThen objSQLCmd.Parameters("@.MaintProv").Value = DBNull.ValueElse objSQLCmd.Parameters("@.MaintProv").Value = maintProvEnd If If ipAddress = String.EmptyThen objSQLCmd.Parameters("@.IPAddress").Value = DBNull.ValueElse objSQLCmd.Parameters("@.IPAddress").Value = ipAddressEnd If If invoiceNo = String.EmptyThen objSQLCmd.Parameters("@.InvoiceNo").Value = DBNull.ValueElse objSQLCmd.Parameters("@.InvoiceNo").Value = invoiceNoEnd If If location = String.EmptyThen objSQLCmd.Parameters("@.Location").Value = DBNull.ValueElse objSQLCmd.Parameters("@.Location").Value = locationEnd If If department = String.EmptyThen objSQLCmd.Parameters("@.Department").Value = DBNull.ValueElse objSQLCmd.Parameters("@.Department").Value = departmentEnd If If region = String.EmptyThen objSQLCmd.Parameters("@.Region").Value = DBNull.ValueElse objSQLCmd.Parameters("@.Region").Value = regionEnd If Dim objSQLAdptAs New SqlClient.SqlDataAdapter(objSQLCmd) objCon.Open() objSQLAdpt.Fill(ds) objCon.Close()Return ds Catch exAs Exception objCon.Close()Return dsEnd TryEnd Function

And last but not least the code to call the function:

Dim dsAs New DataSetds = objDataAccess.searchPrinters(Me.tbSerialNumber.Text,Me.tbPrinterName.Text,CStr(Me.ddlStatus.SelectedValue),Me.tbPONumber.Text,Me.tbOrderNumber.Text,CStr(Me.ddlManufacturer.SelectedValue),Me.ddlModel.SelectedValue,CStr(Me.ddlMaintProv.SelectedValue),Me.tbIPAddress.Text,Me.tbInvoiceNo.Text,Me.ddlLocations.SelectedValue,Me.ddlDepartment.SelectedValue,Me.ddlRegion.SelectedValue)

|||

I would recommend to set all your parameters in stored procedure to have default value of null and modfiy your coller code to just not create parameter for command if value is not used in search criteria.

And what if someone would like to use OR instead of AND?

JPazgier

|||

I would recommend to set all your parameters in stored procedure to have default value of null and modify your caller code to just not create parameter for command if value is not used in search criteria.

And what if someone would like to use OR instead of AND?

JPazgier