Conditional Operators In SQL Statements
Oct 28, 2005
95% of our DB Calls are in STored Procedures, however there are a few that we're finding hard to convert.
Example:
string strSQL = "Some Big *** SQL query with 8 inner joins";
If(Some Variable == something)
strSQL += "AND ...':
if(Something == somethingElse)
strSQL += " AND (this > that OR this <= other + 1);
so on and so forth.
Is it possible to put these types of statements in a Stored Procedure so I can get this out of code?
View 3 Replies
ADVERTISEMENT
Dec 13, 2005
i would like some conditional settings in a stored procedure
i have a variable
@Variable
and I want to do a conditional statement like
if @Variable = 1 then @Variable2 = 1
Elseif @Variable = 3 then @Variable2 = 4
Else @Variable = 11 then @Variable2 = 12
not sure about how to implement elseif bit
i know you can have
{if this Else That} in T-Sql
View 3 Replies
View Related
Dec 1, 2004
Hi there,
I'm probably just being thick but is there a way to use conditional statements within aggregate functions? I'm trying to do something along the lines of the following -
Code:
SELECT SUM(
CASE
WHEN Currency='GBP' THEN TotalAmountCharged
ELSE TotalAmountCharged/1.45
) as total
from bookshop_orders
where year(OrderDate) = 2004 and month(OrderDate) = 9
TIA
Nick
View 4 Replies
View Related
Mar 18, 2005
Hello,
I am trying to filter records according to an exact string case match and the operator = does not seem to do the job.
Executing,
SELECT * FROM Customers WHERE kUserId='admin' AND strPw='abcd'
and
SELECT * FROM Customers WHERE kUserId='admin' AND strPw='aBcD'
always fetches the same record.
So, how do I do about employing case-exact filtering?
Thank you very much.
David
View 1 Replies
View Related
Oct 28, 2007
I apologised if this has been posted before. I am having so many problems with these operators.
I can get this to work..
" Select *
from emp
where job is not 'manager';
but not
" Select *
from emp
where job is not 'manager'
or where job is not 'president' ;
View 2 Replies
View Related
Mar 19, 1999
Am I hallucinating or does SQL Server 6.5 not support the EXCEPT and INTERSECT operators?
View 1 Replies
View Related
Aug 9, 2006
Hi ,
In a stored procedure when retrieving records based on a DATETIME values in WHERE clause - can we use BETWEEN clause or Col <= AND Col >= ?
Please suggest which is the optimised way.
Thanks in Advance,
Hari Haran Arulmozhi
View 2 Replies
View Related
Mar 21, 2007
Hi I just got a paging procedure from the internet and it’s working just fine, but I would like to return the numbers of pages too. So I used the Count() function to retrieve all records on the table and divided by the page size, but even I doing with float number, this is returning a integer number. I just started with SQL Server, does some knows what is wrong with the following code? or knows a better way to do it.
Declare @Records int,
@Pages float,
@hey int
Set @Records = (Select Count(*) as 'Friends' From friends
Set @Pages = ((@Records) / (@PageSize))
Set @hey = (@Pages)
if @Pages > @hey
begin
@Pages = @Pages + 1
end
return @Pages
Thank you very much.
View 4 Replies
View Related
Jun 12, 2006
Hi ,
when
operator = then index SEEK
operator <> then index SCAN
Is normal ?
Example
SELECT *
FROM dbo.Batch
WHERE (Status = 'Batch Completed')
(1 row(s) affected)
StmtText
---------------------------------------------------------------------------------------------------------------------------------
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([PriceAvisPr].[dbo].[Batch]))
|--Index Seek(OBJECT:([PriceAvisPr].[dbo].[Batch].[IX_Batch]), SEEK:([Batch].[Status]='Batch Completed') ORDERED FORWARD)
StmtText
---------------------------------------------------------------------------------
SELECT *
FROM dbo.Batch
WHERE (Status <> 'Batch Completed')
(1 row(s) affected)
StmtText
------------------------------------------------------------------------------------------------------------------------
|--Clustered Index Scan(OBJECT:([PriceAvisPr].[dbo].[Batch].[PK_Batch]), WHERE:([Batch].[Status]<>'Batch Completed'))
View 2 Replies
View Related
Jul 22, 2007
What am I missing? I can only select 1 operator to inform.
Should an 'operator' be redefined to have multiple addresses, if so, how should they be delimited?
View 1 Replies
View Related
Oct 13, 2006
Hi,
I have an implementation of the UDT - 3-dimentional vector. In my code I have implemented add, subtract and multiply methods for the type. I have also implemented overloaded operators for +/-/* in my C# code. Those overloaded operator are working as expected in C# tests. However when I€™m trying to use +/-/* operators in T-SQL over my UDT it returns the following error:
Invalid operator for data type. Operator equals add, type equals Vector.
The following fragment does work:
DECLARE @v1 Vector, @v2 Vector, @v3 Vector;
SELECT @v1 = CAST('1,1,1' as Vector), @v2 = CAST('2,2,2' as Vector)
SELECT @v1 'v1', @v2 'v2', @v1.[Add](@v2) 'v1 + v2'
And this fragment does not work:
DECLARE @v1 Vector, @v2 Vector, @v3 Vector;
SELECT @v1 = CAST('1,1,1' as Vector), @v2 = CAST('2,2,2' as Vector)
SELECT @v1 'v1', @v2 'v2', @v1+@v2 'v1 + v2'
I guess that SQL Server is not aware of the operators€™ overload I have implemented in the C# code. Is there any way to instruct SQL Server to use overloaded operators in the T-SQL so the code will look naturally @a + @b instead of @a.[Add](@b) and as a result use standard summary functions SUM() instead of writing user defined aggregate function for the Vector type field?
Maxim
View 4 Replies
View Related
Jan 26, 2007
Hi there,
I'm not sure which section this really comes under and I think 'availability/disaster recovery' is probably the most applicable, sorry if it isn't.
I have SQL Server 2005 set-up and about 50 jobs listed, now, these jobs were configured before an operators with alerting by email was set-up... so, none of the jobs when fail alert an operator.
Is there any way I can automate a solution so if any job fails a designated/or default operator will be emailed and told what happened? Or would I need to edit each job manually to set this?
Thanks
Ed
View 1 Replies
View Related
Mar 7, 2008
Hello everyone,
Is there a way in order to execute a subscribed report based on a certain criteria?
For example, let's say send a report to users when data exist on the report else if no data is returned by the query
executed by the report then it will not send the report to users.
My current situation here is that users tend to say that this should not happen, since no pertinent information is contained in the report, why would they receive email with blank data in it.
Any help or suggestions will be much appreciated.
Thanks,
Larry
View 6 Replies
View Related
Jun 19, 2012
I've got a subquery that keeps throwing up an error but I can’t think of another way of completing the query.
Firstly I need to find records where TestQty =3
(this would find 2 records with TestQty =3, TestNumber, 7171003 and 7088650)
The TestNumber is not unique so I would like the final set of records to include all the records with TestNumber, 7171003 and 7088650
TestNumberTestQty
7088650____________3
7088650____________1
7088650____________2
7088650____________1
7088650____________2
7171003____________1
7171003____________3
7171003____________2
Code below:
[Code SQL]
USE TestWarehouse
IF ('dbo.TestItems') IS NOT NULL
DROP TABLE [dbo].[TestItems];
GO
CREATE TABLE [dbo].[TestItems]
( TestItem int not null IDENTITY (1,1)
[Code] .....
View 5 Replies
View Related
Sep 14, 2006
I'm trying to put an expression into a view query column. I've already created a column which contains the results of a calculation in the form of an expression. What I don't know how to do is use comparison operators to return a boolean value for display. I'll give some examples of what I'm trying to do, using literal values just to simplify things...
So say I want to display the result of an expression in a view query column, as a boolean value (or BIT), as True or False. I've tried putting the following expressions into the column and none work:
4 < 5: Access converts this to the string '4 < 5' and it's just displayed as a string.
CAST( '4 < 5' AS BIT ): When run, the query displays the error 'Syntax error converting the varchar value '4 < 5' to a column of data type BIT.
Although I'm using Access 2003, It's just a front end for a SQL Server 2000 database. The actual SQL statement containing the latter SQL function is:
SELECT dbo.Tapes.No, dbo.Tapes.Status, dbo.Tapes.LastWrittenDate, dbo.Tapes.Pool, dbo.Tapes.Location, dbo.TapeJobs.No AS Expr1, dbo.TapeJobs.JobNo,
dbo.TapeJobs.TapeNo, dbo.Jobs.No AS Expr2, dbo.Jobs.Date, dbo.Jobs.Successful, dbo.Jobs.RetentionRule, dbo.RetentionRules.No AS Expr3,
dbo.RetentionRules.Name, dbo.RetentionRules.Description, dbo.RetentionRules.RetentionPeriod, dbo.RetentionRules.RetentionCycles,
dbo.Jobs.Date + dbo.RetentionRules.RetentionPeriod AS AgedJob, CAST('4 < 5' AS BIT) AS Expr4
FROM dbo.RetentionRules INNER JOIN
dbo.Jobs ON dbo.RetentionRules.No = dbo.Jobs.RetentionRule RIGHT OUTER JOIN
dbo.Tapes RIGHT OUTER JOIN
dbo.TapeJobs ON dbo.Tapes.No = dbo.TapeJobs.TapeNo ON dbo.Jobs.No = dbo.TapeJobs.JobNo
I'm new to SQL. I think the CAST function is the way forward, but I just don't know how to force it to resolve the expression '4 < 5'.
Can someone please help ?
--
Paul Anderson
View 8 Replies
View Related
Dec 29, 2007
Hi:
I am trying to create a stored procedure that filters some customers. The field in wich I am trying to apply the filter is the age field. The problem is that I need to be able to select the comparison operator =,<,>,=<,>=,<>.
I was trying to do it with the following code:
Select CustomerName From Customer Where
(CustomerAge & @Operator & @Age)
But sql Server shows an error telling me that @Operartor couldnt be converted to int.
I dont know if I am in the right track or way off, how is this done?
View 3 Replies
View Related
Apr 14, 2008
Hi everybody:
I want know where can I find information about valid expresion types for each one of de Arithmetic Operators, Bitwise Operators, Comparison Operators, Logical Operators, Unary Operators in SQL server. I want something like this.
for (+) ADD, syntaxis -> expression + expression
expression Valid? result type
-----------------------------------------------------------------
int + int ok int
int + bigint ok bigint
char + char ok char <- (+) works like concatenation
char + int ok char???
image + bit error -
binary + int ok binary???
for (*) MULTIPLY, syntaxis -> expression * expression
expression Valid? result type
-----------------------------------------------------------------
int * int ok int
int * bigint ok bigint
char * char error -
char * int error -
image + bit error -
binary + int ok binary????
for (<) LESS THAN, syntaxis -> expresion < expression
expression Valid? result type
-----------------------------------------------------------------
int < int ok bit???
char < char ok bit???
char < int ok bit???
image < bit error -
tks for help.
Jack
View 3 Replies
View Related
Jul 24, 2007
Hello, how do you setup a Profiler filter, that has mixed AND/OR operators. Thru the Profiler GUI, I don't even see how you would ask for any statement/proc with READS over 10,000 OR DURATION over 1000ms. How do you do an "OR" thru the GUI?
Forgetting the GUI and doing traces thru scripts, the @logical_operator isn't too logical, and I can't find any description for setting this parameter. If you only have one filter, it doesn't matter if you set it to 0 (AND) or to 1 (OR)... But if you want multiple filters, so one filter is Reads >= 10,000 and the other filter is Duration >= 1000ms., how do you do that? I tried it with the @logical_operator of "1" on both, but then I also got other events with a NULL value in READS.
Code Snippet
-- @traceID = 1
-- @columnID = 13 (Duration)
-- @logicalOperator = 1 (OR)
-- @comparison_operator = 4 (greater or equal)
-- @value = 1000000 (1000 ms)
sp_trace_setfilter 1, 13, 1, 4, 1000000
-- @traceID = 1
-- @columnID = 16 (Reads)
-- @logicalOperator = 1 (OR)
-- @comparison_operator = 4 (greater or equal)
-- @value = 10000 (# Reads)
sp_trace_setfilter 1, 16, 1, 4, 10000
Looks like DURATION is never NULL for these events>>>
RPC:Completed
SP:Completed
SPtmtCompleted
SQL:BatchCompleted
SQLtmtCompleted
but the READS value cane be null (for SP:Completed at least).
Any ideas on how to mix AND/OR operators in multiple filters of one trace?
Thanks, Bruce
View 1 Replies
View Related
Apr 4, 2008
Hi all, real basic question:
I can't seem to find a basic reference that tells me the syntax and allowable values of parameters in common functions such as Format or CDate when editing a Field Expression in Visual Studio (Report Definition Language?). Where would I start?
For example, I've discovered that the Format function "Returns a string formatted according to instructions contained in a format String expression.", and the string expression can have values like "D" and "d" which produce different results, but where would I find out what the allowable string expressions are and their meaning?
I have also been guessing at the syntax of the CDate command with no luck. I need a reference that tells me what the different function parameters mean.
thanks
Jac
View 1 Replies
View Related
Mar 16, 2014
I am in the midst of writing a query to return a range of product BIN LOCATIONS from a warehouse stock levels program.I know the start and end BIN LOCATIONS for the warehouse, e.g.: Start - #00000, and End - Z10000.However I cannot hard code these into the program, instead I want my operator to designate the first letter of both the start and end BINS locations, and then hit the 'go button'.Towards this end I have declared a couple of variables in my SQL, as follows -
Code:
declare @strBin varchar(10)
set @strBin = ''
if @strBin = ''
set @strBin = '#%'
declare @endBin varchar(10)
if @endBin = ''
set @endBin = 'z%'
My challenge is writing a relevant WHERE clause for my select statement, I have tried the following -
Code:
where BINLOCAT.BINLABEL between @strBin and @endBin
order by BINLOCAT.BINLABEL
Whilst the clause above does not cause any errors, neither does it return any results.My first thought is that the BETWEEN operator does not accept wildcards. But if this is correct then how do I go about allowing an operator to enter the start, and end BINS without typing the entire BIN string (e.g.: #00000)? I have tried >= @strBin and <= @endBin, but I am having a similar issue with no errors, and no data returned.
View 9 Replies
View Related
Mar 1, 2015
We can use comparison operators with strings as well. Hence, I tried to use the following query on a SQL Server 2012 instance with the sample AdventureWorks2012 database (the collation of the database and of the column is the default:
SQL_Latin1_General_CP1_CI_AS):
USE AdventureWorks2012 ;
GO
--Returns 5 records
SELECT pp.Name
FROM Production.Product AS pp
WHERE pp.Name >= N'Short' AND pp.Name <= N'Sport' ;
GO
The query only returns 5 records. This despite the fact that the search is an inclusive search and the Production.Product table contains records that begin with "Sport".
Now, when I replace "Sport" with "Sporu" (just moving one character up in the alphabet to verify whether characters after the word have any impact on the search) gives me 8 records.
USE AdventureWorks2012 ;
GO
--Returns 8 records
SELECT pp.Name
FROM Production.Product AS pp
WHERE pp.Name >= N'Short' AND pp.Name <= N'Sporu' ;
GO
What's going on inside of SQL Server that allows it to fetch "Short-Sleeve Classic Jersey" for the starting word "Short" but prevents it from fetching "Sport-100 Helmet" for the ending word "Sport" despite the search being an inclusive search?
View 3 Replies
View Related
Aug 7, 2015
I have a problem at the moment, where the client wants to be able to type in a custom algebraic formula with add/minus operators, and then to have this interpreted, so that the related datasets are then added and returned as a single dataset.
An example would be having a formula stored of [a] + [b] - [c]
and if I were to write the SQL to apply that formula, I might write something like (let's assume 1:1 relationships with the ID's)
select a.a + b.b - c.c as [result]
from z
inner join tblA a on z.id = a.id
inner join tblB b on z.id = b.id
inner join tblC c on z.id = c.id
The formula can change though, maybe things like:
[a] + [b] + [c] + [d]
[a] + [b]
The developer before me wrote something SQL-based where they parsed the string and assigned each value of the formula as either positive or negative (e.g A is positive, B is positive, C is negative, now sum the datasets to get the result), and then created one large table of values then summed them. This does (kind of) work, I'm just contemplating potential alternatives, as it is quite a slow process, and feels like it is quite convoluted, when I get into the details. If I were to do something like this in SQL, I'd normally want each part of the expression to be a column, and then to just apply the operators, but because the formula can change, then the SQL would need to be somehow dynamic for this approach.
View 5 Replies
View Related
Oct 11, 2015
DECLARE @Teams AS TABLE(Team VARCHAR(3))
INSERT INTO @Teams
SELECT 'IND'
UNION
SELECT 'SA'
UNION
SELECT 'AUS'
select Team from @Teams where Team > 'AUS'
[code]....
co-relation between comparison operators in WHERE Clause and the respective output.
View 3 Replies
View Related
Dec 15, 2006
I have the following code in the color property of a textbox. However, when I run my report all of the values in this column display in green regardless of their value.
=SWITCH(Fields!Wrap.Value >= 3, "Red", Fields!Wrap.Value < 3, "Green")
I already tried =iif(Fields!Wrap.Value >= 3 , "Red", "Green") and got the same results.
Is it because this is a matrix report? What am I doing wrong?
Thanks in advance . . .
View 4 Replies
View Related
Apr 13, 2001
SQL 7
Hi All !!
I want to know how I can create conditional FROM WHERE clauses like below ..
SELECT X,X,X
FROM
CASE @intAltSQL > 0 Then Blah Blah Blah END
CASE @intAltSQL = 0 Then Blah END
WHERE
CASE @intAltSQL > 0 Then Blah Blah Blah END
CASE @intAltSQL = 0 Then Blah END
Thoughts ?
Thanks
View 4 Replies
View Related
Jun 23, 2008
Is anything similar to this possible without writing whole query in every begin block or without dynamic sql:
proc(@arg1 bit)
select * from table where x=y
if @arg1 = 1 begin
and when a=b
end
order by date
View 6 Replies
View Related
Jul 23, 2005
What does "conditional" mean as a command? sp_who2 reports this forsome sessions, along with "insert" etc.Thanks,Jim Geissman
View 2 Replies
View Related
Apr 23, 2007
Hi,
Is it possible to have a conditional sum based on an item type existance in a set of values?
Example if i have the following set:
A
A
A
A
B
I just wanna sum B else if B doens't exist sum A
Best Regards,
Luis Simoes
View 7 Replies
View Related
Jan 30, 2007
Im faced with the following design issue..
on my site there are different profiles: a city profile, a restaurant profile and a user profile.
in my DB:City profiles are stored in tbCities cityID int PK shortname nvarchar(50) forumID int FK (...)
Restaurant profiles are stored in tbRests restID int PK shortname nvarchar(50) forumID int FK (...)
User profiles are stored in tbUsers userID int PK shortname nvarchar(50) forumID int FK (...)
as you can see a single ID value (for CityID,restID or userid) might occur in multiple tables (e.g. ID 12 may exist in tbRests and in tbUsers)Each of these profile owners can start a forum on their profile.
forumID in each of the above tables is a FK to the PK in tbForums:forumID intforumname nvarchar(50) (...)
Now imagine the following:
a site visitor searches ALL forums...say he finds the following forums:ForumID Forumname1 you opinion on politics2 is there life in space?3 who should be the next president of the USA?
a user may want to click on the forum name to go to the profile the forum belongs to.And then there's a problem, because I dont know in which table I should look for the forum ID...OR I would have to scan all tables (tbCities,tbRests and tbUsers) for that specific forumid,which is time-consuming and I dont want that!
so if a user would click on forumID 2 (is there life in space?)
I want to do a conditional inner join for the tablecontainingforumID (which may be tbCities,tbRests or tbUsers)
select tablecontainingforumID.shortname FROM tablecontainingforumID tINNER JOIN tbForums f ON t.ForumID=f.ForumIDwhere f.ForumID=2
I hope my problem is clear..any suggestions are welcome (im even willing to change my DB design if that would increase effectivity)
View 6 Replies
View Related
Jan 31, 2007
I encounter a T-Sql problem related to if conditional processing:The following script execute an insert statement depending on whether column 'ReportTitle' exists in table ReportPreferences. However it gets executed even when ReportTitle column is not present.Could anyone offer some advice? IF(Coalesce(Col_length('ReportPreferences','ReportTitle'),0) > 0) BeginINSERT INTO dbo.DefaultsSELECT FinancialPlannerID,ReportTitleFROM dbo.ReportPreferencesendGO
View 6 Replies
View Related
Jul 23, 2007
I have a stored procedure that performs a search function with params:@username nvarchar(50)@country nvarchar(50)and like 10 more.A user may provide values for these params optionally.So when the @username var is left blank, there should be no filtering on the username field (every field should be selected regardless of the username)Currently my statement is:select username,country from myUsers whereusername=@username and country=@countryWith this statement when a user provides no value for username the username field selects on ''m which returns ofcourse nothing...What can I do to solve this?Thanks!
View 6 Replies
View Related
Feb 4, 2008
Hi, is it possible to do a conditional WHERE in T-SQL? I have a table with a column that consists of a reference that starts with either a single alpha character or two alpha characters followed by four numeric digits (the numeric portion is always unique but the alpha isn’t). E.g. A1234, AB1235, AB1236, C1237, HT1238. What I want to do is select a range of rows based on the numeric portion of this reference column. In other words I want to select say 50 rows starting from row 1000 (rows 1000 to 1050) regardless of whether there is one or two alpha characters preceding the numerics.The Stored procedure I have so far works (using COUNT for testing) for selecting a range of rows that has two alpha's at the start. However, if I simply add an OR to the WHERE to select rows where there is a single alpha in the reference column, when a single alpha reference is found it will fail the first logical check for two alpha's giving an error condition. Therefore, how can I incorporate a conditional WHERE using IF or some alternative method, so that it will also give me all the rows in the number sequence that start with either single or double alpha's within the same SELECT / WHERE statement?
Thanks for any help.ALTER PROCEDURE [dbo].[sp_Test]
(
@startRef int,
@endRef int
)
AS
BEGIN
SELECT Count(*) FROM myTable
WHERE ((SUBSTRING(Ref,3,LEN(Ref)-2) BETWEEN @startRef AND (@startRef + @endRef)))
END
RETURN
View 2 Replies
View Related
Feb 23, 2008
I have an SqlDataSource that uses a value from the query string in the WHERE clause of the sql statement.
The sql is something like this:
SELECT * FROM myTable WHERE myfield = @myfield
and I have the QueryStringParameter setup like this:
<asp:QueryStringParameter Name="myfield" QueryStringField="myfield" />
What I need is for the sql statement to return all records in the case that "myfield" is not defined in the query string.
How would I implement this?
Thanks,
Joshua Foulk
View 6 Replies
View Related
