0

I try to explain correctly: I have stored procedures that use two tables: myFiles and myBooks. Now I want to create two new tables myFilesProduction and myBooksProduction.

What's the best way to change stored procedures to select the correct tables to use with an argument ?

  1. Use a variable @nameTable with dynamic queries? 

    IF(@isProduction)
    SET @books = 'myBooksProduction' 

EXEC 'SELECT * FROM' + @books

    But I already use dynamic SQL in some procedures.

  2. Using a simple condition 

    IF(@isProduction)
BEGIN
    -- SELECT * FROM MyFilesProduction, MyBooksProduction
ELSE
    -- SELECT * FROM myFiles, myBooks
END

    but I have to use redundant code

  3. some other way ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Huojian
  • 198
  • 2
  • 14
  • 2
    Since dynamic sql can be subject to SQL injection I'd lean towards the second if you must choose one of these two. Since you mentioned production I assume the other is test, in which should be on separate databases IMHO. Then, you would have the proc on the test DB and the production DB and avoid this all together. Why clog up your production DB with erroneous or duplicated tables? – S3S Dec 07 '16 at 16:48
  • You use different databases for dev and prod, and have the same schema in both. Silly rabbit! mixing dev and prod stuff in a single environment >. – Ricardo C Dec 07 '16 at 16:56
  • Simply because that was a model to do urgently and now the (difficult) customer want a table to test data. This is a really big model and I do no have time to duplicate it. thanks for your reply – Huojian Dec 07 '16 at 17:00
  • Don't have the time? You may want to spend the time... just clone the DB... there's plenty of examples like http://stackoverflow.com/questions/3829271/how-can-i-clone-an-sql-server-database-on-the-same-server-in-sql-server-2008-exp – S3S Dec 07 '16 at 17:02
  • Yes and after that i need to change all my datasource from excel, ssas, ssrs and ssis? I've also others db to test other models but at this moment this is not possible i can't, I juste want the best to way to select to correct table (juste two in all my model). Thanks for the subject i'm keeping it by my side ! – Huojian Dec 07 '16 at 17:11
  • 1
    Just remember not fixing the problem now just means you'll have to fix it later... and more ssis packages... and more vb code... and...etc – S3S Dec 07 '16 at 17:12
  • I'll leave the implications to a separate discussion. The difference between the two options is that Dynamic SQL requires _on-the-fly_ compilation of the command (your select statement) whereas the IF THEN ELSE does not. For a very simple select statement you may see no difference between the two options. If, on the other hand, the select statement is quite complex and is invoked repeatedly, you will definitely see performance difference. I hope this answers your question. Still, some of the above comments may deserve your attention (after some cleaning of irrelevant wordings). – FDavidov Dec 07 '16 at 17:19

0 Answers0