December 13, 2007 at 1:55 pm
I have a stored procedure that executes a Select Statement in more then one part of the T-SQL script. Each of these selects return the same list of fields. Is there a way (besides Select * from Table) that I can define the list of fields in the select statement once?
For instance in the stored procedure below I have two select statements that return the same list of fields. Any time the table changes I have to modify each of the select statements. In the example below this type of change is simple. In more complicated procedures adding or removing fields from the multiple select statements can be a real headache. I tried to create one select list using a CTE, but I could not get the CTE to work. The IF statement seemed to mess the CTE up.
Any help would be much appreciated.
Thanks
CREATE PROCEDURE dbo.AP_Terms_GET (@TermCode Int = Null)
AS
BEGIN
-- Select One Record By Primary Key --
If Not(@TermCode Is Null)
Begin
Select TermCode, Description, ChangeDateTime, OperID
From AP_Terms
Where TermCode = @TermCode
Return
End
-- Select All Records Sorted By Primary Key --
Select TermCode, Description, ChangeDateTime, OperID
From AP_Terms
Order By TermCode
END
December 13, 2007 at 4:40 pm
Any time the table changes
How often the table is changed?
If more than 2 times in a lifetime you better rethink you application design.
And you don't need 2 statements for your procedure.
This will do what you need without repeating the code:
[Code]
Select TermCode, Description, ChangeDateTime, OperID
From AP_Terms
Where (@TermCode Is Null OR TermCode = @TermCode )
[/Code]
_____________
Code for TallyGenerator
December 13, 2007 at 9:30 pm
I'd use Sergiy's method, but if you don't want nulls, use
if @x is not null
instead. It's cleaner.
December 14, 2007 at 6:29 am
Unfortunately for me the tables do tend to change more then I would like (this is out of my control).
I agree that I could use Sergiy's method for the stored procedure that I used in my example. The problem is with more complicated stored procedures (I didn't include an example of one of these stored procedures because I thought that the simple procedure would enough to give everyone an idea of what I am trying to accomplish).
Any help on this would be much appreciated?
December 14, 2007 at 6:37 am
The only way to do this that I think of right now is with Dynamic SQL, which I don't recommend.
I'd just build in the time it takes to make the changes into the process. If it's a large number of changes, select the column list (highlight, CTRL-C), then make the change and do a search and replace. That might make things simpler.
December 14, 2007 at 7:29 am
Thanks for the suggestion. That is basically what I am doing.
I had initially thought that using a CTE would work. I tried the following but could not get it to compile. If seems that the IF statement caused a problem. I am not all that familiar with CTE's so I am not sure why this couldn't be done.
With AP_Terms_CTE
As
(Select TermCode, Description, RowVersion, LastChangedDateTime, OperID
From AP_Terms)
-- Select One Record By Primary Key --
If Not(@TermCode Is Null)
Begin
Select * From AP_Terms_CTE Where TermCode = @TermCode
Return
End
Select * From AP_Terms_CTE Order By TermCode
Thanks
December 14, 2007 at 8:40 am
It seems to me that what you need is a single select statement, and put the complexity in the where statement. You'll end up with a complex execution plan, which means the proc will run a bit more slowly, but it will be easier to maintain.
If you need fast execution more than you need easy maintenance, I think you're pretty much stuck with either dynamic SQL or rewriting each select in your proc when the table changes.
- 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
December 14, 2007 at 9:12 am
Thanks. I was hoping for an easier way to make these types of changes, but I guess I am stuck with what I am currently doing or using dynamic sql.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply