Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2006-04-06 : 16:12:09
|
| I usually try to write SQL access using ADO, but once in a while, I need to preserve the filter criteria for later use. SQL strings come to mind...strSQL = "EXEC dbo.MyProc @Parm1=99,@Parm2='text',@Parm3=NULL"This'll store a query handily in a DB for future use.If anyone has a better way, jump in and stop me, but if not...I need to modify a couple of the parameters in this string, so I'm faced with writing a few functions to take the string and: - Add a parameter - Remove a parameter - Replace a parameter value with a new valueAm I heading down a dark trail here? If anyone has already done this, would you mind posting the code to save me an hour or two of hacking away at a solution?Sam |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-07 : 06:58:03
|
| Parameterise it using sp_ExecuteSQL so that the query gets cached? (or have I got the wrong end of the stick?)Kristen |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-04-07 : 07:21:43
|
| You can create an ADO command object and save it in a variable and use it over and over again, just like your strSQL var ... the command can have parameters in a proper, easy to manipulate collection. Why overcomplicate things? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-04-07 : 07:46:29
|
i think what he meant is that he needs to store sql strings in the db.so he can display last 10 searches on the page.Go with the flow & have fun! Else fight the flow  Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"] |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2006-04-07 : 08:21:22
|
quote:
Originally posted by spirit1
i think what he meant is that he needs to store sql strings in the db.so he can display last 10 searches on the page.
That, and I need to "schedule" the query for future execution (it sends email to a filtered set of users.) |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-04-07 : 08:28:15
|
well here's how i'd do it if you haven't already done it  1. substring the paramteres out.2. split on "," and trim the spaces3. split each new array on =4. save entries in a dictionary5. adding a new parameter is simple add to dict.6. build a new string.7. saveGo with the flow & have fun! Else fight the flow Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"] |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-04-07 : 08:34:00
|
| If you need to store it in the DB, then why not normalize it? 1 table with the SQL statement (or the stored procedure), a related 1-many table with the parameters. A simple concatenation builds your string before executing it, and it is much easier to manipulate your params that way. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-07 : 12:29:18
|
| Or store it as something like:EXEC dbo.MyProc @Parm1={Parm1},@Parm2={Parm2},@Parm3={Parm3}and parse the {STUFF}, locate any valid [current] parameter values, and then use REPLACE to substitute them into the query.Or [probably better still] just "pile the parameters on the end" of a call to sp_ExecuteSQL.Then you could just have a table of Name/Value pairs for the parameters:Header Table:ReportID (e.g. NEW_CUSTOMER)SQL Query (e.g. "SELECT * FROM CUSTOMERS WHERE CreateDate >= @MyCreateDate")Parameter Table:ID (e.g. NEW_CUSTOMER)Parameter Name (e.g. MyCreateDate)DataType (e.g. DateTime)Value (e.g. "20060407")and you generate:EXEC sp_ExecuteSQL 'SELECT * FROM CUSTOMERS WHERE CreateDate >= @MyCreateDate','@MyCreateDate datetime', @MyCreateDate='20060407'(Syntax not checked or carefully thought through I'm afraid!)Kristen |
|
|
|
|
|
|
|
|
Manipulating SQL Strings in ASP