How Can I Use A Conditional Where Clause In Sql Statment

May 15, 2007

I have a store procedure where i need to use conditionel where clause

View 2 Replies


ADVERTISEMENT

Stored Procedure With Conditional Select Statment

Feb 6, 2007

Hi all,
I created a stored procedure which perfoms a simple select like this:
CREATE  PROCEDURE Reports_Category
( @loc varchar(255), @pnum varchar(255)           )
AS          declare @vSQL nvarchar(1000)         set @vSQL = 'SELECT ' +  @loc + ', '  + @pnum +  ' FROM  Category '         exec sp_executesql @vSQL
RETURNGO
It takes field names as parameters. What happens is that when I supply value to only one parameter, the procedure gives error as it is expecting both values. Is there a way to make these parameters work like an 'OR' so that the procedure returns a dataset even if there is only one value supllied.
 Please help,
Thanks,
bullpit

View 7 Replies View Related

Problem With Proc And Multi Where Clause Statment.

Apr 17, 2008

I am trying to make a proc with this code:

create proc AddImages (
@Error smallint output, @ImageName varchar(50), @Game varchar(25), @SubSet varchar(25), @FullSubSet varchar(75), @Width smallint, @Height smallint, @AltText varchar(50)
) as
declare @AbbreviationExists varchar(25), @ImageExists varchar(25)

set @AbbreviationExists = (select Short from Eaglef90.Abbreviations where Short = @SubSet)
set @ImageExists = (select ImageName, Game, SubSet from Eaglef90.Images where ImageName = @ImageName and Game = @Game and SubSet = @SubSet)
set @Error = 0

if @AbbreviationExists is null
insert Eaglef90.Abbreviations (Short, Long) values (@SubSet, @FullSubSet)

if @ImageExists is null
insert Eaglef90.Images (ImageName, Game, SubSet, Width, Height, AltText) values (@ImageName, @Game, @SubSet, @Width, @Height, @AltText)
else
set @Error = 1

but when I hit execute in Query Analizer I recive this error:

Msg 116, Level 16, State 1, Procedure AddImages, Line 7
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Line 7 has this code on it:

set @ImageExists = (select ImageName, Game, SubSet from Eaglef90.Images where ImageName = @ImageName and Game = @Game and SubSet = @SubSet)

Does anyone know what is wrong with that select statement?

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

View 4 Replies View Related

Conditional WHERE Clause

May 8, 2006

Hi,
[SQL 2005 Express]
I would like a DropDownList to be populated differently depending on the selected value in a FormView.
If the FormView's selected value (CompanyID) is 2, then the DropDownList should show all Advisers from the relevant Company.  Otherwise, the DropDownList should show all Advisers from the relevant Company where the TypeID field is 3.
Here is the SQL for case 1:
SELECT    AdviserID,    AdviserName FROM    Advisers WHERE    (CompanyID = @CompanyID).
Here's the SQL for case 2:
SELECT    AdviserID,    AdviserName FROM   Advisers WHERE    (CompanyID = @CompanyID) AND    (TypeID = 3).
Here's my best (failed) attempt to get what I want:
SELECT    AdviserID,    AdviserName FROM   Advisers WHERE    IF @CompanyID = 2 THEN      BEGIN         (CompanyID = @CompanyID)      END   ELSE      BEGIN         (CompanyID = @CompanyID) AND          (TypeID = 3)      END
I've also tried:
SELECT    AdviserID,    AdviserName FROM   Advisers WHERE    CASE @CompanyID       WHEN 2 THEN (CompanyID = @CompanyID)      ELSE (CompanyID = @CompanyID) AND          (TypeID = 3)   END
and 
SELECT    AdviserID,    AdviserName FROM   Advisers WHERE    CASE WHEN (@CompanyID = 2) THEN (CompanyID = @CompanyID)      ELSE (CompanyID = @CompanyID) AND (TypeID = 3)   END
I'd be very grateul to know (a) what the correct syntax for this is and (b) if it can be achieved using a parametised query, rather than a stored procedure.
Thanks very much.
Regards
Gary

View 7 Replies View Related

Conditional Where Clause

Dec 14, 2007

Hi all,

I have a table QT defined as

CREATE TABLE [dbo].[QT](
[Query] [nvarchar](50) NULL,
[Frequency] [int] NULL
) ON [PRIMARY]

Now based on a parameter I want to include a predicate in the select statement.

Basically I am trying to write something similar to the one below but possible only usinf one select statement.

if @queryString is null then

select query ,sum(frequency)

from qt

group by query
else
select query ,sum(frequency)

from qt

group by query
where query = @queryString.

Now is there a way to achieve this thing without using two separate select? The actual code I am trying to write is much bigger and I am trying to see if there is more compact way of expressing things.

Thanks
Aye.

View 7 Replies View Related

Conditional Where Clause Possible?

Jun 11, 2007

Is it possible to use a conditional statements in a where clause?



IE: I have 3 paramaters that may or may not be filled.



I would like to do something along the lines of...





Select * From (tables)

WHERE

If @param1 has value

Begin

'run this where statement

if @Param2 has value

'add this to the where clause

if @param3 has value

'add this to the where cluase



View 10 Replies View Related

Conditional If In Where Clause

Oct 8, 2007



Can I use "CASE WHEN ... THEN ... ELSE ... END" in the where clause of a SQL statement? I have sucessfully used it in the select portion of my statment but I would also like to use conditional criteria in the WHERE portion. Any advice is greatly appreciated.

View 7 Replies View Related

Using Conditional Statement In Where Clause

Mar 26, 2012

I'm trying to use a conditional statement in the where clause.

Here is my table
UID Amount ID PID Amount2
1 30000 8064 NULL NULL
2 30000 8042 8064 30000

What I'm trying to achieve:

If Amount = Amount2 for UID 2 then show UID 1

View 4 Replies View Related

Performance Issue Using Conditional WHERE Clause

Jan 25, 2008

Consider the following two functionally identical example queries:Query 1:DECLARE @Name VARCHAR(32)SET @Name = 'Bob'SELECT * FROM EmployeesWHERE [Name] = CASE WHEN @Name IS NULL THEN [Name] ELSE @Name ENDQuery 2:SELECT * FROM Employees WHERE [Name] = 'Bob'I would expect SQL Server to construct an identical QEP under the hoodfor these two queries, and that they would require essentially thesame amount of time to execute. However, Query 1 takes much longer torun on my indexed table of ~300,000 rows. By "longer", I mean thatQuery 1 takes about two seconds, while Query 2 returns almostinstantly.Is there a way to implement a conditional WHERE clause withoutsuffering this performance hit? I want to avoid using the IF...THENmethod because I frequently require several optional parameters in theWHERE clause.Thanks!Jared

View 6 Replies View Related

Conditional Where Clause W/ Case Statement Possible?

Sep 25, 2007

Greetings,

After many hours search many forums and many failed experiments, I figure it's time to turn to the experts.

I need to execute a query that changes the returned data based upon a parameter's value. In my example below, the lob field contains both text values and nulls.


SELECT uniqueID, lob, xdate
FROM mytable
WHERE

CASE WHEN @myparam = 'ALL'

THEN

xdate >= '2007-09-01'
ELSE

xdate >= '2007-09-01' or
lob = @myparm
END

I've experimented with various forms of the LIKE function, checking for null/not null and keep coming up blank.

I thought about using an IF statement and creating different versions of the entire statement, however, in real-life I need to do this with four fields using four parameters (one for each field). The permutations are a little too much.

Any ideas?

Rob

View 8 Replies View Related

Transact SQL :: How To Get Results Based On Conditional Where Clause

Jul 14, 2015

My source table has two columns... Policynum and PolicyStartdate and data looks like..
.
Policynum              PolicyStartdate
123G                       01/01/2012    
456D                       02/16/2012     
789A                       01/21/2012
163J                       05/25/2012

