| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10
Comments:
<0> trigger, on select will that helping *if i want it keep at smallint(2)*
<1> yes, NUMERIC(5) would use 5 bytes.
<2> Ox41464b, not clear what you're asking
<2> nor is it clear why you chose that nick
<0> ill put trigger calculating the -minus value to positive value in on SELECT trigger
<0> so, yes... i *was* AFK ^^
<2> what, to get 0-65555?
<2> or whatever
<0> Yes
<0> to get 0-65535
<2> i'd just write a udf for it then
<1> That's not how signed values work.
<2> i don't think i'd put it in a trigger
<0> so... dont ?
<2> i also don't see why you wouldn't just use int and forget all this
<2> just use int
<2> up to 2 bilion or so
<2> billion
<0> 1. data itself limited to 2 bytes, why should i make my db larger ?
<2> cause it doesn't really matter? you're splitting hairs here
<0> 2. lots of data, generated in (seconds)
<2> you prefer to impose more burden on the db to save two bytes
<2> so
<2> you're just making my point
<1> Fine, store it signed and convert it at the client.
<0> 2bytes x 250k x 5 x (24 x 60)
<0> 250k = member, 5 = sub.category
<2> uh huh
<1> 2 bytes aren't going to make or break that
<0> convert at client... thanks
<0> why should i convert it at sql.side :D
<2> why not just store all data as strings and deal with it in the client
<2> then life would be simple again.
<0> it is
<2> so i see
<0> but not string, binary
<2> nice topic. but i thought they used mysql to store details of the birth of christ on 00/00/0000 00:00:00 GMT
<3> christ wasnt born on gmt
<3> wasnt he east of it?
<3> :)
<4> If I have SProc-A EXEC SProc-B and SProc-B creates a #temp table, should I be able to access that #temp table from SProc-A?
<2> no
<2> dynamic sql like that creates temp objects in a different scope
<4> So there's no way I can have a SProc that populates a #temp table based on p***ed in criteria and have other SProcs EXEC that SProc to get the #temp table? Ugh!
<2> have sproc B return a resultset, then in sproc A you can INSERT..EXEC sprocB
<2> into a temp table
<2> understand?
<4> Yeah, but then all the other SProcs have to create that #temp table. I was trying to centralize as much of the code as possible.
<2> depending on the process, you can have sprocB create a global temp table
<4> Multiple sessions may be creating the #temp table simultaneously. Can't be global.
<4> BTW, should have mentioned up front: SQL Server 7/2000.
<2> as i said, i would create the temp table in A and have invocations of other procs return resulsets so that A can stick them into A's temp table
<2> then you're only creating one temp table per proc set
<4> If I have to go that route, I'll do it. But it's not just SProc-A. There are hundreds of SProcs that need to create and populate this #temp table.
<2> in 2k, i suppose you could use table variables and p*** them around, but i don't think i'd go there
<2> but that temp table is created and ditched after each call of A ?
<4> And does 7 support table variables? I know it doesn't support UDFs.
<2> no
<2> hard to comment, not enough context on what you need to do
<2> or what the procs are all doing to a single temp table
<2> clearly you're not actually there yet, or you wouldn't be asking this
<4> Basically I have several hundred SProcs that all implement particular logic (in many different ways). I want to centralize that logic into one SProc and have all the other SProcs call that SProc to get the subset of records they need to work with. That way the logic can be implemented in one place.
<2> so how many procs will you end up with, one?
<5> Return a dataset instead of using a temp table?
<2> sounds like he has additional processing
<4> No, I'll still have the several hundred, plus the one. The several hundred will call the one to get a subset of records, then further filter that subset based on whatever each SProc is supposed to be doing.
<2> what is invoking the other several hundred procs
<4> Some cl***ic ASP VBScript.
<4> And I have to support 7, so no UDFs. Otherwise this would have been simpler.
<2> i'd still go for main #temp in master proc and whittle away with the other procs
<2> the only other bad option i can think would be to have master proc create a unique id, create a global temp table named that, and include that id in the other proc params, but then you'd have to alter several hundred
<2> but that blows on many levels
<2> so you're saying that currently all these hundreds of procs read from a temp table that already has row data and filters them?
<4> Hmmm... SProc-A (being one of the several hundred) could p*** @@SPID into Sproc-B (being the One) and Sproc-B could create a global ##temp table named with SProc-A's @@SPID....
<4> All theses hundreds of SProcs SELECT directly from a real table and have WHERE clauses that filter the records to only records the user is allowed to see, plus any additional filtering they need to do based on what each SProcs purpose is. The problem is all these SProcs implement "filtering the records to only those the User is allowed to see" differently (human error). I want to centralize that "filter Issues a USer is allowed to see" logic.
<4> I want to change that so all these SProcs call a single SProc to get the list of records the User is allowed to see, then apply any additional filtering they need. Much cleaner and MUCH easier to maintain.
<2> how much logic in each? sounds like a ****ing view would simplify a lot of it
<2> have you considered views ?
<2> not as a complete solution, but as part of it?
<4> I thought so at first too, but a view can't take a parameter (have to p*** the UserID in so it can know which user to filter the records for since each user can see different records).
<2> no, but in your proc you add the userid filter in the where clause of calling the view
<4> The filtering logic is WAY to complex to impelement as a single SELECT. There will be lots of IFs and selecting from other tables to find out what records a User can see.
<2> fine, temp table in A
<2> sounds pretty complex so its probably inappropriate to toss out 'its this easy' solutions
<2> not sure i have much else to add
<4> I was just looking into p***ing A's @@SPID into B so B can create a global temp table named based on A's @@SPID... but CREATE TABLE won't accept a @variable for the table name and I hate to put the CREATE TABLE in a string as dynamic SQL...
<2> yes
<4> If I have a table name in an @variable, how can I create that table?
<2> dynamic sql
<2> you have no choice
<2> and not a big deal really, not like its some complex multi join select or something
<4> That ****s. This is starting to feel too "kludgy".
<2> just creating a table
<2> yes
<2> i think your hundred procs are the problem
<2> how they process data
<6> heh
<2> they read rows from a table and return a filtered resultset?
<4> Ugh! Why do I have to support SQL Server 7?!?! :( If I didn't I could use a UDF and this would be so much easier. If I'm no mistaken, you can do SELECT blah FROM udf_MyFunction(@UserID) WHERE ...
<2> yes
<6> I was gonna say I would have thought 'kudgy' around the 50th sproc, at least
<2> well, syntax is wrong, but yes
<4> Guest3: I agree, unfortunately this is an already existing system and I'm just a contractor paid to help add features to it. :)
<2> adding features....
<6> I mean I would have started creating real tables if multi-sprocs needed them
<2> thats like adding a third nipple to a man
<6> I totally hear that Leep, find myself in that position often
<4> Well, I insisted that they find a way to centralize this logic. They keep adding new Sprocs that implement this filtering logic and it always gets implemented slightly different, and it's a nightmare.
<4> And the rules of this filtering (very complex) only exist in a few coders heads and scattered through the code... No human could be expected to get it right, ever! I've spent the last week digging through all the code and questioning the "few coders" and have finaly got the "rules" documented in a Word document. First time they've EVER been on paper. :|
<6> heh, welcome to the real world
<6> unfortunately that's more the rule than the exception
<6> at least you had the social skills to get the rules, thats important
<5> Yeah, documentation invariably ****s.
<7> yo
<4> Ok, just got off the phone with a friend with is a SQL Server god in my opinion. ;) He agreed that with having to support SQL Server 7 about my only choice is to have each of the hundreds of SProcs CREATE the #temp table and INSERT into into EXEC SProc-B. I don't like it, but it's the least kludgy way I can think of.
<7> he's a moron
<5> Not possible to do the work with a view instead?
<4> Drk`Angel: Unfortunately not. These rules are just way to complex to implement as a single SELECT statement.
<4> By the end of the year they're planning to do a conversion of the ASP product to ASP.NET and at that point they're going to drop support for SQL Server 7. Then I can implement this as a UDF and life will be glorious again. :)
<8> If I have 10 columns and I want to select all but 1, which I want to specify the value. How do I do that?
<8> TSQL
<9> you select them all but one and , 'specified value' AS foobar
<4> SELECT col1, col2, col3, col4, ..., <expression> AS col10 FROM table
<8> I don't want to do that
<8> I have like 70 columns in this table
<9> too bad
<4> You said 10. But still, even with 70, if you don't want all the columns you have to specify the columns individualy.
<5> Make less crappy tables.
<8> I was giving an example
<4> That too. Normalize your tables better. :)
<8> The table is based on a flat file
<8> My question still stands.
<8> If anyone knows how.
<4> So why can't you do SELECT *, <expression> AS colname FROM table? Where colname is something other than one of the table's column names.
<8> Because I'm doing an insert
<8> I want to insert t1 from select * t2
<8> But I want to change one of the columns values
<5> wo0twoo: We already answered your question. Just because you don't like the answer doesn't make it wrong.
<8> Drk`Angel, you haven't answered my question.
<5> No, Leep and upb did.
<8> I knew how to do what you were telling me, but that's not what I wanted. I have a specific question.
<4> You're going to have to specify all the column names. In the time you've talked about it here, you could have done a copy/paste to get all the column names already. :)
<8> Well, I wanted to know if anyone had done this before
<5> If you select everything, you can use *. If not, you have to list them all out.
<5> That's all there is to it.
<8> I've run into this a few times
<8> Drk`Angel, shhhh
<4> Ok, if you don't want to hear it from Drk`Angel, I'll say it. If you select everything, you can use *. If not, you have to list them all out. That's all there is to it.
Return to
#sql
or
Go to some related
logs:
Ron Jeremy + accused + Ginger Lynn
#hardware
LavelFlash
#gentoo
planet engine demo
lindsey b mooched
#politics
#photoshop
#politics
#politics
|
|