T-SQL: Different SELECT Executed Depending On Condition.

Apr 30, 2004

I'm trying to execute a different SELECT statement depdning on a certain condition (my codes below). However, Query Analyzer complains that 'Table #Endresult already exists in the database', even though only one of those statements would be executed depending on the condition. Any ideas as to a work around? I need the result in an end temporary table.








IF @ShiftPeriod = 'Day'


SELECT * INTO #EndResult FROM #NursesAvailable WHERE CanWorkDays = 1


ELSE IF @ShiftPeriod = 'Night'


SELECT * INTO #EndResult FROM #NursesAvailable WHERE CanWorkNights = 1


ELSE IF @ShiftPeriod = 'Evenings'


SELECT * INTO #EndResult FROM #NursesAvailable WHERE CanWorkEvenings = 1


ELSE


SELECT * INTO #EndResult FROM #NursesAvailable

View 1 Replies


ADVERTISEMENT

Execute SendMail Depending On A Condition

Apr 17, 2008



Hello All,

I have an Execute SQL Task and a SendMail in the control flow of my package.

If the Execute SQL Task inserts records in the Database, I want the SendMail task to be executed.

But if Execute SQL Task does not inserts even a single record in the Database, I dont want the SendMail task to be executed.

How can I achieve this...

Thanks,
Kapadia Shalin P.

View 1 Replies View Related

INSERT-SELECT Depending On The Select:ed Order

Aug 15, 2006

I'm doing a INSERT...SELECT where I'm dependent on the records SELECT:ed to be in a certain order. This order is enforced through a clustered index on that table - I can see that they are in the proper order by doing just the SELECT part.

However, when I do the INSERT, it doesn't work (nothing is inserted) - can the order of the records from the SELECT part be changed internally on their way to the INSERT part, so to speak?

Actually - it is a view that I'm inserting into, and there's an instead-of-insert trigger on it that does the actual insertions into the base table. I've added a "PRINT" statement to the trigger code and there's just ONE record printed (there should be millions).

View 3 Replies View Related

Execute Select Depending On The Input Parameters

Nov 14, 2006

Hello, a question please. Could anyone say me if I can create a store procedure with 2 'select's statements into. Besides, I'd want to know if I can execute a "select" depending on input parameters.

Something like this:

create storeproc
Param1, param2

if param1 <> null

Select *
from table
where id = param1

else

Select *
from table
where id = param2

end if

Thanks in advance....







View 2 Replies View Related

How To Get Recordset From Only Last SELECT Stmt Executed???

Feb 14, 2005

How to get recordset from only last SELECT stmt executed???

this is part of my code.. in T-Sql


Create proc some mySp
...
AS
...
set nocount on
if (@SelUserID is null)
select 0
else if (@SelUserID = @userID)

select 1

else
begin

select * -- select a.
from dms_prsn_trx_log
where @incNo = ult_incid_no and
prsn_trx_no = 10 and
trx_log_txt = @logText
if (@@rowcount != 0)
set @isForwardedByUser = 1
select 2 -- select b. I NEED This value.

end
set nocount off
GO

here it executes select a, b.
But, I want mySp return last executed select result. which is here "select 2"

I thought set nocount ON does that, but it doesn't.
How can I do???

View 2 Replies View Related

Can A Stored Procedure Be Executed From Within A Select Statement?

Dec 2, 2005

Can a stored procedure be executed from within a select statement?

Given a store procedure named: sp_proc

I wish to do something like this:

For each row in the table
execute sp_proc 'parameter1', parameter2'...
end for
...but within a select statement. I know you can do this with stored functions, just not sure what the syntax is for a stored procedure.

View 2 Replies View Related

Select Permission Obligatory Need To Execute Procedure (that Has Executed Permssion)

Jan 30, 2006

I've all procedures running over EXECUTE permission. They're running properly without the SELECT or DRY permissions on involved tables.
But some procedures of above cited,  in a particular tables or particular the procedure don't runs properly with out enabling SELECT permission on involved tables.
The EXECUTE permision runs overs other permissions on tables if the're not implicit denied, it's the best segurity practice. Then what is happen?? why need extra SELECT permision on some tables ?. The usser, and function role are ok.
You troube the same, some help please :)

View 7 Replies View Related

Same DTS Fails Executed As Job ,but Run Fine When Executed From DTS Designer

Mar 13, 2002

I created DTS a while ago and placed in job to run once a day (it worked fine for 3 months)
2 days ago I changed sa password and now job fails with error (Login failed for user 'sa'.), but it run fine from DTS !!!


1. My DTS created with domain Account DomainSVCSQL2000( sa rights and local admin)
2. SVCSQL service use DomainSVCSQL2000 to run
3. SVCSQL agent use DomainSVCSQL2000 to run
4. DTS use 'osql -E

Where should look for reference to sa ?







Executed as user: MONTREALsvcsql2000. DTSRun: Loading... Error: -2147217843 (80040E4D); Provider Error: 18456 (4818) Error string: Login failed for user 'sa'. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code 1. The step failed.

View 5 Replies View Related

SELECT UNION SELECT Condition

Dec 14, 2006

let say i got such condition

INSERT INTO TABLE
SELECT
WHERE XX NOT EXISTS (SELECT 1 FROM TABLE)
UNION
SELECT
WHERE XX NOT EXISTS (SELECT 1 FROM TABLE)

do you think that mssql will produce error or problem?
from what i heard it will.

View 1 Replies View Related

&#39;IF&#39; Condition On Select Statement?

Dec 8, 1998

Can you implement the same type of feature in SQL as MS Access uses with it's "immediate if"
or iif() function? In other words can you look at a specific row and change the contents
of one field based on the contents of another field in the same row?

I am trying to create a view like the the following:

CREATE VIEW AS Test
SELECT
name,
city ,
IF city = 'NY' state ELSE country
FROM address_table

The view's 3rd field will be either [state] or [country] depending on the contents of [city]

Thanks in advance!

..Marisa

View 3 Replies View Related

Select Condition From Log Table

Jun 1, 2006

Hi guys

Im not even sure how to word this right, but how do I compare one element(unsure word number 1) namely Filename, from a list of results, and use that value as a variable for another condition in the same select... I think?!

So if I get a list of 50 filenames (from a log table), some might be double.
If the file was successfully processed, it will log that filename and the status as OK and if not then BAD.
The file will be modified and then reprocessed until OK. Each time the file was processed, it will log the filename and status.
I then run a statement that shows all the files that were BAD (from the log table) and it will bring up then filename and BAD status.
What I want to do is check the status' and if the file is BAD, check if there is another logged entry with that filename and status is OK, if found then not produce output as Im only looking for the files that status' are BAD with no OK status for any of the logged entries for that filename.

Is that understandable? If not then Ill try reword it.

The help is much appreciated!
Justin

View 2 Replies View Related

If Condition In Select Statement...

Apr 3, 2007

Hi,
I need to write an if condition in SELECT statement. Below is the code for the same. But its throwing error. Can some refine the code below.




SELECT tblCustomer.Customer_LegalName,
(IF (tblCustomer.IsNRACustomer = TRUE) SELECT tblCustomer.Customer_PassportNo ELSE
ISNULL(tblCustomer.Customer_TaxId, tblCustomer.Customer_PassportNo)) AS TAXID,

tblCustomer.Customer_DoingBusinessAs, tblSeed_EDDCategory.CategoryName, '2' AS DCS, tblUser_OfficerCode.User_OfficerCode,
tblCustomer.Customer_AreaId, tblCustomer.Customer_BranchId, CONVERT(VARCHAR(11), tblCustomer_EDDCategory.CreateDate)
AS CreateDate, tblSeed_EDDCategory.EDDCategoryId, tblCustomer_EDDCategory.Category_CreateEmpId,
tblCustomer_EDDCategory.CustCatId, tblCustomer.Customer_Id, tblSeed_Area.AreaName, tblSeed_Employee.Name,
tblUser_OfficerCode.User_OfficerName, tblCustomer.Customer_TaxId, tblCustomer.IsNRACustomer,
tblCustomer.Customer_PassportNo
FROMtblCustomer INNER JOIN
blCustomer_EDDCategory ON tblCustomer.Customer_Id = tblCustomer_EDDCategory.CustomerId INNER JOIN
tblSeed_EDDCategory ON tblCustomer_EDDCategory.EDDCategoryId = tblSeed_EDDCategory.EDDCategoryId INNER JOIN
tblSeed_Employee ON tblCustomer.Customer_CreateEmpId = tblSeed_Employee.EmployeeId INNER JOIN
tblUser_OfficerCode ON tblCustomer.Customer_CreateEmpId = tblUser_OfficerCode.EmployeeId INNER JOIN
tblSeed_Area ON tblCustomer.Customer_AreaId = tblSeed_Area.AreaId


