SQL Compare on varchar values

Question

I have a field that stores value like >=99.35 (storing goals in table) and lets say the actual data value is 78. I then need to compare if goal met or not. How can I accomplish this?

I tried to put that in a @sql variable which will say like:

Select case when 78>=99.35 then 1 else 0 end

but how can I execute this @sql to get the value 1 or 0 in a field of a table?

What you've written is [dynamic SQL](https://msdn.microsoft.com/en-us/library/ms188001.aspx). The MSFT article shows how to execute such a statement. — xQbert, Aug 13 '15 at 14:13

score 0 · Answer 1 · answered Aug 13 '15 at 14:11

0

DECLARE @sql VARCHAR(1000);
DECLARE @Result INT;

SET @Result = 78;
SET @sql = 'SELECT CASE WHEN ' + @Result + ' >=99.35 THEN 1 ELSE 0 END'

EXEC sp_executesql @sql

Using sp_executesql is more likely to cache the query plan if you are going to be repeatedly calling this SQL statement.

answered Aug 13 '15 at 14:11

Neil Knight

47,437
25
129
188

Yes, this runs fine but I need to update another field with the value returned from @sql something like - update x set field = exec (@sql) – user4946275 Aug 13 '15 at 14:39
@user4946275: See [this question](http://stackoverflow.com/questions/803211/how-to-get-sp-executesql-result-into-a-variable). – Joe Farrell Aug 13 '15 at 14:48

score 0 · Answer 2 · answered Aug 13 '15 at 14:33

0

DECLARE @ActualValue INT = 100

DECLARE @SQLQuery nVARCHAR(MAX) = ''

SET @SQLQuery = ( SELECT 'SELECT CASE WHEN '+CONVERT(VARCHAR,@ActualValue)+ YourColumn+ 'THEN 1 ELSE 0 END AS ResultValue'  FROM YourTable )


EXECUTE sp_executesql  @SQLQuery

answered Aug 13 '15 at 14:33

mindbdev

404
3
8

Some explanation would be nice. – Aug 14 '15 at 02:00

score 0 · Answer 3 · answered Aug 13 '15 at 14:47

A solution using sp_executesql as described in the other answers will work fine, but if you're going to be executing the contents of a table as part of a dynamic SQL statement, then you need to be very careful about what can be stored in that field. Do the goals you're storing always consist of a single operator and a target value? If so, it wouldn't be hard to store the two separately and process them with a static query. Something like:

declare @SampleData table
(
    [ActualValue] decimal(11, 2), 
    [Operator] varchar(2), 
    [ReferenceValue] decimal(11, 2)
);
insert @SampleData values
    (100, '>=', 98.25),
    (100, '<=', 98.25),
    (100, 'G', 98.25);

select
    [ActualValue],
    [Operator],
    [ReferenceValue],
    [GoalMet] = case [Operator]
        when '>=' then case when [ActualValue] >= [ReferenceValue] then 1 else 0 end
        when '<=' then case when [ActualValue] <= [ReferenceValue] then 1 else 0 end
        /*...other operators here if needed...*/
        else null
    end
from
    @SampleData;

This is a bit more verbose but perhaps a bit safer as well. Maybe it's usable for your general case and maybe it's not; I just thought I'd throw it out there as an alternative.

Thanks Joe. I have used this code and modified to get the <= symbols from the field and then compare! — user4946275, Aug 13 '15 at 17:50

SQL Compare on varchar values

3 Answers3