Now my output should return based on 3 parameters..

First two parameters are date range... let say @fromdt and @todt

Third parameter is @policynum

Scenario-1: Enter dates in date range param and leave policynum param blank
Ex: policystartdate between '01/01/2012 and '01/31/2012'.... It returns 1st and 3rd rows from above in the output

Scenario-2: enter policy num in policynum param and don't select any dates
Ex:  policynum ='456D'     It returns 2nd row in the output

Scenario-3: Select dates in date range param and enter policynum in param
Ex: policystartdate between '01/01/2012 and '01/31/2012' and policynum
='163J'.  it should return only 4th row even though dates were selected(Override date range when policynum is entered in param and just return specified policynum row in the output)

I need t-sql code to get above results.

View 12 Replies View Related

Conditional Where Clause Depending On Input Parameter

May 5, 2008

I am trying to merge 2 pieces( i.e procedures , or stored proc) of sql together.

My simple QueryA

SELECT colA, colB, colC, colD
FROM tableA
WHERE
colD IS NOT NULL

My simple QueryB

SELECT colA, colB, colC, colD
FROM tableA
WHERE
colC IS NOT NULL

I am trying to merge these 2 pieces if sql together by passing a input parameter which will decide which query to run. So if I pass an input parameter QueryA , it will run QueryA. If I pass an imput parameter QueryB, it will run QueryB.

Essentially both my queries are the same besides the where condition. Is there a way to merge it into one query (and not use if conditions and make my storedproc long) and apply the where condition depending on what input parameter is passed in ?

I know it can be done using dynamic SQL construction. But any other ways ?

Also can someone also give in the solution in PL/SQL.

Thanks a bunch.

Jaffery.

View 7 Replies View Related

Conditional Where Clause With Comma Delimited String And Link Table

Jul 24, 2007

 I have 3 tables:tblUsersuserID int PK(...)tblSportsSportID int PK(...)tblUsersAndSports  (contains the link between users and sports..a single user may have multiple entries in this table)Usercode intSportID intNow I want a stored proc that enables visitors to search on all user that have a specific sportID.The SportIDs to search on are in the var @sports as a comma delimited string,like '3,6,7'@sports may also be null (or an empty string if that is more convenient for building the SQL) when a visitor does not want to search on any of the sports a user practices, in that case no selection based on the sport criteria should be done, so ONLY filter on sports when the value of @sports is not nullpseudo code:select * from tblUserswhere   if @sports not null    user.sports in @sportsand username=@usernameand age=@agehelp is greatly appreciated!

View 10 Replies View Related

Insert And Delete Statment In One Statment

Jan 18, 2008



Hi all

I have two tables I need to Select the record from the First table and insert them into the second table and delete the record from the first table how can i do that with the SQL Statment?

Thank you in advance .....

Regards,
sms

View 15 Replies View Related

Conditional Subscription / Conditional Execution Of Report

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

Conditional Formatting - Not So Conditional??

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

Transact SQL :: How To Create UNION Clause With Two Queries That BOTH Have WHERE Clause

Nov 4, 2015

I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?

I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....

I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.

Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?

View 13 Replies View Related

Help With Sql Statment?

Jul 11, 2006

I cant find the error in this sql statment. What I want it to do is return everything from book, locations.locationname, and author.fullname. I want to to only return the first 10 rows. This will be used in paging, so, eventually it will be first 10, than 11-20, etc. Heres what I have, without the row limitSELECT RowNum, book.*, locations.LocationName, author.fullname FROM (SELECT book.*, locations.LocationName, author.fullname ROW_NUMBER() OVER(ORDER BY book.id) as RowNum FROM book INNER JOIN Author ON book.AuthorID = Author.ID OR book.AuthorID2 = Author.ID OR book.AuthorID3 = Author.ID OR book.AuthorID4 = Author.ID OR book.AuthorID5 = Author.ID INNER JOIN Locations ON book.LocationID = Locations.ID WHERE (Author.FullName LIKE '%' + @Search + '%') OR  (Author.FirstName LIKE '%' + @Search + '%') OR (Author.LastName LIKE '%' + @Search + '%')) as BookInfo

View 6 Replies View Related

SQL Not Statment Help

Jul 11, 2007

I have two tables. One for videos and one for a "block list"Videos : VideoID UserID VideoURL VideoTitle etc. VideoBlockList :VideoID UserIDI have a datalist to show the videos but i want for the datalist to miss out any videos that a user can not see.So if the block list has "VideoID" = 2 and UserID = 1 if user 1 does a search then the search will skip out the video 2.how is this done? i tryed to do it using a specific / custom SQL statment but it errored cos the NOT value conflicted with the search... any ideas? thanks in advance si! 

View 19 Replies View Related

Sql Statment

Nov 13, 2007

Select @ID  = top 1 ID From Contents Where Contents.UserID=@UserID And Contents.Status=4  AND Contents.InEdit=1
why it dosn't get back the ID vlaue but when i remove the top1 one its work well why
 

View 5 Replies View Related

Need Help With Sql Statment

Apr 14, 2008

Hi I have two tablesTABLE 1 named problemas with the field N_problem (numeric)Tabe 2 named  resolvidos with the field Resol (numeric)
How can i select all the records from table 1 who are not in Tabe2 ?
Thank you
mario

View 2 Replies View Related

IF Statment

May 16, 2002

Does anyone know how to write a statement in SQL Server that is similiar to Microsoft Access's IIF function. Im not quite sure how the syntax works in a SQL Server IF statement. Thanks!

View 1 Replies View Related

If Statment

Apr 2, 2007

need help with if statment in a stored procedure
here is what i have and it does not work

Code:


CREATE PROCEDURE Get_ckcompany
@cknum int,
@company varchar

AS

if (@cknum is not null) and (@company is null)
select *
from PMTK_tbl
where Company = @company

else if (@compnay is not null) and (cknum is null)
select *
from PMTK_tbl
where check_Num = @cknum
else
select *
from PMTK_tbl
where Check_Num = @cknum and Company = @company
end if

GO

View 2 Replies View Related

Need Help With An Sql Statment

May 1, 2008

I have a database used for a point of sale system
it has a main table with the total amount of a check - the tip
the tip is save in a different table that holds payments the issue is i need to make a statement that finds checks based of the total with the tips added. the table that holds tips can have more then one tip because you can have multiple payments on a check.

The statement i have now is like this

select jc.total + sum(jp.tip) as total from jc innerjoin jp on key
where total <= @max and total >= @min
group by jc.total

but this statement uses total before the tip is added. I am not sure how else to do this any help would be great

View 4 Replies View Related

SQL Statment

Jun 1, 2006

select zsong.song, zsong.trk
from zsong, zfmt
where zfmt.upc='13117' AND zfmt.muzenbr=zsong.muzenbr


That staement works, but if the UPC code is repeated twice it returns the results twice (i want to only read it once)...

how do i make it only return the first result or the last one or whatever (since they all reference the same thing)

View 4 Replies View Related

Use Statment

Jun 9, 2008

Posted - 06/09/2008 : 11:10:47
--------------------------------------------------------------------------------

trying to run the following script.

use 001
select *
from imitmidx_sql

Get incorrect syntax near 001

If I change the use 001 to Data_58 it works fine. Do I need special syntax when the database name is 001??

View 1 Replies View Related

SQL Statment

Feb 18, 2007

Hello All,

I imported a excel file from SSIS and created a table called Lockbox.
To avoid the user from having to change the excel file -it is being imported as is.
I only need 4 fields: [Contract ID] , [Check Number], [Owner ID], [Site ID]

The table I need to import to Transaction has Diffrent Column Names -ex-CustomerID, ResortID.
The columns are in diffrent order.
And I need to add more information into them like UserID = 'Hwells', Trantype = 'MF'
and convert to a diffrent data type [Site ID] to text.

Is their a sql statment that can do this?

SQL2005

Thanks for your time

View 2 Replies View Related

Sql Statment

Aug 17, 2007

hi,

I Have a following SP running on sql 2005:-

DECLARE @MONTHNO int
DECLARE @DB CHAR (8)
DECLARE @CR CHAR (8)
SET @MONTHNO = 2
SET @DB = 'DB_'+CONVERT(NCHAR(2),@MONTHNO)
SET @CR = 'CR_'+CONVERT(NCHAR(2),@MONTHNO)
SELECT (acctno),@CR from bmaster

. The table has a 12 field with cr_1 ... to 12

. the sql can’t understand the @cr the column papers "with No column name'

View 2 Replies View Related

SQL Statment By Asp.net

Oct 8, 2006

is there any chance to use the follwoing script to add new record to the database

"

rs.addnew

rs.("name")="Scot"

rs.update

"

insted of the old fashion " insert into ...."





View 1 Replies View Related

SELECT Statment

Mar 20, 2007

Hello, Is there an SELECT statement to just return the last 100 row in my tables?  I have about 500 rows in my tables and I only need the info on the last 100 rows.
 Thanks
Steve

View 4 Replies View Related

A SELECT Statment, Please Help Me

Oct 2, 2007

hello,
i have a page "Picture.aspx?PictureID=4"
i have a FormView witch shows details about that picture and uses a stored procedure with input parameter the "@PictureID" token from query string
the Pictures table has among other rows "PictureID", "UserID" - uniqueidentifier - from witch user the picture belongs to
i have a second FormView on the same page, witch should show "other pictures from the same user" and uses a Stored Procedure
how should i write that stored procedure...frist to take the UserID from the picture with PictureID=4, then to pass it as input parameter and select the pictures witch has as owner the user with that UserID, and if can be done, to avoid showing the PictureID=4 again
a solution should be to add at querry the UserID too, but i want to avoid that
any sugestion is welcomed, please help me
THANKS

View 5 Replies View Related

Optimize SQL Statment

Mar 9, 2008

Hello, I have three sql select statments I would like to combine into one.  I have created a statment that works but I am not sure if it is a good solution performance wise.  Is there a better way to run this query?
Thanks Very Much!!
 if exists (Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight
From SiteIndex Where update_freq = 0 and nextupdate < GetDate() Order By nextupdate)
Begin
Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight
From SiteIndex Where update_freq = 0 and nextupdate < GetDate() Order By nextupdate
End
else
if exists (Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight
From SiteIndex Where nextupdate < GetDate() Order By importance desc)
begin
Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight
From SiteIndex Where nextupdate < GetDate() Order By importance desc
end
else
Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight
From SiteIndex Order By nextupdate 

View 1 Replies View Related

Sql Insert Statment

Mar 28, 2008

i have 3 tables  in sql the relation between them is one to one
person ==> employee ==> sales_department_stuff&
another relation ( one to one )between
person(the same as above ) ==> customer 
i want to put person id in employee not repeated in customer becase all (employee and customer is a person ) 
sooooooo
i put this sql statment to try to insert person id in employee then in sales_department_stuff  table to complet his ordersinsert into Person (Person_Name_Ar,Person_Name_En) values ('aaaaa',' ssssssss')select @@identity from Personinsert into Employee values (@@identity,'1') select @@identity from Personinsert into sales_department_Staff values (@@identity,'1')select @@identity from Personinsert into Driver  values (@@identity,'2','2222','1/1/2005','5') /* SET NOCOUNT ON */
      RETURN
 
 the first 3 statment is run and success
but underline stetment return error that
he can insert null value in id field ,,,,,,,,,,,,,,,,
he can compile @@identity in these statment ??????????????
plz help me
 
  
 

View 7 Replies View Related







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