Parameter Problem

• Loner

  SSC-Insane

  Points: 21279

  More actions

  May 15, 2015 at 2:46 pm

  #296975

  I have two parameter - Action and Result.

  Action - available value from a query - SELECT actionid, actionvalue from ACTION

  Result - available value from a query - SELECT resultid , resultvalue from ACTIONRESULT where actionid = actionid from Action parameter

  Howerer not all actionid in ACTIONRESULT, now the users want if the actionid not in ACTIONRESULT, display all result from the ACTIONRESULT table.

  There is no expression in the available value in parameter so I don't know how to make it to work.

  Any idea??

• Alan Burstein

  SSC Guru

  Points: 61136

  More actions

  May 15, 2015 at 4:14 pm

  #1798368

  Using this DDL and sample data:

  USE tempdb

  GO

  CREATE TABLE dbo.[ACTION]

  (

  actionid int not null,

  actionvalue varchar(100) not null

  );

  CREATE TABLE dbo.ACTIONRESULT

  (

  resultid int primary key,

  actionid int not null,

  resultvalue varchar(100) not null,

  );

  GO

  INSERT dbo.[ACTION] VALUES (1,'action1'),(2,'action2'),(3,'action3');

  INSERT dbo.ACTIONRESULT VALUES (1,1,'result1'),(2,2,'result12);

  You could use this logic:

  DECLARE @Action int = 1;

  SELECT *

  FROM dbo.ACTIONRESULT

  WHERE actionid = @Action

  OR NOT EXISTS (SELECT actionid FROM dbo.ACTIONRESULT WHERE @Action = actionid);

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  May 15, 2015 at 4:14 pm

  #1798369

  Just add a second query to the dataset/procedure (You are using Stored Procedures?)

  SELECT resultid , resultvalue from ACTIONRESULT where actionid = @actionid;

  IF @@ROWCOUNT=0

  SELECT resultid , resultvalue from ACTIONRESULT ;

  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]