December 19, 2012 at 2:55 pm
pdanes (12/19/2012)
David Webb-200187 (12/19/2012)
You could send a fixed terminating character on the end of your variable parameters and check to see if it is actually the last character in the parameter. If all is well, just replace() it with '' and proceed.Or you could check the datalength() of the parameter and if it is exactly the max length of the variable, that could send up a red flag (given that the variable should be oversized to start with).
Or pass in the parameter length as a separate parameter.
All of these will slow your proc, of course, or cause some additional work on the front end.
I've thought of some of those, but not the one about checking for an exact length as a warning. That might be the simplest to check in code.
But I just though of another way to pass parameters. What about a separate, one-record table? That could be defined in SQL Server and made read-write accessible to the application. The application could load values into strongly typed fields, it WOULD get warnings about length overruns and the procedure could pass values back the same way (maybe using another such table, to keep the confusion down). It sort of feels like a global variable, which is often discouraged in procedural programming, but even there it can be a very useful tool, if used carefully. The table could even incorporate business logic, to keep the application from loading parameters that may be legitimate separately, but nonsense in certain combinations. I don't recall ever seeing that suggested anywhere, but this must have occurred to other people.
Seems like a lot of work versus writing a check into the application; i.e. javascript or even a text box that only allows x amount of characters.
Jared
CE - Microsoft
December 19, 2012 at 3:21 pm
GilaMonster (12/19/2012)
pdanes (12/19/2012)
But I just though of another way to pass parameters. What about a separate, one-record table?Multiple users.
Oh, yeah. Oops. You could use multiple rows and put an ID with each record, but it sounds like a good way to make a huge mess. That's not the direction I'm trying for.
But what about such a table as a parameter? No business logic, but at least the fields would be strongly typed. I don't know if I would get a warning trying to load too much into a field in a table variable, as opposed to a just a single variable - I'll have to try that out. If it works, that might be a good compromise.
Been there, done that, not quite as simple as it sounds.
It rarely is, but at least these forums are great for learning from other people's mistakes.
December 19, 2012 at 3:25 pm
pdanes (12/19/2012)
But what about such a table as a parameter? No business logic, but at least the fields would be strongly typed. I don't know if I would get a warning trying to load too much into a field in a table variable, as opposed to a just a single variable - I'll have to try that out. If it works, that might be a good compromise.
That's what I suggested earlier. Table-valued parameter.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 19, 2012 at 3:41 pm
GilaMonster (12/19/2012)
pdanes (12/19/2012)
But what about such a table as a parameter? No business logic, but at least the fields would be strongly typed. I don't know if I would get a warning trying to load too much into a field in a table variable, as opposed to a just a single variable - I'll have to try that out. If it works, that might be a good compromise.That's what I suggested earlier. Table-valued parameter.
Oh, okay. I thought you meant that long string I wrote to Grant - pass that as a table instead of a string (which is also probably a good idea). I've never passed tables to a procedure, just gotten them back, so I'll have to play with the syntax a bit. Access uses two different types of recordsets - I don't know if they're both compatible with this method of transmission, so I have some experimenting ahead of me. Thanks for the tips.
December 20, 2012 at 3:05 am
pdanes (12/19/2012)
GilaMonster (12/19/2012)
pdanes (12/19/2012)
But what about such a table as a parameter? No business logic, but at least the fields would be strongly typed. I don't know if I would get a warning trying to load too much into a field in a table variable, as opposed to a just a single variable - I'll have to try that out. If it works, that might be a good compromise.That's what I suggested earlier. Table-valued parameter.
Oh, okay. I thought you meant that long string I wrote to Grant - pass that as a table instead of a string (which is also probably a good idea).
Err, yes. Split the long string into rows and pass that as a table-valued parameter.
No idea though if Access can do it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2012 at 5:01 am
GilaMonster (12/20/2012)
pdanes (12/19/2012)
GilaMonster (12/19/2012)
pdanes (12/19/2012)
But what about such a table as a parameter? No business logic, but at least the fields would be strongly typed. I don't know if I would get a warning trying to load too much into a field in a table variable, as opposed to a just a single variable - I'll have to try that out. If it works, that might be a good compromise.That's what I suggested earlier. Table-valued parameter.
Oh, okay. I thought you meant that long string I wrote to Grant - pass that as a table instead of a string (which is also probably a good idea).
Err, yes. Split the long string into rows and pass that as a table-valued parameter.
No idea though if Access can do it.
Probably - I'll try it and post back.
But maybe I misunderstood again. It looks like there are two methods under discussion here:
1. 'Normal' tables, like the string I just mentioned, passed as a table containg (in this case) a single varchar(2) field with multiple similar records.
2. A 'parameter' table, containing a single record, numerous various fields defined as char, varchar, int and so on, instead of multiple parameters. This might catch and warn about string length overruns in the application, instead of blindly truncating them, as it does with individual varchar parameters.
The second is what occurred to me after you pointed out the problem with my idea of a linked parameter table stored on SQL Server. It's a little more convoluted and less intuitive than individual parameters, but maybe safer typing.
December 21, 2012 at 5:06 am
Neither of those is what was suggested.
A parameter table that contains that list split into rows.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2012 at 6:06 am
GilaMonster (12/21/2012)
Neither of those is what was suggested.A parameter table that contains that list split into rows.
That what I said, I think. This string:
'1E,D,E,Es,F,Fs,G,Gs,H,Hs,J,K,Ks,L,Ls,Lc,M,Me,N,Ns,O,Os,Ob,Oc,P,Ps,Pa,Pb,Pc,R,Ra,S,Ss,Sb,Sc,T,Ts,Ta,Tb,Tc,Ua,Ub,Uc,Va,Z,Zs'
Would instead get passed as a table:
KatalogLetter varchar(2)
1E
D
E
Es
F
Fs
G
Gs
H
Hs
J
K
Ks
L
Ls
Lc
M
Me
N
Ns
O
Os
Ob
Oc
P
Ps
Pa
Pb
Pc
R
Ra
S
Ss
Sb
Sc
T
Ts
Ta
Tb
Tc
Ua
Ub
Uc
Va
Z
Zs
That is method 1, suggested by Grant, Jared and (I thought) you. No? If not, what did you mean?
Method 2 is my idea for passing parameters of various types, as a one-record table, with stronger typing than offered by individual parameters, specifically length checking. That occurred to me after you shot down my idea of using an ordinary table, that the application would write to, instead of passing parameters. You then answered "That's what I suggested earlier. Table-valued parameter.", and I thought that you meant exactly this, and that I hadn't understood you the first time around. Did you mean something else altogether? You write "Neither of those is what was suggested." What, then? Sorry if I'm being dense, but sometimes your responses are a little cryptic.
December 21, 2012 at 6:18 am
pdanes (12/21/2012)
GilaMonster (12/21/2012)
Neither of those is what was suggested.A parameter table that contains that list split into rows.
That what I said, I think. This string:
'1E,D,E,Es,F,Fs,G,Gs,H,Hs,J,K,Ks,L,Ls,Lc,M,Me,N,Ns,O,Os,Ob,Oc,P,Ps,Pa,Pb,Pc,R,Ra,S,Ss,Sb,Sc,T,Ts,Ta,Tb,Tc,Ua,Ub,Uc,Va,Z,Zs'
Would instead get passed as a table:
KatalogLetter varchar(2)
1E
D
E
Es
F
Fs
G
Gs
H
Hs
J
K
Ks
L
Ls
Lc
M
Me
N
Ns
O
Os
Ob
Oc
P
Ps
Pa
Pb
Pc
R
Ra
S
Ss
Sb
Sc
T
Ts
Ta
Tb
Tc
Ua
Ub
Uc
Va
Z
Zs
That is method 1, suggested by Grant, Jared and (I thought) you. No? If not, what did you mean?
Method 2 is my idea for passing parameters of various types, as a one-record table, with stronger typing than offered by individual parameters, specifically length checking. That occurred to me after you shot down my idea of using an ordinary table, that the application would write to, instead of passing parameters. You then answered "That's what I suggested earlier. Table-valued parameter.", and I thought that you meant exactly this, and that I hadn't understood you the first time around. Did you mean something else altogether? You write "Neither of those is what was suggested." What, then? Sorry if I'm being dense, but sometimes your responses are a little cryptic.
Is that string a constant? If so, why isn't it loaded into a table in the database already and accessed from there. If something needs to be added, add it to the table.
December 21, 2012 at 6:19 am
pdanes (12/21/2012)
That is method 1, suggested by Grant, Jared and (I thought) you. No? If not, what did you mean?
Neither Grant, Jared or myself suggested a 'normal' table. We suggested a table-valued parameter, not a normal, permanent table.
A 'normal' table is going to cause you all sorts of fun as soon as there's more than one user using the proc (and probably sooner than that)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2012 at 6:39 am
If your input parameter is just a list of character-separated values, then you can make it just VARCHAR(MAX), then use split function to split it into table inside of proc (you can use J.Moden classic: http://www.sqlservercentral.com/articles/Tally+Table/72993/).
That at least 100% will work if SP is called from Access...
December 21, 2012 at 6:55 am
Lynn Pettis (12/21/2012)
pdanes (12/21/2012)
GilaMonster (12/21/2012)
Neither of those is what was suggested.A parameter table that contains that list split into rows.
That what I said, I think. This string:
'1E,D,E,Es,F,Fs,G,Gs,H,Hs,J,K,Ks,L,Ls,Lc,M,Me,N,Ns,O,Os,Ob,Oc,P,Ps,Pa,Pb,Pc,R,Ra,S,Ss,Sb,Sc,T,Ts,Ta,Tb,Tc,Ua,Ub,Uc,Va,Z,Zs'
Would instead get passed as a table:
KatalogLetter varchar(2)
1E
D
...
Va
Z
Zs
That is method 1, suggested by Grant, Jared and (I thought) you. No? If not, what did you mean?
Method 2 is my idea for passing parameters of various types, as a one-record table, with stronger typing than offered by individual parameters, specifically length checking. That occurred to me after you shot down my idea of using an ordinary table, that the application would write to, instead of passing parameters. You then answered "That's what I suggested earlier. Table-valued parameter.", and I thought that you meant exactly this, and that I hadn't understood you the first time around. Did you mean something else altogether? You write "Neither of those is what was suggested." What, then? Sorry if I'm being dense, but sometimes your responses are a little cryptic.
Is that string a constant? If so, why isn't it loaded into a table in the database already and accessed from there. If something needs to be added, add it to the table.
No, it's not a constant. It is the list of catalogs that the user wishes to search. It may contain one or several of those letters/letter pairs. In practice, usually either just one, or a group of several related catalogs. I'm probably the only one who will normally submit the entire list regularly, since I am the only one who regularly deals with database-wide issues. The users generally concentrate on only their own catalogs.
December 21, 2012 at 7:02 am
Eugene Elutin (12/21/2012)
If your input parameter is just a list of character-separated values, then you can make it just VARCHAR(MAX), then use split function to split it into table inside of proc (you can use J.Moden classic: http://www.sqlservercentral.com/articles/Tally+Table/72993/).That at least 100% will work if SP is called from Access...
The DelimitedSplit8K is optimized for strings no longer than 8K, but based on what has been shown I don't see why the parameter for this string oc characters couldn't be declared as varchar(8000).
December 21, 2012 at 7:03 am
GilaMonster (12/21/2012)
pdanes (12/21/2012)
That is method 1, suggested by Grant, Jared and (I thought) you. No? If not, what did you mean?Neither Grant, Jared or myself suggested a 'normal' table. We suggested a table-valued parameter, not a normal, permanent table.
A 'normal' table is going to cause you all sorts of fun as soon as there's more than one user using the proc (and probably sooner than that)
I quote myself, a few posts back "Would instead get passed as a table:" PASSED as a table. Passed means as a parameter, does it not? By normal, I meant a table that contains multiple records with similar values, i.e., my list of catalog letters.
My other thought was a table, again PASSED AS A PARAMETER, that contains ONE record, with various fields fields defined, instead of passing those values as separate parameters. Is THAT a method that's used much? At all? Any reasons to do / not do things that way?
December 21, 2012 at 7:06 am
Lynn Pettis (12/21/2012)
Eugene Elutin (12/21/2012)
If your input parameter is just a list of character-separated values, then you can make it just VARCHAR(MAX), then use split function to split it into table inside of proc (you can use J.Moden classic: http://www.sqlservercentral.com/articles/Tally+Table/72993/).That at least 100% will work if SP is called from Access...
The DelimitedSplit8K is optimized for strings no longer than 8K, but based on what has been shown I don't see why the parameter for this string oc characters couldn't be declared as varchar(8000).
It could, but Gail thinks it's better to let the client do the work and pass it already formatted as a table. It would solve the overrun problem, same as varchar(max), but I've been getting flack about being sloppy by using such large numbers.
Viewing 15 posts - 31 through 45 (of 53 total)
You must be logged in to reply to this topic. Login to reply