UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Using Like And % Wildcard, SQL Server 2012    
 
   
mrpersonality
post May 11 2019, 05:37 PM
Post#1



Posts: 465
Joined: 4-December 02
From: NSW Australia


this stored proc compiles and works
however the LIKE really is no good without the % wildcard as LIKE is returning only exact matches
as soon as i try and use % to return rows i get an syntax error near "%"
can someone see what i am doing wrong
USE [MY_SQL_BE]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[search_Customers] @custid nchar(5) = NULL,
@Custname nvarchar(40) = NULL,
@debug bit = 0 AS
DECLARE @SQL nvarchar(4000)
SELECT @SQL = ' SELECT Row_id, Date_Stamp, CustomerID, CustomerName ' +
' FROM dbo.Customers WHERE 1 = 1 '
IF @custid IS NOT NULL
SELECT @SQL = @SQL + ' AND CustomerID = @custid '
IF @Custname IS NOT NULL
--SELECT @SQL = @SQL + ' AND CustomerName LIKE @Custname' + '%' this doesn't work
SELECT @SQL = @SQL + ' AND CustomerName LIKE @Custname' -- this works but it only returns exact matches
IF @debug = 1 PRINT @SQL

EXEC sp_executesql @SQL , N'@custid nchar(5), @Custname nvarchar(40)',
@custid, @Custname
Go to the top of the page
 
June7
post May 11 2019, 05:55 PM
Post#2



Posts: 597
Joined: 25-January 16



Try ALIKE instead of LIKE. http://codevba.com/msaccess/sql_alike.htm#.XNdUUGaWyCg

This post has been edited by June7: May 11 2019, 06:02 PM

--------------------
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
mrpersonality
post May 11 2019, 06:50 PM
Post#3



Posts: 465
Joined: 4-December 02
From: NSW Australia


Thanks June7 worked a treat
to me its a bit like the batman film- where do they get these toys shrug.gif

[censored],spoke to soon -no it don't like ALIKE
Msg 4145, Level 15, State 1, Line 3
An expression of non-boolean type specified in a context where a condition is expected, near 'ALIKE'.
This post has been edited by mrpersonality: May 11 2019, 06:53 PM
Go to the top of the page
 
June7
post May 11 2019, 08:18 PM
Post#4



Posts: 597
Joined: 25-January 16



Sorry, I don't use SQLServer and so not really familiar with stored procedure.

--------------------
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
mrpersonality
post May 11 2019, 08:56 PM
Post#5



Posts: 465
Joined: 4-December 02
From: NSW Australia


Ok-well anyone who knows SQL Server can maybe help me ?
Go to the top of the page
 
cheekybuddha
post May 12 2019, 03:44 AM
Post#6


UtterAccess VIP
Posts: 11,270
Joined: 6-December 03
From: Telegraph Hill


Hi,

Try:
CODE
-- ...
IF @Custname IS NOT NULL
SELECT @SQL = @SQL + ' AND CustomerName LIKE \'' + @Custname + '%\'' this doesn't work
IF @debug = 1 PRINT @SQL
-- ...


Though I'm struggling to see why you need dynamic SQL at all:
CODE
ALTER PROCEDURE [dbo].[search_Customers]
  @custid nchar(5) = NULL,
  @Custname nvarchar(40) = NULL,
  @debug bit = 0
AS
  SELECT
    Row_id,
    Date_Stamp,
    CustomerID,
    CustomerName
  FROM dbo.Customers
  WHERE (CustomerID = @custid OR @custid IS NULL)
    AND CustomerName LIKE COALESCE(@Custname, '') + '%'
GO


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
mrpersonality
post May 12 2019, 04:23 AM
Post#7



Posts: 465
Joined: 4-December 02
From: NSW Australia


Hi Cheekybudda
i am the first to admit i know little about SQL and stored procs
its hard to separate all the different options presented on the net and in the forums.You need to ask the right question to get the right answer,and you need to know SQL to ask the right question
to sum up what i am trying to accomplish
i have an access fe with several search forms that build an SQL string based on the users selected criteria,then supply the SQL to the query,and the subform uses the query to populate the form.There can be no criteria,to a dozen criteria,things like show me records whose customer name is like,show me records between dates ,show me records entered by a staff member,show me records with all that criteria etc
i am moving the be to SQL server,and have been advised to use a pass through query and a stored proc to do what i was doing in access
ive also been advised to use a temp table then a stored proc on the temp table,to use dynamic SQL,to not use dynamic SQL,to be careful of SQL injection ,to not worry about SQL injection and so on-its all pretty confusing
i don't have the knowledge to know the best approach.I just know i need to pass parameters to SQL server to retrieve the data that matches,so that the processing is done on the server
i am trying to start of with just a couple of parameters,so i can get my head around whats going on,then expand on that

Go to the top of the page
 
AlbertKallal
post May 13 2019, 06:35 PM
Post#8


UtterAccess VIP
Posts: 2,813
Joined: 12-April 07
From: Edmonton, Alberta Canada


A few things:

A nice tip about the SQL and parameters. While we so very often in VBA use string concatenation to build up the SQL, as David’s post shows, in T-SQL, we not only don’t have to do this, but we as a general rule avoid this approach. (Not a big deal – it just a general approach that is not required in t-SQL).

So, as you noted, you have near 1 million choices, and you are new to SQL server. (So, no worries).


However, I going to give you a better tip

There is no need, nor advantage, or requirement to convert your existing goal example into a store proc. That is a lot of work, and you gain near zero advantages. Here.


In fact, if the main form is based on a table (now a linked table), and the sub-form has the link/master settings?

Well, you really don’t have to do anything here.

Access as a general rule does a great job, and will only pull the child records into that form. (Not the whole table – so you can often make zero changes here).

So, I would not go to all this trouble.

If the form (or more often report) is for display, has multiple tables? And you want to filter that report (or form)?

Again, keep you VBA code and everything you have 100% “as is”. Access will only pull the records based on the forms “where” clause.

If the SQL was/is complex? Then convert that SQL to a SQL server side view, link to that view, and AGAIN you existing VBA filter code will ONLY pull the data for that filter.

You really do not need to create store procedures for basic filter of data.

If you have a form bound to a linked SQL table.

Say, it is 400,000 rows.

You then go:

CODE
‘Code here to ask our for invoice number

DoCmd.OpenForm "frmInvoice",,,"InvoiceNum = " & strInvoice


In above, even with the form bound directly to a linked table (to SQL server), then the above ONLY pulls the one record down the network pipe. In other words, no change to the Access FE is required here.

So, in 9 out of 10 cases, you don’t need (or want) a store proc.