Thanks,
Rahul Jha

View 2 Replies View Related

Select Max Record With A Condition

Sep 17, 2012

I am wondering how to select the Max date within a table where a timestamp is within 2 second of the other

IDTimestamp
1NULL
209/17/2012 11:04:32
309/17/2012 11:05:44
409/17/2012 11:05:45

So I need a query to return

IDTimestamp
409/17/2012 11:05:45

as 11:05:45 is the max of 09/17/2012 11:05:44 and 09/17/2012 11:05:45 and they are within 2 seconds of eachother.

View 6 Replies View Related

Condition In Select Query

Jun 12, 2008

create function stzipcustinfo

( @campaign varchar(50), @startdate datetime, @enddate datetime,@audience_type char(10) )


RETURNS TABLE

AS

RETURN


(Select distinct t.campaign,t.Sopnumbe,t.Custnmbr,t.Custname,

t.Address1,t.Address2,t.City,t.State,t.Country,t.Zipcode, t.Phone,sum(t.totalquantity) as Totalquantity,

t.Audience_type

from

(

select distinct

v.sopnumbe, v.campaign, v.custnmbr, v.custname, v.address1, v.address2,v.city,v.state,v.country,v.zipcode,

v.Phone, v.totalquantity as totalquantity,


case @audience_type

when v.custclas then v.custclas

end as audience_type
from vwstzipcustaudienceinfo v

Where (v.itemnmbr = '' or v.itemnmbr=v.itemnmbr) and v.Campaign = @Campaign and (v.docdate between @startdate and @enddate)


) as t

where t.audience_type is not null

group by t.campaign,t.Sopnumbe,t.Custnmbr,t.Custname,

t.Address1,t.Address2,t.City,t.State,t.Country,t.Zipcode, t.Phone, t.Audience_type

)

--select * from mystatezipcustaudienceinfo('cpd','1/1/2008','5/15/2008','INDPATIENT') --getting 500rows

--select * from mystatezipcustaudienceinfo('Cpd'','1/1/2008','5/15/2008','INST-HOSPITAL') -- note getting single row

problem have a “-“ in them..i m not getting result..so what condition should be in red color one..


thanks

View 7 Replies View Related

WHERE Condition In SELECT Statement

Nov 14, 2007



Hi All,
I want get the result in a single SELECT statement

SELECT
SUM (PAID_LOSS),
SUM (PAID_EXP)
GROUP BY COMPANY
FROM VIEW_POLICY
WHERE Accounting_Period GE 200701 and LE 200712
-************************************
SELECT
SUM (MEDICAL_RESERVE)
GROUP BY COMPANY
FROM VIEW_POLICY
WHERE Accounting_Period LE 200712

View 5 Replies View Related

Condition In Select Statement

Mar 31, 2008

col1 col2 col3 col4

36930.60 145 N . 00
17618.43 190 N . 00
6259.20 115 N .00
8175.45 19 N .00
18022.54 212 N .00
111.07 212 B 100
13393.05 67 N .00


In above 4 col

if col3 value is B then cursor has to fectch appropriate value from col4.
if col3 value is N then cursor has to fectch appropriate value from col1.
and also wants the sum of result above conditions when column 3 has two values(i.e N,B) for single col2 value

here col2 values are unique

take an example for col2=212
if col3=B
then result is 100
if col3=N
then result is 18022.54
for a single col 2 value ,if col3= N and B then we want sum of above 2 conditions i.e (100+18022.54)
but not the sum of (100+18022.54+111.07+0.0) .


Can any one reply for this..............

View 1 Replies View Related

Need Help On Select Statement Wit Like Condition

Nov 17, 2007

I tried following code and it return no data after query run successfully. I m testing it on msaccess .

SELECT Book.ID, Book.Title, Book.Arthor, Book.Publisher, Book.Year, Book.Price, Inventory.B_ID, Inventory.Quantity
FROM Book INNER JOIN Inventory ON Book.ID=Inventory.B_ID
WHERE Book.ID=Inventory.B_ID And ("Book.Arthor" Like '%es%');


-------

I have test and test2 in Arthor column ... Thanks all !!

View 7 Replies View Related

Select Statement - Filter Condition

Nov 5, 2013

How to display in select statment starting from the word 'basic'

table name:version

Table values:

versionidVersiondescription
53666445SAG Basic Guild Agreement 07/01/2014 - 12/31/2014 PT - HV
53666446SAG Basic Guild Agreement 07/01/2014 - 12/31/2014 HV - HV
53666447SAG Basic Guild Agreement 07/01/2014 - 12/31/2014 NWLN- SY
53666448SAG Basic Guild Agreement 07/01/2014 - 12/31/2014 BC - SY

Output should be:

versiondescription
Basic Guild Agreement 07/01/2014 - 12/31/2014 PT - HV
Basic Guild Agreement 07/01/2014 - 12/31/2014 HV - HV
Basic Guild Agreement 07/01/2014 - 12/31/2014 NWLN- SY
Basic Guild Agreement 07/01/2014 - 12/31/2014 BC - SY

View 3 Replies View Related

Transact SQL :: Select Column On Condition

Jul 4, 2015

I have a table called names  (firstname, lastname, number) then i have a DECLARED variable called  @displaynum...If @displaynum id true   my select query should select the number column.  if the variable is false , only the first two columns are selected.

e.g   @displaynum = true                                          
@displaynum = false
Firstname  | Lastname | number                               Firstname  | Lastnam

blah           |   blah        |   12345                             
blah           |  blah

i am not sure if this is possible as a case still selects the column but just changes the values.

View 12 Replies View Related

How To Select Train Data With Condition

Jan 28, 2008

Hi, in SQL Management Studio, we just click 'Process' to train the data, how could I conditionally select a subset of Data Source (View) to train?

Thanks in advance.
Ricky.

View 4 Replies View Related

Differentiate Between Whether Stored Procedure A Is Executed Inside Query Analyzer Or Executed Inside System Application Itself.

May 26, 2008

Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?

What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results.
Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.

However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.

Looking forward for replies from expert here. Thanks in advance.

Note: Hope my explaination here clearly describe my current problems.

View 4 Replies View Related

If Condition Within Select Query Sql Server 2000

Dec 16, 2007

Hi all,
 
I have to write a select query which need some logic to be implemented.
Query is like
select name,number,active,
if active ="true" then
select @days=days from tbl_shdsheet
else
@days=''
end
from tbl_emp
 In the above query there will be days row for that employee if active is true else there won't be any data for that emp in the tbl_shdsheet
So how can i write queery for this.

View 3 Replies View Related

Transact SQL :: Where Condition - If No Input Value Select All Rows

Apr 27, 2015

I  have table with filed

EmpProject -Table
Empno, Name, Project NO, cost center

I need to restrict the results by project No by user input. In case if user did not provide any value for the projectNO, then need to fetch all rows.

Select empno, name, projecno, cost_center from empproject where projectno=nvl(:pno, :deptno)

View 6 Replies View Related

Transact SQL :: How To Use Case Statement In Select Condition

Nov 6, 2015

below is my original query

select Value = count(*) from dbo.test 

I have 20 rows in dbo.test so i will get 20 as my output, now i need to write a case statement here such that when count(*) = 0 then it should display text filed 'NO Data' else it should display the count.

View 5 Replies View Related

How To Put Condition In Select Statement To Write A Cursor

Mar 29, 2008

col1 col2 col3 col4
36930.60 145 N . 00
17618.43 190 N . 00
6259.20 115 N .00
8175.45 19 N .00
18022.54 212 N .00
111.07 212 B .00
13393.05 67 N .00
In above 4 col
if col3 value is B then cursor has to fectch appropriate value from col4.
if col3 value is N then cursor has to fectch appropriate value from col1.
here col2 values are unique.

Can any one reply for this..............

View 3 Replies View Related

Conditionally Select A Field Depending On Another Field Value

Jul 13, 2004

I want in my query to select a different field in case another one is null. in mysql i'd do it like this:

select
a
,if(b is null, c, b)
,d
from
alphabet

how can this be done in sql server?
thanks

View 2 Replies View Related

HELP With SQL Query: Select Multiple Values From One Column Based On &&<= Condition.

Aug 7, 2007

Hello all. I hope someone can offer me some help. I'm trying to construct a SQL statement that will be run on a Dataset that I have. The trick is that there are many conditions that can apply. I'll describe my situation:

I have about 1700 records in a datatable titled "AISC_Shapes_Table" with 49 columns. What I would like to do is allow the user of my VB application to 'create' a custom query (i.e. advanced search). For now, I'll just discuss two columns; The Section Label titled "AISC_MANUAL_LABEL" and the Weight column "W". The data appears in the following manner:

(AISC_Shapes_Table)

AISC_MANUAL_LABEL W
W44x300 300
W42x200 200
(and so on)
WT22x150 150
WT21x100 100

(and so on)
MT12.5x12.4 12.4
MT12x10 10
(etc.)

I have a listbox which users can select MULTIPLE "Manual Labels" or shapes. They then select a property (W for weight, in this case) and a limitation (greater than a value, less than a value, or between two values). From all this, I create a custom Query string or filter to apply to my BindingSource.Filter method. However I have to use the % wildcard to deal with exceptions. If the user only wants W shapes, I use "...LIKE 'W%'" and "...NOT LIKE 'WT%" to be sure to select ONLY W shapes and no WT's. The problems arises, however, when the user wants multiple shapes in general. If I want to select all the "AISC_MANUAL_LABEL" values with W <= 40, I can't do it. An example of a statement I tried to use to select WT% Labels and MT% labels with weight (W)<=100 is:




Code SnippetSELECT AISC_MANUAL_LABEL, W
FROM AISC_Shape_Table
WHERE (W <= 100) AND ((AISC_MANUAL_LABEL LIKE 'MT%') AND (AISC_MANUAL_LABEL LIKE 'WT%'))



It returns a NULL value to me, which i know is NOT because no such values exist. So, I further investigated and tried to use a subquery seeing if IN, ANY, or ALL would work, but to no avail. Can anyone offer up any suggestions? I know that if I can get an example of ONE of them to work, then I'll easily be able to apply it to all of my cases. Otherwise, am I just going about this the hard way or is it even possible? Please, ANY suggestions will help. Thank you in advance.

Regards,

Steve G.



View 4 Replies View Related

Transact SQL :: Exclude A Distinct Records From Select When One Condition Is True?

May 28, 2015

I have customers named Alex (Cid=1), Bob (Cid=2), and Carrie (Cid=3) in a table customer.

Cid
First_Name
1
Alex
2
Bob
3
Carrie

I have products name Gin (Pid=1), Scotch (Pid=2) and Vodka (Pid=3) in a table products.

Pid
Product_Name
1
Gin
2
Scotch
3
Vodka

And I have a table that holds purchase called Customer_Purchases that contain the following records:

Cid
Pid
1
1
1
2
2
1
2
3
3
2

I would like to make a marketing list for all customers that purchased Gin or Scotch but exclude customers that purchased Vodka. The result I am looking for would return only 2 records: Cid’s 1 (Alex) and 3 (Carrie) but not 2 (because Bob bought Vodka).

I know how to make a SELECT DISTINCT statement but as soon as I include Pid=2 This clearly doesn’t work :

SELECT DISTINCT Pid, Cid
FROM           
Customer_Purchases
WHERE        (Cid = 1) OR
(Cid = 3) OR
(Cid <> 2)

View 3 Replies View Related

Can I Print The Results Of A Condition Based On The Condition?

Feb 9, 2006

For example..

select * from mytable where MyNum = 7

If this brings back more than 1 row, I want to display a message that says,

Print 'There is more than one row returned'

Else (If only 1 row returned), I don't want to print anything.

Can I do this? Thx!

View 1 Replies View Related

COndition Spli - Error Date Condition

Apr 19, 2007

Dear friends,

I'm having a problem... maybe it's very simple, but with soo many work, right now I can't think well...



I need to filter rows in a dataflow...

I created a condition spli to that... maybe there is a better solution...

And the condition is: Datex != NULL(DT_DATE)

(Some DATE != NULL)





[Eliminar Datex NULL [17090]] Error: The expression "Datex != NULL(DT_DATE)" on "output "Case 1" (17123)" evaluated to NULL, but the "component "Eliminar Datex NULL" (17090)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.



What is wrong??

Regards,

Pedro

View 4 Replies View Related

Reporting Services :: SSRS IIF One Condition Or IIF Another Condition

Jun 22, 2015

I  am trying to write an visibility function to have message shown based on two different IIF  conditions:

If behavior is to Add a customer ( if message =NAME ALREADY EXISTS, return " NAME ALREADY EXISTS",    otherwize return " NAME CREATED")If behavior is to  Delete a customer (( if message =NAME DOES NOT EXIST, return "NAME DOES NOT EXIST",    otherwize return "NAME SUCCESSFULLY DELETED")
I tried the following which doesn't work:
=IIF((UCase(First(Fields!Message.Value, "DataSetName")) = "NAME ALREADY EXISTS"), "WARNING: NAME ALREADY EXIST", "NAME  CREATED"),
IIF((UCase(First(Fields!Message.Value,  "DataSetName")) = " NAME DOES NOT EXIST"), "WARNING: NAME DOES NOT EXIST", " NAME DELETED")

View 6 Replies View Related

Transact SQL :: How To Add Condition In Where Clause According To Another Condition

Oct 17, 2015

I write a query to get some data as the following. but i need when a user check specified condition a query parameter change to specified condition :

create proc proc_ReservationDetails
(
@status nvarchar(50) = null
)
as
begin
select reservationId, reservationStatus, reservationDesc

[Code] .....

View 3 Replies View Related

SELECT Query - Different Columns/Number Of Columns In Condition

Sep 10, 2007

I am working on a Statistical Reporting system where:


Data Repository: SQL Server 2005
Business Logic Tier: Views, User Defined Functions, Stored Procedures
Data Access Tier: Stored Procedures
Presentation Tier: Reporting ServicesThe end user will be able to slice & dice the data for the report by


different organizational hierarchies
different number of layers within a hierarchy
select a organization or select All of the organizations with the organizational hierarchy
combinations of selection criteria, where this selection criteria is independent of each other, and also differeBelow is an example of 2 Organizational Hierarchies:
Hierarchy 1


Country -> Work Group -> Project Team (Project Team within Work Group within Country)
Hierarchy 2


Client -> Contract -> Project (Project within Contract within Client)Based on 2 different Hierarchies from above - here are a couple of use cases:


Country = "USA", Work Group = "Network Infrastructure", Project Team = all teams
Country = "USA", Work Group = all work groups

Client = "Client A", Contract = "2007-2008 Maint", Project = "Accounts Payable Maintenance"
Client = "Client A", Contract = "2007-2008 Maint", Project = all
Client = "Client A", Contract = allI am totally stuck on:


How to implement the data interface (Stored Procs) to the Reports
Implement the business logic to handle the different hierarchies & different number of levelsI did get help earlier in this forum for how to handle a parameter having a specific value or NULL value (to select "all")
(WorkGroup = @argWorkGroup OR @argWorkGrop is NULL)

Any Ideas? Should I be doing this in SQL Statements or should I be looking to use Analysis Services.

Thanks for all your help!

View 1 Replies View Related







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