Avoid dynamic SQL

• emily-1119612

  SSCarpal Tunnel

  Points: 4166

  More actions

  February 26, 2010 at 2:26 pm

  #230322

  Greeetings all,

  Sample data and question to follow.

  CREATE TABLE #Accounts

  (

  Account varchar (10)

  )

  INSERT INTO #Accounts

  SELECT 'Account1' UNION

  SELECT 'Account2' UNION

  SELECT 'Account3' UNION

  SELECT 'Account4' UNION

  SELECT 'Account5'

  CREATE TABLE #AccountGroups

  (

  AccountGroup varchar (10)

  )

  INSERT INTO #AccountGroups

  SELECT 'Group1' UNION

  SELECT 'Group2 '

  CREATE TABLE #AccountGroupMembers

  (

  AccountGroup varchar (10),

  Account varchar (10)

  )

  INSERT INTO #AccountGroupMembers

  SELECT 'Group1','Account1' UNION

  SELECT 'Group1','Account2' UNION

  SELECT 'Group2','Account3' UNION

  SELECT 'Group2','Account4' UNION

  SELECT 'Group2','Account5'

  CREATE TABLE #Sales

  (

  Account varchar (10),

  AmountSold int

  )

  INSERT INTO #Sales

  SELECT 'Account1', 3 UNION

  SELECT 'Account1', 4 UNION

  SELECT 'Account2', 6 UNION

  SELECT 'Account2', 12 UNION

  SELECT 'Account2', 1 UNION

  SELECT 'Account4', 4 UNION

  SELECT 'Account4', 9 UNION

  SELECT 'Account5', 3

  SELECT * FROM #Accounts

  SELECT * FROM #AccountGroups

  SELECT * FROM #AccountGroupMembers

  SELECT * FROM #Sales

  --The parameter passed to my procedure can either be a group or an account

  DECLARE @param1 varchar (15)

  SET @param1 = 'Account1' --test for account

  --SET @param1 = 'Group1' --test for group

  --I can then determine whether the parameter is a group or an account

  --An account can not have the same name as a group and vice versa

  DECLARE @Account_or_Group varchar(7)

  IF EXISTS

  (

  SELECT Account FROM #Accounts WHERE Account = @param1

  )

  BEGIN

  SET @Account_or_Group = 'Account'

  END

  ELSE

  BEGIN

  SET @Account_or_Group = 'Group'

  END

  PRINT @Account_or_Group

  --Based on the @Account_or_Group I sum sales

  IF @Account_or_Group = 'Account'

  BEGIN

  SELECT SUM(AmountSold) FROM #Sales WHERE Account = @param1

  END

  ELSE

  BEGIN

  SELECT SUM(AmountSold) FROM #Sales

  WHERE Account IN

  (

  SELECT Account FROM #AccountGroupMembers WHERE AccountGroup = @param1

  )

  END

  DROP TABLE #Accounts

  DROP TABLE #AccountGroups

  DROP TABLE #AccountGroupMembers

  DROP TABLE #Sales

  My two sales summary statements are actually a lot more complicated. My goal is to have one SELECT statement to maintain. So I want to avoid the "If @Account_or_Group = x Do this else do this" method. I also do not want to use dynamic SQL.

  Possible?

• Rob Schripsema

  SSCertifiable

  Points: 7469

  More actions

  February 26, 2010 at 4:26 pm

  #1125866

  You could simplify the query *somewhat* by using a couple of conditionals in the WHERE clause (which would at least leave the SELECT portion of the query common to both cases):

  WHERE ((@Account_or_Group = 'Account') and (Account = @param1))

  OR

  (Account IN (SELECT Account FROM #AccountGroupMembers WHERE AccountGroup = @param1))

  Not sure if that really helps your cause any....

  Rob Schripsema

  Rob Schripsema
  Propack, Inc.

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  February 26, 2010 at 4:52 pm

  #1125870

  Agreed, the WHERE clause can combine both, but also you can do away with the @Account_Or_Group variable and associated lookup code.

  You said the two lookups are mutually exclusive, so there is no real need to worry about them....

  SELECT SUM(AmountSold) FROM #Sales WHERE Account = @param1

  OR Account IN

  (

  SELECT Account FROM #AccountGroupMembers WHERE AccountGroup = @param1

  )

  With your test data, because you have the same number of records in tables #Accounts and #AccountGroupMembers, both forms of the queries will perform about the same - your form has to query #Accounts to see if it is an Account lookup - this form queries #AccountGroupMembers, but not #Accounts. Both have to read #Sales.

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]