And in the other 9 out of 10 times if the query is complex, has multiple joins? Then create a view on SQL side. You find that you current report launch and filters will work un-changed, and you get about the same performance as using a store procedure. (But is WAY less work – on your VBA/client side, no code change is required. Just point that form (or report) data source to the linked view in place of what VERY likely was an access (client side) query.

Also, keep in mind that store-procedures are ALWAYS read only. So, again, they are limited.

Views are read/write, and will become your best choice for 99% of the times were you need or want to improve performance.

However, for an access form (or sub form) linked to a table, and not some complex query? Don’t have to do 1 thing in this case – access will filter and only pull the records requested. The key concept here is if your existing design only pulled the records it asked for, then that setup will work fine with SQL server, and in most cases without any changes to the Access application.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Go to the top of the page
 
mrpersonality
post May 13 2019, 06:57 PM
Post#9



Posts: 465
Joined: 4-December 02
From: NSW Australia


Thanks Albert
----------------------------------------------
"i am moving the be to SQL server,and have been advised to use a pass through query and a stored proc to do what i was doing in access
ive also been advised to use a temp table then a stored proc on the temp table,
------------------------------------------------------------------------------------
I can now add ,no need to do anything to this list below
to use dynamic SQL,
to not use dynamic SQL,
to be careful of SQL injection ,
to not worry about SQL injection
use a view
dont use views

and so on-its all pretty confusing - no make that very confusing
my main search form is unbound-it has many drop down boxes filled with search criteria options that relate to a particular subject
it has text boxes that use Like* to search,date fields etc etc,think of anything and its probably there,and if you cant think of it,your user's will
the sub form has a query as a record source
eg
the SQL string is built
Qstr = Qstr & ") ORDER BY " & GetOrderBy(Nz(Me.MyOrder, 2))

Me.Scheduling_SF.Form.RecordSource = Qstr
Me.Scheduling_SF.Requery

the subform is always read only
it has one cmd button which when clicked opens the record
DoCmd.OpenForm "Job_Scheduling_Changes", acNormal, , "[Job_ID] = " & Me.Ctl2, acFormEdit, acDialog
so,are you saying a stored proc wont help speed up data return across a network ?






Go to the top of the page
 
mrpersonality
post May 14 2019, 08:19 PM
Post#10



Posts: 465
Joined: 4-December 02
From: NSW Australia


This works for the wildcard
@SQL += N' AND Cus.CustomerName LIKE @Cname + ''%'''
Go to the top of the page
 
AlbertKallal
post May 15 2019, 09:50 AM
Post#11


UtterAccess VIP
Posts: 2,813
Joined: 12-April 07
From: Edmonton, Alberta Canada


QUOTE
so, are you saying a stored proc won’t help speed up data return across a network ?


Well, helps over what other approach is the issue.

If that form, or report is based on a single, or simple linked table? No, it will not help, and all that extra work gains you nothng.

If you passing filters (parameters) to that store proc, then yes, it works well. However, a filter against a simple base table works well too (and without changes to your Access application).

And if the query does do some heavy lifting (multiple joins etc.), then use a view. You will find in general you don’t get ANY better performance than just having created a view. But a view is “oh so much” less work on your part.

QUOTE
to use dynamic SQL,
to not use dynamic SQL,


I doubt that anyone suggested this is a good idea, or a good approach.

Can you do so? Sure!

Is it the end of the world and we have to send you out to the salt mines?

No. I have used SQL concentration in T-SQL. Not the end of the world here.

It only a good solid tip. So, the basic takeaway was the example posted in T-SQL showing how it not necessary in most cases to use string SQL in T-SQL. It is a good tip.

It not a “must avoid” issue. The basic tip here is that by advoiding string concatenation in T-SQL, then it less messy, easier to read, and you don’t have to worry about quotes around each value (or no quotes for numbers). The other bonus mention is you get SQL injection protection.

So, again, I can’t imagine that string concat in SQL server has EVER been recommended as a great general approach. However, like any experienced software developer?

Over time, you learn enough when it makes sense to break the rules. When you starting out? Well, you don’t know enough, and you don’t know when you should, or should not break the rules (so all you can do is follow the good rules until such time you gain enough experience as to know when to break the rules).

So, some of this advice as noted has to be taken with a grain of salt.

QUOTE
use a view
Don’t use views


Well, once again? It depends on what you doing, and your skill level. And it not some 0 or 1 (binary) choice here.

Anyone who says to never use views, or never use string contact in t-SQL is being too narrow in scope. There are appropriate use cases for both, and there are appropriate times when you should avoid this.

So, just because someone says to avoid views? Well, that advice likely applied to a particular case, and is not an all-encompassing blanket statement.

And, keep in mind that significant amounts of advice on SQL server is advice from developers who don’t have years of experience with Access.

I happen to have those years of experience on both.

I rare use store procedures with MS-access. They are rare used, and rare worth the cost and effort. But worse, access as a client Can NOT filter data from store procs, so for example it is VERY bad to use a store proc for the data source of a bound combo box. (Access can’t filter these).
(However, if the store proc has a filter parameter, then that is ok).

However, in certain environments, say such as web based? Well, now things change a lot – and t-SQL developers will swear by using store procs.

As I stated, “context” of such advice matters. So, quoting advice that store procedures are really the way to go? Well, ok, but you likely talking to a web developer!

And it depends on your goal too!
My advice is based on this goal and context:
Least amount of changes in Access.
Really good performance when required.
Keep cost down to a “min” when converting a back end from Access to SQL server.

QUOTE
eg
the SQL string is built
Qstr = Qstr & ") ORDER BY " & GetOrderBy(Nz(Me.MyOrder, 2))

Me.Scheduling_SF.Form.RecordSource = Qstr
Me.Scheduling_SF.Requery


Ok, given the above? My approach?

I would look at the SQL and see if the query is based on one main table. I would accept a few left joins as being ok. But we talking a relative simple query here.

If you’re existing SQL is the above?

Then I would change nothing! Link your forms and tables to SQL server, and test the above. If it runs fast like before, then I would not change anything.

If the query runs sluggish?

Then move that query to a view, link to that view, and thus ZERO changes will be required to your VBA code that makes up the filter.

In other words, apply your filter against the view. Access does “fine” job of restricting the records pulled in this case. In near all cases you get the same performance as a store proc, but MUCH less work and effort on your part.

The above means you only create a view, and in place of using the query, you used the name of the view as the same name as the query. You THEN apply your filter against that view. This will only pull the records you ask over the network.

You then have:

Select * from myView <-- this SQL you don’t’ change in VBA

Then add the filter.

Select * from myView” & strMyFilter & strOrderBy.

Etc. etc.

And, as noted, you may well not even need a view.

If the query is hitting a single table? Then don’t change anything at all as to compare to what you have now! So, it depends on how simple, or complex the original SQL query was, and how many tables are involved.

So, yes, learning something new certainly brings out a new level of learning, and frustration. And that frustration is because advice without context to your particular problem is going to be less than ideal advice.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada



Go to the top of the page
 
mrpersonality
post May 15 2019, 04:24 PM
Post#12



Posts: 465
Joined: 4-December 02
From: NSW Australia


Thanks Albert
if nothing else I sure know more now than I did a week ago.The different advice I got at least made me dig around,read,interpret and understand things better
I take your point about being an access developer-there are some SQL specific forums that once you point out this is an access FE,they either say that's a problem,move into some.NEt language etc etc,and their answers to questions become a binary answer
Anyway-just about all the search forms I use involve multiple joins and they are sluggish on the network as is,and its only the search forms I intend to try and improve.
99% of the application works just fine
I can now write a stored proc,pass it parameters,use both static and dynamic SQL, and prevent SQL injection,daunting at first I have to say
A view is pretty simple,basically the same as access,so that should not present to many problems
Armed with this knowledge I can tackle the task with a degree of confidence,look for a better solution if performance is a problem
at least I can now test each scenario
.i will pick what I consider to be the most complex search form
start with doing nothing
move to a view
move to a stored proc
and observe them on the actual network
Go to the top of the page
 
AlbertKallal
post May 15 2019, 11:02 PM
Post#13


UtterAccess VIP
Posts: 2,813
Joined: 12-April 07
From: Edmonton, Alberta Canada


QUOTE
99% of the application works just fine


Excellent! – You are experiencing what is expected.

And basic forms can be bound to the linked table, and Access does a rather fine job of respecting the “where” clause – it does play nice with SQL server.

And your comments about the “messy” SQL not so great? Once again, 100% on the money.

QUOTE
move to a view
move to a stored proc
and observe them on the actual network


Could not have said the above better myself. You get a prize for the above. dance.gif

My bets are you stop after moving to a view!

Now, a view is a bit of extra work, but it is tons less than the store procedure. What is also rather nice here is that existing VBA filter code you “build up” in general does not have to be changed. This includes dates etc. – they should work as you have now.

And, only use datetime2 formats SQL side if you willing (have to) install the native 11 or later drivers on each workstation. If you want to avoid having to install native ODBC drivers, then use the legacy “SQL driver”, but that driver does not support datetime2 columns from SQL server – so this is a choice of which is the lessor evil (install native odbc 11 or later on each workstation, or simply avoid datetime2 on SQL server side).


Good luck! May the winds of SQL server be in your sails.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th June 2019 - 07:46 PM