Creating SQL Statements to work with both SSMS and SSRS.

Question

I need to set up this question a moment. Let's say you've created a database called ParameterTesting and created a table and populated it like so:

IF EXISTS
     (SELECT 1
      FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'StarWars'
      AND   TABLE_SCHEMA = 'dbo')
  BEGIN
    DROP TABLE dbo.StarWars
    PRINT 'Dropped StarWars'
  END



SET  ANSI_NULLS ON;
GO
SET  QUOTED_IDENTIFIER ON;
GO

CREATE TABLE [dbo].[StarWars]
(
  [IdentityColumn]     INT IDENTITY (1, 1) NOT NULL
 ,[MasterDate]         DATETIME NULL
 ,[ForcePowerRating]   INT NULL
 ,[Name]               VARCHAR(25) NULL
 ,[Status]             VARCHAR(25) NULL
)
ON [PRIMARY];
GO


INSERT INTO StarWars(
              MasterDate
             ,ForcePowerRating
             ,[Name]
             ,[Status])
VALUES (
         '5/21/1980'
        ,500
        ,'Luke Skywalker'
        ,'Master')

INSERT INTO StarWars(
              MasterDate
             ,ForcePowerRating
             ,[Name]
             ,[STATUS])
VALUES (
         '1/1/1910'
        ,1000
        ,'Yoda'
        ,'Jedi Council')

INSERT INTO StarWars(
              MasterDate
             ,ForcePowerRating
             ,[Name]
             ,[STATUS])
VALUES (
         '1/1/1930'
        ,NULL
        ,'Obi-Wan Kenobi'
        ,'Master')

INSERT INTO StarWars(
              MasterDate
             ,ForcePowerRating
             ,[Name]
             ,[STATUS])
VALUES (
         '1/1/1920'
        ,300
        ,NULL
        ,NULL)

INSERT INTO StarWars(
              MasterDate
             ,ForcePowerRating
             ,[Name]
             ,[STATUS])
VALUES (
         NULL
        ,450
        ,'Anakin Skywalker'
        ,'Sith Apprentice')

In this example I am trying to create a multi-valued parameter for Status, but this gets tricky because the Status column can be null. If I didn't have to worry about nulls, I could simply set the available values and default values from the same query and the parameter becomes optional.

In my opinion the correct way to construct that Query in SSMS or a similar tool is:

DECLARE @Status AS VARCHAR(20)
SET @status = 'Master'

SELECT [Name]
      ,ForcePowerRating
      ,MasterDate
      ,Status
FROM StarWars
WHERE 
(status in (@Status) or (status is null and   '..None' in (@Status)))

I would populate the dataset for available and default values to:

select distinct coalesce(status, '..None') JediStatus from StarWars

I use the IN statement so it can support multiple values. My parameter dataset query substitutes ‘..None’ for nulls and the WHERE clause of this statement handles Null values. If you do not do so your parameter will not default to the entire list in SSRS because multiple value parameters cannot support nulls in SSRS.

Essentially, my problem is this. How can I use my query to populate my t-sql variable when working with it in SSMS or another tool?

I want to be able to run my queries (including the parameter list query) in SSMS to check my work. Instead of:

Set @Status = ‘Master’

I want to do something like:

Set @Status = select distinct coalesce(status, '..None') JediStatus from StarWars

That won’t work and using a table variable won’t either because you cannot use IN with a table variable. I looked into using a SP to create a string of characters (from a query) like ‘Master’,’Jedi Council’,’..None’………

That’s very complex because I have to support SQL 2000 and cannot use Common Table Expressions.

So, what are my options? Am I thinking about this correctly?

Answer 1

Why not use a table varaible and use a join instead of an in clause?