2

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.

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)
  • 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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