SQL converting column name instead of value

Question

I have a Surveys table which includes several boolean columns (where 0 = false and 1 = true). What I'm trying to do is have another table where 1 column contains the names of these columns, and the other column calculates the percentages of the true (1) occurrences. Based on this reference, I created a T-SQL Script to do this:

/****** Drop existing table ******/
DROP TABLE [dbo].[PercUsedFor]
GO

/****** Drop existing calculated values ******/
IF OBJECT_ID('GetPercUsed', 'FN') IS NOT NULL
  DROP FUNCTION GetPercUsed
GO

/****** Calculate new values ******/
CREATE FUNCTION GetPercUsed (@Task nvarchar)
RETURNS DECIMAL
AS
BEGIN
  DECLARE @Total INT
  SELECT @Total = COUNT(*) FROM Surveys

  DECLARE @Count INT
  SELECT @Count = COUNT(*) FROM Surveys WHERE @Task = 1

  DECLARE @Percent DECIMAL
  SELECT @Percent = ((@Count / @Total) * 100)

  RETURN @Percent
END
GO

/****** Create new table ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PercUsedFor](
    [Task] [nvarchar](50) NULL,
    [PercUsed] AS ([dbo].GetPercUsed(Task))
)
GO

/****** Fill in values ******/
INSERT INTO [dbo].[PercUsedFor] (Task)
SELECT 'Volume'
SELECT 'Speed'
SELECT 'Gap'
SELECT 'Length'
SELECT 'Stats'
GO

/****** Show table ******/
SELECT * FROM [dbo].[PercUsedFor]
GO

When I run this, the table is created, but the last line

SELECT * FROM [dbo].[PercUsedFor]"

gives me an error

Conversion failed when converting the nvarchar value 'V' to data type int.

The only place I see that could be what is causing this is in the Function at

SELECT @Count = COUNT(*) FROM Surveys WHERE @Task = 1

like it's trying to convert the value of @Task (being "Volume") into an int, instead of finding the instances of '1' in Surveys for that column.

How can I fix this? How can I make it so that I can properly use a query with a dynamic column name in the Where clause?

Community · Accepted Answer · 2017-05-23 12:14:08Z

3

select .. where 'V' = 1

Is not valid as it will attempt to coerce V to an int.

select .. where 'V' = '1'

If you pass 'Volume' then as your @Task nvarchar has no size it will default to 1 character (V)

... use a query with a dynamic column name in the Where clause?

If your intention is to use @Task as a column name this will not work, you need to use Dynamic SQL

edited May 23, 2017 at 12:14

CommunityBot

11 silver badge

answered Jun 22, 2015 at 15:41

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andy Over a year ago

thank you on both accounts. That thread you linked included a link to an article on dynamic SQL. Through that and other examples I managed to figure out how to modify that SELECT statement. However, it turns out you can't use dynamic SQL in user defined functions. The only other way I can think of to manage what I'm trying to do is brute forcing a calculation for each specific Task. Is there a better way you can suggest instead?

LDMJoe · Accepted Answer · 2015-06-22 16:32:54Z

You don't want your input variable in your WHERE clause.

To make this work, I had to invent a Surveys table. Here's what I used...

CREATE TABLE dbo.[Surveys]
    (
    SurveyID bigint identity (1,1) NOT NULL,
    SurveyName [nvarchar](max) NULL,
    SurveyStarted bit null,
    SurveyCompleted bit null,
    SurveyBilled bit null,
    SurveyPaid bit null,
    SurveyCanceled bit null
    )

INSERT INTO Surveys (SurveyName, SurveyStarted, SurveyCompleted, SurveyBilled, SurveyPaid, SurveyCanceled) VALUES ('Survey 1', 1, 1, 1, 1, 0)
INSERT INTO Surveys (SurveyName, SurveyStarted, SurveyCompleted, SurveyBilled, SurveyPaid, SurveyCanceled) VALUES ('Survey 2', 0, 0, 0, 0, 1)
INSERT INTO Surveys (SurveyName, SurveyStarted, SurveyCompleted, SurveyBilled, SurveyPaid, SurveyCanceled) VALUES ('Survey 3', 1, 0, 0, 0, 0)
INSERT INTO Surveys (SurveyName, SurveyStarted, SurveyCompleted, SurveyBilled, SurveyPaid, SurveyCanceled) VALUES ('Survey 4', 1, 1, 1, 0, 0)
INSERT INTO Surveys (SurveyName, SurveyStarted, SurveyCompleted, SurveyBilled, SurveyPaid, SurveyCanceled) VALUES ('Survey 5', 1, 1, 0, 0, 0)

I'm still fighting with a function to do this, but I think the answer lies in doing an UNPIVOT to get all the bit fields as values, then using the input variable in the WHERE clause against those column names pivoted into field values.

Okay, if you use this in your function...

SELECT
    ThingType, 
    SUM(CONVERT(int, ThingState)) AS [Count]
FROM
    (
    SELECT SurveyName, ThingType, ThingState FROM Surveys 
    UNPIVOT
        (
        ThingState FOR ThingType IN (SurveyStarted, SurveyCompleted, SurveyBilled, SurveyPaid, SurveyCanceled)
        ) Unpivoted
    ) DerivedUnpivoted
GROUP BY
    [ThingType]

...you should be able to tack a WHERE clause onto the bottom of it to only get the one row for calculating your @Count variable, then do the rest of the math as you intended. This avoids Dynamic SQL entirely.

paparazzo · Accepted Answer · 2015-06-22 17:19:19Z

If I understand the question there is a very easy and very efficient answer
SQL does not have boolean it has Bit
Bit false = 0 and Bit True = 1
You can't sum on Bit but you can cast it to tinyint and then sum

Run this is or just use it to create a view

  select sum(cast(question1 as tinyint)) / cast(count(*) as numeric) as [Q1pct]
       , sum(cast(question2 as tinyint)) / cast(count(*) as numeric) as [Q2pct] 
    from [Survery]

As for the existing query a few problems:

(@Task nvarchar)
nvarchar will default to a width of 1 so it is getting truncated

@Task = 1
1 is an integer so you are getting a conversion error
need @Task = '1'

You don't have a single task of '1'

Not at all clear what you mean by instead of finding the instances of '1' in Surveys for that column.

I think you mean

SELECT @Count = COUNT(*) FROM Surveys WHERE Task = @Task

Even then you are going to have problems with

SELECT @Percent = ((@Count / @Total) * 100)

As integer will round down to 0

SELECT @Percent = (100* @Count / @Total)

Even above will round down to and integer

SELECT @Percent = (100* cast(@Count as decimal) / @Total)

Collectives™ on Stack Overflow

SQL converting column name instead of value

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related