Pass Parameter technique

Question

Post reply

Pass Parameter technique

dtopicdragovic

Old Hand

Points: 335
More actions
November 22, 2010 at 10:37 am

#228916

Hi,

I have attached a sample of user interface that I found interesting, and I am trying to figure out how this could be achieved.

Basically user create his search and then submit. When submitted there is a search history build in the upper space, so that user can combine his searches with AND or OR afterwards.

Does anyone have an idea how this works? How we send parameters to stored procedure? Where do we keep this search history?

Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Generally, applications for this kind of thing use dynamic SQL of one sort or another. Because of this, it's relatively easy to store the Where clauses of the searches, and to concatenate them together with either AND or OR in between.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

dtopicdragovic Old Hand Points: 335 More actions · Answer 2

Thanks for input.

I have read a lot about dynamic SQL, but I still have not found a single example that would do something similar to this. In most cases the number of parameters is known. But here we don't know how many parameters user will choose from the bottom part. Do you think that these parameters are combined into one "Where clause" and sent as such to db or it is build differently?

Any example that would address this case would be really appriciated.

Thanks a lot

GSquared SSC Guru Points: 260824 More actions · Answer 3

You'd use string concatenation.

Say, for example, you have three search fields: First Name, Last Name, Department

If someone fills in Last Name only, you'd have:

"Where LName = 'whatever they typed'"

If they fill in first and last, you'd add " and FName = 'whatever they typed there'" to it, using whatever string building technique is appropriate to your site's coding language.

Often, devs will start the string with "Where 1 = 1 ", so they can simply put "and" at the beginning of each search criterion, without having to worry about whether it's the first one or a subsequent one. Hence, you'd end up with "Where 1 = 1 and LName = 'smith' and FName = 'john'" or something like that. If you want to save the search, you'd store the columns and values desired in a "saved searches" table.

Might look like:

create table dbo.SavedSearches (



SearchID int not null,

SearchColumn sysname,

SearchValue nvarchar(100),

constraint PK_SavedSearches primary key (SearchID, SearchColumn));

Then you'd have a separate table with SearchID as its primary key, where you'd store things like whose search (UserID or CustomerID or something like that), when the search was done, and so on.

From the table, you could reconstruct the Where clause, and you could combine it with other searches.

That's a rough outline. You'd need to flesh it out considerably to make it fully functional, but this should get you started.

One IMPORTANT warning about all of this. This type of query-building is very prone to SQL Injection attacks. Make very, very sure you proof it up against those. You'll need to do some research on the subject before you even begin to design the system.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

dtopicdragovic Old Hand Points: 335 More actions · Answer 4

This was very helpful!!!

It seems I am at the right track.

What I do up to now is:

I build @whereClause in my front-end application (access 2007) which I send to server with some other parameters that will decide what @DefaultSelect and @FromClause to choose from table that holds all combinations of join tables.

Then in SP I construct something like:

@Sql = 'SELECT ' + @DefaultSelect + ' FROM ' + @FromClause + ' WHERE ' + @WhereClause

My idea was to get somehow this @Sql statement and save into temporary table on user machine, so it would be there while user works on application and he could combine this Sql statement with some other by INTERSECT, UNION, EXCEPT... he would have a choice to save search , and then i thought of sending it to some table on server for future use.

Now I don't know how to get this @Sql from SP to my VBA code.

Do you think this approach is OK?

Or I shouldn't deal with temporary tables on user machine, but just go directly to server tables? My concern is how I would get rid of those searches that user doesn't want to keep?

Thanks a lot.

GSquared SSC Guru Points: 260824 More actions · Answer 5

I haven't worked with that version of Access, and haven't worked with any version since about 2007. Maybe someone else will answers on that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

dtopicdragovic Old Hand Points: 335 More actions · Answer 6

dtopicdragovic

Old Hand

Points: 335

November 25, 2010 at 6:15 am

#1255549

Thanks for all your input.

I will post back once I decide the way I will do it.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

dtopicdragovic (11/25/2010)

Thanks for all your input.

I will post back once I decide the way I will do it.

That would be a really cool thing to do because a lot of people say that but very, very few actually do it. It would be a nice thing to see so please do come back. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions