Case Senitive SQL Quary Using Datarow

Mar 14, 2008

I have in SQL Table1 the following values in
Field1:

ABC
ABc
AbC
abc
aBc
abC
  Select Field1 From Table1 Where Field1 = "AbC"

I get all six records, not just the one record (AbC) that I want to retrieve. How do I tell the select statement to be case sensitive?

 
my asp.net statement is given below
 
 DataRow dr1 = ds.Tables["Login"].Select("UserId ='" + txtUserId.Text + "'And Passwords ='" + txtPassword.Text + "'")[0]; 
i m using datarow
so i need a help
i have quary but i don;t know how can use
  DataRow dr1 = ds.Tables["Login"].Select("UserId ='" + txtUserId.Text + "'And Passwords ='" + txtPassword.Text + "'where CONVERT(binary(5),Userid)=CONVERT(binary(5),'" + txtUserId.Text + "')'" )[0];                                     
this give me error "Where condiction need operator"
waiting for reply
 

View 2 Replies


ADVERTISEMENT

Help In Solving SQL Quary

Jul 6, 2007

Hi,

I am new to the forum. I need help in solving the following quary.

Table structure:
Region: region_id, name
Employee: employee_id, name, region_id
Sales: sales_id, employee_id, sale_date, sale_amount
-there’s an individual entry in the sales table for each sale


Problem
Write a query that returns a list of the Regions and the number of employees in each region. The employee only gets added to the count if they had total sales greater than $50,000 last month. Sort the information by the number of employees per region who fit this criteria.


Thanks,
Divya

View 16 Replies View Related

How To Write Annual Report Quary

Jun 9, 2007

I want to write a report quary which will dispaly total of amount,hamali,servicetax,other,granttotal for a particular service
for a financial year

i have tried useing cross-tab but it is not working

OUT PUT OF QUARTY SHOULD LIKE

SERVICE NAME DATA 2007-APRIL 2007-MAY
PARCELAMOUNT 29503000
HAMALI 200256
SERVICE TAX 3010
OTHER 2060
GRANT TOT 3200 3326

View 2 Replies View Related

Parent/child Two Table Quary Distinct

Feb 4, 2005

hi,
i have two tables with parent/child relationship - pipeline and pipelineStatus. the select statement like this:

SELECT *
FROM pipeline INNER JOIN
pipelineStatus ON pipeline.id = pipelineStatus.parentID

i got multiple records for each pipeline.id because of multiple records of pipelineStatus. Is it possible to get only one record for each pipeline.id with last record of pipelineStatus table?
(stored procedure ok)

thanks advance for answering my question.

View 7 Replies View Related

DataRow Syntax

Feb 7, 2007

command.CommandText = “SELECT UserName from Users WHERE UserID =  “ = userID
 
Executing this command returns one table with one column with one row.  What is the syntax for getting that value into a variable?  I can get the information into a dataSet but I can’t get it out.  Should I be using a dataSet for this operation?
 
The rest of the code so far:
 
SqlDataAdapter dataAdapter = new SqlDataAdapter();
            dataAdapter.SelectCommand = command;
            dataAdapter.TableMappings.Add("Table", "Users");
 
            dataSet = new DataSet();
            dataAdapter.Fill(dataSet);

View 3 Replies View Related

DataRow Array

Jan 16, 2008

Hi,
 i m pretty new to this forum and c#.net 
i m doin a project in c#.net
I have four values in my datarow array
for example
DataRow[] cmb;
cmb=dsResult.Tables[0].Select("Controls Like 'cmb%'");// Here i m getting four Rows
 
for(i=0;i<cmb.Length;i++)
{
cmb[i]=Session["cmb'+i].ToString().Trim()//Here i m getting error;Cannot implicitly convert type 'string' to 'System.Data.DataRow'
}
 
 How to assign my session values to them.
I want to assign my value stored in the session variable to that array.Is there any way i can do it.Can i convert datarow array to string array! Please can any one help
me.
 

View 6 Replies View Related

How Can I Capture The Id Columne From A New Datarow?

May 14, 2007

In my BLL I have a method that adds a new row to a table in the database...
[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, true)]    public bool AddContact(string firstname, string lastname, string middleinit, bool active, Guid uid, bool newsletter)    {        ContactsDAL.tblContactsDataTable contacts = new ContactsDAL.tblContactsDataTable();        ContactsDAL.tblContactsRow contact = contacts.NewtblContactsRow();
        contact.FirstName = firstname;        contact.LastName = lastname;        contact.MiddleName = middleinit;        contact.Active = active;        contact.UserID = uid;        contact.Newletter = newsletter;
        contacts.AddtblContactsRow(contact);        int rowsAffected = Adapter.Update(contact);
        return rowsAffected == 1;    }
The primary key in this table is a BigInt set as an identity column....How do I capture the value of the primary key that gets created when the new row is added?

View 3 Replies View Related

Problem Returning A Datarow

Jul 23, 2005

Hi,I have a client/server app. that uses a windows service for the server and asp.net web pages for the client side. My server class has 3 methods that Fill, Add a new record and Update a record. The Fill and Add routines work as expected but unfortunately the update request falls at the 1st hurdle.I pass two params to the remote(server) method for the update, one is the unique ID and the other is a string that is the name of the table in the database. See code below. I need the SelectedRow method to return a datarow that will then populate textbox's on another page. When the method is called I get an 'internal system error.....please turn on custom errors in the web.config file on the server for more info.(unfortunately my server is not s web server so I don't have a web.config file!!).Can anyone see anything obvious.Cheers. >>Calling routine:Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.LoadSystem.Threading.Thread.CurrentThread.CurrentCultu re = New CultureInfo("en-GB")hsc = CType(Activator.GetObject(GetType(IHelpSC), _"tcp://192.168.2.3:1234/HelpSC"), IHelpSC)Dim drEdit As DataRowDim intRow As Integer = CInt(Request.QueryString("item"))strDiscipline = Request.QueryString("discipline")drEdit = hsc.SelectedRow(intRow, strDiscipline) <<Call the remote methodstrRecord = drEdit.Item(0)txtLogged.Text = drEdit(1)txtEngineer.Text = drEdit.Item(3)End SubRemote Class Function:Public Function SelectedRow(ByVal id As Integer, ByVal discipline As String) As System.Data.DataRow Implements IHelpSC.SelectedRowstrDiscipline = Trim(discipline)Dim cmdSelect As SqlCommand = sqlcnn.CreateCommandDim drResult As DataRowDim strQuery As String = "SELECT * FROM " & strDiscipline & _" WHERE CallID=" & idcmdSelect.CommandType = CommandType.TextcmdSelect.CommandText = strQuerysqlda = New SqlDataAdaptersqlda.SelectCommand = cmdSelectds = New DataSetsqlda.Fill(ds, "Results")drResult = ds.Tables(0).Rows(0)Return drResultEnd Function

View 3 Replies View Related

Simple DataRow Navigation Question

Sep 7, 2005

Hi. I am new to ADO.NET and I can't seem to figure out how to populate a row that is a layer lower than the instantiated row. I am populating and XML file called Users.xml:


Code:


<user>
<registerDate>9/6/2005</registerDate>
<firstName>TempUser</firstName>
<lastName>TempUser</lastName>
<emailAddress>TempUser</emailAddress>
<password>5F4DCC3B5AA765D61D8327DEB882CF99</password>
<securityQuestion>TempUser</securityQuestion>
<securityAnswer>TempUser</securityAnswer>
<zipCode>TempUser</zipCode>
<uniqueID>TempUser</uniqueID>
<alternateEmail>TempUser</alternateEmail>
<gender>TempUser</gender>
<industry>TempUser</industry>
<occupation>TempUser</occupation>
<jobtitle>TempUser</jobtitle>
<maritalstatus>TempUser</maritalstatus>
<birthDay>
<month>TempUser</month>
<day>TempUser</day>
<year>TempUser</year>
</birthDay>
<homelocation>...



and, i am using the below code to access and populate the rows based on the users registration input:


Code:


Dim NewLogin As Data.DataRow = LoginDS.Tables(0).NewRow



I am able to access all the rows that are one layer into the xml file with the following code:


Code:


NewLogin("someNode") = _someNode.Text



but, how do i populate nodes that are "further down" such as "birthDay". Do i have to reinstantiate NewLogin as ...Tables(1).NewRow? I have tried various forms of this and it doesnt work. Suggestions appreciated... thanks.

View 1 Replies View Related

Can We Specify Datarow Locking In Create Table Statement

Sep 24, 2007

Hi guys,

I have a question regarding a locking scheme in MSSQL I hope you guys can help. In Sybase, I am able to specify datarow locking in DDL (ex. create table, alter table). Can I do the same in MSSQL or is there an equivalent option in CREATE TABLE statement in MSSQL? I came across a few articles in MSDN about datarow locking and it seems to me that MSSQL only allows locking through DML... Is that true? Thanks.

View 2 Replies View Related

Primary Key In Datarow After Update Works In Access Not In Sql Server

Feb 15, 2005

Heys

a while back i had to do a project with an access database, one of the biggest problems i had back then was gettting the primary key
of a datarow you had just inserted into the database.

After a long set of trial and error i came up with the following:

- add the tablemappings of a table
- call the dataadapte.fillschema method

then after inserting a new row into the database the primary key gets filled in automatically!

now thing is

i was hoping to duplicate this in sql server

but it doesn't seem to work at all

so after i insert a row into my datatable
and update it
the row is in the database
but in vb the datarow primary key is not filled in!
anyone have an idea?

prefereabely one that does not resort to stored procedures with return parameters etc

thx a million in advance!

View 1 Replies View Related

ForEach Container With ADO Enumerator - Accessing The Current DataRow?

May 2, 2008

I will attempt to explain my situation more clearly.

I need to get data from a data source using a DataFlow Task (which pushes the DataSet into a Variable) and process the data row by row in a ForNext (ADO Enumerated) Container. I don't want to map the columns into variables because the number of columns and the data types vary considerably and I want to make my package as "generic" as possible.

Is there a way, in Script, to read the current row of the ForEach Container from into an ADO DataRow so that thie names and values of each column can be accessed?

I know how to read the entire DataSet object from a Variable into an ADO DataSet and iterate through the rows in the normal way but this doesn't suit my purpose. It would be really useful if there was a way to do somehing similar with the current DataRow in the ForEach Container.

To explain what I am doing, the idea is to use the Column Names and Values for each row to construct an xml fragement, store it in a string Variable and (in the next step) use the Web Services Task to call a Web Method with the xml fragment (from the Variable) as one of the inputs.

A less attarctive alernative would be to use a Scipt outside a ForEach Container and loop through the rows of teh DataTable as descibed above and perhaps call the Web Service Task from teh Script. The proble is that I don't know how to do this either and it woudl be much "neater" anyway to use the ForEach Container.

Any ideas?

View 7 Replies View Related

SQL Server 2008 :: Change Text Format From Case Sensitive To Case Insensitive?

Aug 31, 2015

How can I change my T-SQL text editor from text sensitive to text insensitive?

View 2 Replies View Related

Case Insensitivity Is On Server Wide: Tables Render Case Sensative...

Jan 6, 2005

Hello:

I have created an SQL server table in the past on a server that was all case sensative. Over time I found out that switching to a server that is not case sensative still caused my data to become case sensative. I read an article that said you should rebuild your master database then re-create your tables. So after rebuilding the master database, a basic restore would not be sufficient? I would have to go and manually re-create every single table again?

Any suggestions?

View 4 Replies View Related

Case Insensitive Searching In Sql Server 2000 When It's Case Sensitive

May 4, 2007

Can someone point me to a tutorial on how to search against a SQL Server 2000 using a case insensitive search when SQL Server 2000 is a case sensitive installation?
 
thanks in advance.

View 3 Replies View Related

HELP! Case Insensitive Database On Case Sensitive Server

Aug 17, 2005

We need to install CI database on CS server, and there are some issueswith stored procedures.Database works and have CI collation (Polish_CI_AS). Server hascoresponding CS collation (Polish_CS_AS). Most queries and proceduresworks but some does not :-(We have table Customer which contains field CustomerID.Query "SELECT CUSTOMERID FROM CUSTOMER" works OK regardless ofcharacter case (we have table Customer not CUSTOMER)Following TSQL generate error message that must declare variable @id(in lowercase)DECLARE @ID INT (here @ID in uppercase)SELECT @id=CustomerID FROM Customer WHERE .... (here @id in lowercase)I know @ID is not equal to @id in CS, but database is CI and tablenames Customer and CUSTOMER both works. This does not work forvariables.I suppose it is tempdb collation problem (CS like a server collationis). I tried a property "Identifier Case Sensitivity" for myconnection, but it is read only and have value 8 (Mixed) by default -this is OK I think.DO I MISS SOMETHING ????

View 4 Replies View Related

Doing A Case-sensitive Query In A Case-insensitive Database

May 29, 2008

I am working in a SQL server database that is configured to be case-insensetive but I would like to override that for a specific query. How can I make my query case-sensitive with respect to comparison operations?

Jacob

View 5 Replies View Related

Transact SQL :: Upper Case To Lower Case Conversion

May 4, 2015

I have column with value of all upper case, for example, FIELD SERVICE, is there anyway, I can convert into Field Service?

View 7 Replies View Related

Can You Use Replication From A Case Sensitive Db To A Case Insensitive Db?

Aug 19, 2007

I am curious with using replication in sql server 2005 one way from db A (source) replicating to db B(destination) in which db A has a collation of CS and db B has a collation of CI.  Will there be any problems with this scenario? Thanks in advance! 

View 2 Replies View Related

Problem Using Result From CASE In Another CASE Statement

Nov 5, 2007

I have a view where I'm using a series of conditions within a CASE statement to determine a numeric shipment status for a given row. In addition, I need to bring back the corresponding status text for that shipment status code.

Previously, I had been duplicating the CASE logic for both columns, like so:




Code Block...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,
shipment_status_text =
CASE
[logic for condition 1]
THEN 'Condition 1 text'
WHEN [logic for condition 2]
THEN 'Condition 2 text'
WHEN [logic for condition 3]
THEN 'Condition 3 text'
WHEN [logic for condition 4]
THEN 'Condition 4 text'
ELSE 'Error'
END,
...remainder of SQL view...






This works, but the logic for each of the case conditions is rather long. I'd like to move away from this for easier code management, plus I imagine that this isn't the best performance-wise.

This is what I'd like to do:



Code Block
...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,


shipment_status_text =

CASE shipment_status

WHEN 1 THEN 'Condition 1 text'

WHEN 2 THEN 'Condition 2 text'

WHEN 3 THEN 'Condition 3 text'

WHEN 4 THEN 'Condition 4 text'

ELSE 'Error'

END,
...remainder of SQL view...


This runs as a query, however all of the rows now should "Error" as the value for shipment_status_text.

Is what I'm trying to do even currently possible in T-SQL? If not, do you have any other suggestions for how I can accomplish the same result?

Thanks,

Jason

View 1 Replies View Related

Case Sensitivity When A User Enters Data Into The Database. How To Deal With Case Sensitivity.

Sep 6, 2007

I am working on a C#/asp.net web application. The application has a text box that allows a user to enter a name. The name is then saved to the database.
Before the name is saved to the database, I need to be able to check if the name already exists in the database. The problem  here is that what if the name is in the database as "JoE ScMedLap" and somoene enters the name as "Joe Schmedlap" which already exists in the database,but just differs in case.
In other words how do deal with case sensitiviy issues.

View 2 Replies View Related

Restore Of Case Insensitive Database To A Case Sensitive Database - SQL Server 2000

Jul 20, 2005

Yesterday I received a response to my CI/CS Collation problem and therecommendation was to try and restore a CI Collation database to a CSCollation database. After creating a blank CS database a full restore(Force restore over existing database) does change the Collation toCI. I'm unsure as to how I can restore without changing theCollation. Any suggestions?

View 2 Replies View Related

Need Help With A CASE When

Aug 28, 2007

I just can't get the case to work for me in this view.
CASE WHEN Isnull(dbo.Payments.AmountPaid,'No') THEN 'No' WHEN dbo.Payments.AmountPaid >0 THEN 'Yes' END AS Payment_StatusThe error I get is:
An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'.
ALTER VIEW [dbo].[AffiliationPayments]ASSELECT     dbo.Affiliations.AffiliationType, dbo.Affiliations.AffiliationDescription, dbo.Affiliations.AffiliationEnd, dbo.Payments.PaymentDate,                       dbo.Payments.AmountPaid, dbo.Affiliations.Client_ID,CASE WHEN Isnull(dbo.Payments.AmountPaid,'No') THEN 'No' WHEN dbo.Payments.AmountPaid >0 THEN 'Yes' END AS Payment_StatusFROM         dbo.Affiliations LEFT OUTER JOIN                      dbo.Payments ON dbo.Affiliations.Client_ID = dbo.Payments.Client_ID 

View 2 Replies View Related

CASE,IF,etc??

May 8, 2006

I have a deadline that is set ahead of time and will not change. I need to insert this deadline for all new users, so I want to set in the DB and I have tried to insert the deadline into the db using a case(below)




Error -2147217900


Error -2147217900

 

Incorrect syntax near the keyword 'CASE'.
ALTER            PROCEDURE prcStartWeeks                 @UserID     VARCHAR(50)  ASSET NOCOUNT ONDECLARE @Result INTDECLARE @ID   INTDECLARE @Weeks   INTDECLARE @DeadLine   VARCHAR(8)SET @ID = 0SET @Weeks = 1SELECT @ID = ISNULL((SELECT peID from tblUserInfo where UserID = @UserID),0)IF @ID = 0    BEGIN      SELECT @Result = -1   ENDELSE   BEGIN      WHILE @Weeks < 18    BEGIN   CASE @Weeks      WHEN '1' THEN @DeadLine = '5/9/2006'      WHEN '2' THEN @DeadLine = '9/15/2006'      WHEN '3' THEN @DeadLine = '9/22/2006'      WHEN '4' THEN @DeadLine = '9/29/2006'      WHEN '5' THEN @DeadLine = '10/6/2006'      WHEN '6' THEN @DeadLine = '10/13/2006'      WHEN '7' THEN @DeadLine = '10/20/2006'      WHEN '8' THEN @DeadLine = '10/27/2006'      WHEN '9' THEN @DeadLine = '11/3/2006'      WHEN '10' THEN @DeadLine = '11/10/2006'      WHEN '11' THEN @DeadLine = '11/17/2006'      WHEN '12' THEN @DeadLine = '11/21/2006'      WHEN '13' THEN @DeadLine = '11/28/2006'      WHEN '14' THEN @DeadLine = '12/5/2006'      WHEN '15' THEN @DeadLine = '12/12/2006'      WHEN '16' THEN @DeadLine = '12/19/2006'      WHEN '17' THEN @DeadLine = '12/28/2006'   END       INSERT INTO tblUserWeekly(PEID,WeekID,Points,DeadLine)       VALUES(@ID,@Weeks,0,@DeadLine)       SET @Weeks = @Weeks + 1    END         SELECT @Result = 0   END

View 3 Replies View Related

Between W/case

Aug 26, 2002

I want to use case in my date range to get last weeks range. I have a similar proc that uses variables, but my application UI will not allow this in production.

Error incorrect syntax near '=' on line 15

-- begin
select
Company
,Carrier
,Client
,DatePaid
,DateBilled
,cast(PremiumReceived as money)
,cast(PolicyAmount as money)

from view_billing

where
cast(datebilled as datetime) between

(cast(datebilled as datetime) =
case
when datepart(dw,getdate()) = 2 then getdate()-8
when datepart(dw,getdate()) = 3 then getdate()-9
when datepart(dw,getdate()) = 4 then getdate()-10
when datepart(dw,getdate()) = 5 then getdate()-11
when datepart(dw,getdate()) = 6 then getdate()-12
when datepart(dw,getdate()) = 7 then getdate()-13
end)

and
cast(datebilled as datetime) =
case
when datepart(dw,getdate()) = 2 then getdate()-1
when datepart(dw,getdate()) = 3 then getdate()-2
when datepart(dw,getdate()) = 4 then getdate()-3
when datepart(dw,getdate()) = 5 then getdate()-4
when datepart(dw,getdate()) = 6 then getdate()-5
when datepart(dw,getdate()) = 7 then getdate()-6
end

and and company like '%Comp%'
-- end

View 3 Replies View Related

Case

Apr 27, 2005

is it possible to test two fields in a case statement in SQL ?

View 3 Replies View Related

If Else Or Case When, Then

Dec 14, 2004

I have a query using Transact-SQL and it is moderately complex. I was wondering if someone could tell me if it would be better to use IF...ELSE statements or CASE, WHEN...THEN statements.

What I have is something like this...


Code:

SELECT
something something
code = CASE

WHEN something IN (A list of things)
IF something else
THEN 4



I have just though of doing IF...ELSE statements to keep it simply, but this query needs to run in a relative quick time frame. I guess my question is, is IF...ELSE faster or is it faster to use a CASE statement?

View 6 Replies View Related

Case Within Having ?

Sep 30, 2005

Hi all,

I tried the following code and obviously doesnt work, is there a way to simulate a case within having?


CREATE TABLE LTG (Name VARCHAR(10), B1 INT, B2 INT, B3 INT);

INSERT INTO LTG VALUES
('Luis',1,0,0)
INSERT INTO LTG VALUES
('Hector',0,1,0)
INSERT INTO LTG VALUES
('Alejandro',0,0,1)


DECLARE @S INT
SET @S = 1

SELECTName,
SUM (B1),
SUM (B2),
SUM (B3)
FROMLTG
GROUP BY Name
HAVING
CASE @S
WHEN 1 THEN SUM (B1) != 0
WHEN 2 THEN SUM (B1) != 0
WHEN 3 THEN SUM (B1) != 0
END

Thanks for your help

Luis Torres

View 2 Replies View Related

Sum With Case

Nov 1, 2005

How do I get 1 sum out of the following varchar fileld?

Select 'USER_LEVEL' =
CASE
WHEN USER_LEVEL = 'Unlimited' THEN 0
ELSE USER_LEVEL
END

From Table1

View 1 Replies View Related

Help With CASE And LIKE

Sep 4, 2007

Hello guys! i'm having problem with my stored procedure..can anybody please help me.
I have a stored procedure below that is successfully executed/saved/"Compiled"(whatever you called it) but when I try to use it by supplying value to its paramaters it throws an error (Please see the error message below). I suspected that the error occurs from line with the Bold Letters becuase "@SeacrhArg" variable is of type varchar while columns "Transac.Item_ID" and "Transac.Item_TransTicketNo" is of type Int. What you think guys?

ERROR:

Msg 245, Level 16, State 1, Procedure sp_Transaction_Search, Line 9
Syntax error converting the varchar value 'Manlagnit' to a column of data type int.



STORED PROCEDURE:

USE [RuslinCellPawnShoppeDB]
GO
/****** Object: StoredProcedure [dbo].[sp_Transaction_Search] Script Date: 09/04/2007 08:48:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Transaction_Search]
@SeacrhArg varchar(20),
@SearchBy varchar(20),
@TransType varchar(20),
@FromDate datetime,
@Todate datetime
AS
BEGIN
SELECT Customer.Customer_LastName,Customer.Customer_Middl eInitial, Customer.Customer_FirstName, Customer.Customer_Address,
Items.Item_Description,Items.Item_Principal, Transac.ItemTrans_Date_Granted, Transac.ItemTrans_DateCreated,
Transac.ItemTrans_Status, Transac.Item_ID,Transac.Item_TransID,Transac.Item_ TransTicketNo


FROM RCPS_TF_ItemTransaction Transac
INNER JOIN RCPS_Customer Customer
ON Transac.CustomerID = Customer.CustomerID
INNER JOIN RCPS_Items Items
ON Items.ItemID = Transac.Item_ID

WHERE
CASE
WHEN @SearchBy = 'FirstName' THEN Customer.Customer_FirstName
WHEN @SearchBy = 'LastName' THEN Customer.Customer_LastName
WHEN @SearchBy = 'Item ID' THEN Transac.Item_ID
WHEN @SearchBy = 'Ticket No' THEN Transac.Item_TransTicketNo
END
LIKE @SeacrhArg AND

Transac.ItemTrans_DateCreated BETWEEN @FromDate AND dateadd(day,1,@Todate) AND
(
(@TransType = 'Pawned' AND Transac.ItemTrans_Status = 1) OR
(@TransType = 'Renewed' AND Transac.ItemTrans_Status = 2) OR
(@TransType = 'Redeemed' AND Transac.ItemTrans_Status = 3) OR
(@TransType = 'Sold' AND Transac.ItemTrans_Status = 5)
)
END


CALL STORED PROCEDURE

USE [RuslinCellPawnShoppeDB]
GO

DECLARE@return_value int

EXEC@return_value = [dbo].[sp_Transaction_Search]
@SeacrhArg = '%man%',
@SearchBy = 'LastName',
@TransType = 'Pawned',
@FromDate = N'9/01/2007 12:00:00 AM',
@Todate = N'9/6/2007 12:00:00 AM'

SELECT'Return Value' = @return_value

GO

View 4 Replies View Related

Case Within A Case

Jan 21, 2004

I was just wondering if it is possible to have nested case statements (case within a case). If so, what would the syntax be? Here is what I have now:

case when isdate(my_date)=1 then convert(char(10),convert(datetime,cast(my_date as varchar(8))),101)
else null end "my_date"

I need to put another case outside this one. Any ideas?
Thanks.

View 14 Replies View Related

CASE Within A WHERE

Mar 4, 2015

I'm trying to put something like below within a where clause.I have a parameter @Units which can have one of the values on YES, NO or BOTH. The Idea is

if @Units = Yes I only want USER_DEFINED_DATA.CAD213 = 'on'
if @Units = No I only want USER_DEFINED_DATA.CAD214 = 'on'
IF @UNITS = 'Both' .... I want both

[code]....

View 2 Replies View Related

Help With IF/ELSE Or Case

Apr 8, 2008

I am trying to write the following using either an if/else or case, any help would be aprreciated.

Set the OPPORTUNITY.account_manager_override to false when the COMPANY.account_manager = 'xxx'. In all other cases, set the OPPORTUNITY.account_manager_override to true. Sounds simple enough....

This may help:
select opportunity_name, o.account_manager_id opp_manager, o.account_manager_override opp_override,
co.account_manager_id company_manager, co.account_manager_override company_override
from opportunity o
inner join company co on co.company_id = o.company_id
where co.account_manager_id = xxx

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved