Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I just want to know how you deal with long scripts and redundant information. Here is an example:

Suppose you have three tables: table1, table2, and sometable.

And you have

SELECT  
 id  = 1
 org = 'company'
 name = 'name'
 addr = 'arr'
 lang = 1      
cnt1 = if col1 = 1 then 1 else 1
 cnt2 = if col2 = 1 then 1 else 1
 cnt3 = if col2 = 1 then 1 else 1
INTO  table1
FROM   sometable



SELECT  
 id  = 2
 org = 'company2'
 name = 'name'
 addr = 'arr'
 lang = 1 
 cnt1 = if col1 = 1 then 1 else 1
 cnt2 = if col2 = 1 then 1 else 1
 cnt3 = if col2 = 1 then 1 else 1
INTO  table2
FROM   sometable

So the difference is that the starting stuff (not the cnt* fields) are changing. But as you can see there is a lot of overlap.

Also if I want to change the logic on the counts (which are common to both), then i have to go through both select statements and change them manually. this is error prone.

I was thinking something along this pseudocode:

   SELECT  
 id  = 1
 org = @variable
 name = @variable
 addr = 'arr'
 lang = 1 

  INCLUDE other columns/logic from somewhere else

INTO  table2
FROM   sometable

that way, if my logic changes for the cnt fields, I can just change it in one spot and it would change everywhere.

I am open to any suggestions. SMSS addin, stored procs, or some other ugly or beautiful method people know.

share|improve this question

1 Answer 1

If you have something THAT similar and you need to call it more than once or twice, I would put it into another stored proc that you can call:

CREATE PROCEDURE dbo.InsertStuff (@id int, @org varchar(100), @name varchar(100), @lang int)
AS

DECLARE @SQL nvarchar(max)

SET @SQL = 'SELECT id = ' + @id + ' ' +
           'org = ''' + @org + ''' ' +
           'name = ''' + @name + ''' ' +
           'lang = ''' + @lang + ''' ' +
           'cnt 1 = CASE WHEN Col1 = 1 THEN 1 ELSE 2'
           -- All your other common stuff here

EXEC (@SQL)
share|improve this answer
    
Hello, thanks I was thinking that. But I forgot to add that there might be group by or other where clause conditions and obviously you cant say group by @var –  masfenix May 4 '12 at 20:11
    
You can in this example since we are using dynamic SQL. –  JNK May 4 '12 at 20:11
    
for the other groups, maybe you can throw some CASE statements there. –  MarlonRibunal May 5 '12 at 0:09
1  
EXEC? For shame! :-) –  Aaron Bertrand May 5 '12 at 1:13
    
@AaronBertrand I thought about it before posting but decided this was just an example and not production code :) –  JNK May 5 '12 at 1:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.