January 29, 2009 at 3:29 am
Why would you do a select * in your code anyway?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
January 29, 2009 at 3:31 am
It was just for testing
January 29, 2009 at 3:34 am
oh.
Well will get that error because there is no deferred naming cause SQL knows which table you using and notices that the column is not there, if your select included the #table you wouldn't get the error.
Let me do some performance testing on this issue and see if my solution is bad or not?!?!
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
January 29, 2009 at 3:44 am
OK I've tested this on a table of 100000 rows using the first solution with a GO and then my solution
The performance seems to be pretty much the same with regards to CPU and time.
something is telling me that this is messy I just can't tell why and I'm interested if anyone can tell me why it would be bad to invoke Deferred naming to solve this issue?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
January 29, 2009 at 4:15 am
baumgaertner (1/29/2009)
Unfortunately this doesn't work:Here the code:
if (select nDbSubVersion from tblVersion) = 2
begin
alter table tblCustAttrDefValue add strHelpValue varchar(200) null
end
go
if (select nDbSubVersion from tblVersion) = 2
begin
update tblCustAttrDefValue
set strHelpValue = RTrim(LTrim(strValue))
end
go
Then consider dynamic SQL. It's the only way you're going to get a compile check at run-time
if (select nDbSubVersion from tblVersion) = 2
begin
alter table tblCustAttrDefValue add strHelpValue varchar(200) null
end
go
if (select nDbSubVersion from tblVersion) = 2
begin
EXEC('update tblCustAttrDefValue
set strHelpValue = RTrim(LTrim(strValue))')
end
go
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
January 29, 2009 at 4:17 am
Hi Gail,
any advice on why the Deferred naming option is a NO NO 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
January 29, 2009 at 4:36 am
Christopher Stobbs (1/29/2009)
any advice on why the Deferred naming option is a NO NO 🙂
Did I say it was? It's an interesting trick.
I said dynamic SQL's the only way to get a compile check only at run time. With your trick, the compile is done at parse-time, however because one of the objects doesn't exist,missing column errors aren't thrown. It's not just temp tables, a permanent or table variable should have exactly the same effect
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
January 29, 2009 at 4:54 am
oops sorry no you didn't say that.
It seems to be the little person on my shoulder that keeps nagging me about it
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
January 30, 2009 at 1:21 am
I think I make it with dynamical sql.
Last questions:
- Is there no workaround to have two batches in one if-condition?
if (level = 1)
begin
alter table tblCustAttrDefValue add strHelpValue varchar(200) null
go
update tblCustAttrDefValue
set strHelpValue = RTrim(LTrim(strValue))
go
end
- What about the "Business Intelligence Development Studio"?
Has anyone experience with it. Is there a project type, which can help me?
Thanks for your help
January 30, 2009 at 1:38 am
baumgaertner (1/30/2009)
- Is there no workaround to have two batches in one if-condition?
Dynamic SQL, or Christopher's deferred naming trick
if (level = 1)
begin
alter table tblCustAttrDefValue add strHelpValue varchar(200) null
EXEC('update tblCustAttrDefValue
set strHelpValue = RTrim(LTrim(strValue))')
end
You cannot put GOs into an if statement. It's a batch terminating command, meaning that management studio will send the separate batches to SQL separately. Hence nothing, no variables, no control flow statements crosses a batch.
It is not a T-SQL command
- What about the "Business Intelligence Development Studio"?
Has anyone experience with it. Is there a project type, which can help me?
BI development studio is used to develop Integration Services packages, Analysis services cubes and reporting services reports. If you want to do one of those, then yes, it can help you. If you're writing SQL, stick with management studio.
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
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply