MS SQL Server :: SQL Query Multiple/Optional Feilds

Transact SQL :: Query For Multiple Optional Conditions?

Jun 1, 2015

I have following query. Now I want to change this in such a way that if Condition 1 satisfies then it should not go to next condition ,if not then should go to next. Similarly the same format till condition 4.

select * from table where
IND = 'Y'
and
(
(STATE = @STATE and LOB =@LOB and COMPANY=@COMPANY)--Condition 1
OR
(STATE = @STATE and LOB =@LOB)--Condition 2
OR
(STATE = @STATE and COMPANY=@COMPANY)--Condition 3
OR
(STATE = @STATE)--Condition 4
)

View 4 Replies View Related

Multiple/optional Parameters

Sep 11, 2007

Can I create a report that offers users a choice for the parameter. I want to show a sales report based on either Fiscal year or Calender Year. Can I do that with one report that allows an option on which parameter to choose or do I need two reports.

Thanks.

View 6 Replies View Related

Optional Parameter In Sql Query

Nov 6, 2005

Hi All,I have a stored proc which looks like this.Create ....(@id int,@ud int,@td int=0)if @td=0select bkah from asdf where id=@id and ud=@udelseselect bkah from asdf where id=@id and ud=@ud and td=@td---------------------------------I am wondering if i could replace if condition with the following lineselect bkah from asdf where id=@id and ud=@udand ( @td<>0 and td>@td )IS sql server 2000 smart enough not to use the td>@td in the query if@td is 0Thanks all

View 3 Replies View Related

Search Query With Optional Parameters

Jun 12, 2006

I need to create a stored procedure that will search some tables.

The stored procedure will be passed some parameters that may or may not have a value.

I have googled the best way to do this.
I found this post as an example: Optional Search Parameters

and also found this example : Optional Parameters in T-sQL

I am trying to figure out the best way to do this.

In the past I would build a dynamic query like the following.

SQL Code:

Original
- SQL Code

CREATE PROCEDURE [dbo].[Search_Results]

@SUBCITY VarChar(100) = 'Any'

AS

------------------------------------------------------------------------------------------------------
Declare @SUBCITYString Varchar(200)
If @SUBCITY <> 'Any'
Begin
Set @SUBCITYString = ' AND (Table1.SUBCITY LIKE ''' + @SUBCITY + '%'') '
End
Else
Begin
Set @SUBCITYString = ''
End
-----------------------------------------------------------------------------------------------------

Declare @SQLString As Varchar(500)
Set @SQLString = 'SELECT*

FROMTable1

WHERE Table1.ID IS NOT NULL
' + @SUBCITYString + '

ORDER BY Column ASC'

Execute (@SQLString)

GO

CREATE PROCEDURE [dbo].[Search_Results] @SUBCITY VarChar(100) = 'Any' AS ------------------------------------------------------------------------------------------------------DECLARE @SUBCITYString Varchar(200)IF @SUBCITY <> 'Any' BEGIN SET @SUBCITYString = ' AND (Table1.SUBCITY LIKE ''' + @SUBCITY + '%'') 'ENDELSE BEGIN SET @SUBCITYString = '' END----------------------------------------------------------------------------------------------------- DECLARE @SQLString AS Varchar(500)SET @SQLString = ' SELECT * FROM Table1 WHERE Table1.ID IS NOT NULL ' + @SUBCITYString + ' ORDER BY Column ASC' Execute (@SQLString) GO

However this is really cumbersome to create and is not fun debugging!

Does one of these ways have an advantage over the other? Or is there another way to do this?

Thank you!

View 2 Replies View Related

Help With Optional Parameter Query With IN Statements

Aug 24, 2007

I have a query with 17 separate, optional, parameters. I have declared each parameter = NULL so that I can test for NULL in the case that the user didn��t not pass in the parameter.

I am new enough to SQL Server that I am having difficulty building the WHERE clause with all of these optional parameters.

One solution I was advised on by a well paid SQL programmer, was to use a string in the stored proc and dynamically build the WHERE clause and exec it at the end of the sp. But the whole point of a stored proc is that it can be compiled and cached to make it faster, yet the string approach makes it have to compile every time it��s run! Not a good solution, but maybe it��s the best I can do . . .

I have tried many different approaches using different functions, etc. but I��ve hit a brick wall. Any help in sorting it out with YOUR techniques would be greatly appreciated:

1. To add the parameter to the WHERE clause and test for NULL I��ve used the COALESCE function such as ��WHERE table.fieldname = COALESCE(@Param, table.fieldname)�?. This works well if there is only one item in the parameter, but in the case that I pass multiple items to the parameter, it completely fails.

2. To handle multiple items, for example, if @Param = ��3,7,98�� (essentially, a csv separated list of keys)

Code SnippetWHERE table.fieldname IN(COALESCE(@Param, table.fieldname))

doesn��t work because @Param needs to be parsed from a string into an array of integers in the parameter. So, I am using a UDF I discovered to parse the multi-item parameter. The UDF can be found at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/TreatYourself.asp and it returns a table variable that can be used in an IN statement. So I��m using

Code SnippetISNULL(table.fieldname, 0) IN (SELECT value FROM dbo.fn_Split(@Param,��,��))

which works brilliantly in my WHERE statement AS LONG AS @Param ISN��T NULL. So how do I test for NULL first and still use this approach to multi-item parameters?

I��ve tried

Code SnippetWHERE @Param IS NULL OR ISNULL(table.fieldname, 0) IN (SELECT value FROM dbo.fn_Split(@Param,��,��))

and though it works, the OR causes it to slow way down as it compares every record for the OR. (It slows down by approximately 800%.) The other thing I tried was

Code SnippetISNULL (table.fieldname, 0) IN (CASE WHEN @Param IS NULL THEN ISNULL(table.fieldname, 0) ELSE (SELECT value FROM dbo.fn_Split(@Param,��,��)))

This fails with ��Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression�? due to the multiple values in the parameter. (I can��t understand why the line without the CASE statement works, but the CASE line doesn��t!)

Am I even on the right track, cuz this is driving me mad and I just need a way to deal with optional multi-item parameters in an IN statement? HELP!

View 4 Replies View Related

Max Number Of Feilds

Apr 28, 2004

hi all

having worked with access in the past and getting stumped by the fact a table could only hold 256 feilds....... does sql server have any such limit????

thanks

cq

View 7 Replies View Related

Devide 1 Feild Into 3 Feilds!

Oct 22, 2004

Hi there!

There is a feild in my table that contains the city, state and zip, all in the same feild. I was wondering how would be the best way to devide all that info up into 3 feilds? i really don't know how I would go about it since there are cities that can be comosed of 2 or more words. Here is some sample data. I hope someone can help.

SOUTH EL MONTE CA91733617
BOSSIER LA71172
GARDENA CA90249107
MILWAUKEE WI53216
PARIS IL61944
DUQUOIN IL62832
REDWOOD FALLS MN56283
AUBURN ME04210

IRWINDALE CA91706048
PORTLAND OR97202901
PORTLAND OR972028901
ANAHEIM CA928071735
KENT WA98032
CRYSTAL LAKE IL60014611

View 2 Replies View Related

How To Get Relation Between Two Feilds Of Same Table

Apr 18, 2007

Hi,
I am new to sql and is working with sql server managment 2005 +c# 2005.

My application needs to create a blockdiagram sort of thing say
if in my database i got a table 'Addition' with 'a', 'b', 'c',and the primary key addition_id, and c is related to a and b as c = a+ b.

there is stored procedure name usp_addition which contains this relation. Each time any insert or update is done this sp is executed and all the values are updated for accordingly.
My problem starts in the front end where i need to draw the graphical representation of table addition.

In this graphical representation, I need to draw the labels a, b, c and the arrows from a and b which will connect to c, showing that c has a, b as inputs.

I got the label using dataset and datacolumns but hte problem is how to create the arrows the name of labels (i.e my column names from which the arrow should start and end)

How does I get the information that c as two inputs a, b. I dont need the values since i just want to view the columns in table and which column is input to another column.

Since I need to do this dynamically because my tablename, and the number and name of column would differ does any body knows how to do this.

Priyadarshini

View 1 Replies View Related

Sorting Based On Foreign Key Feilds

Dec 1, 2006

shibu writes "I have a table 'manage_user_types' and its primary key is 'Category_ID'. There is another field 'Category_Parent_ID' which refers the 'Category_ID' in the same table. And one more field is 'Category_Name' which is related to 'Category_ID'. ie.when displaying the table in front end, 'Category_Name' is displayed under 'Category_Parent_ID'. I want to sort the table with 'Category_Name' when clicking the 'Category_Parent_ID' to sort. What should i do?"

View 1 Replies View Related

Select Distinct One Some Fields, But Return All Feilds

Jul 23, 2005

I was curious...Is there a way to select distinct on a combination of some fields andthe for each record returned also get the other fields of anarbitrarily chosen record matching the fields in the distinct record.For example, if I have a select distinct on say three fields:SELECT DISTINCT Code1, Code2, Code3but the table also has other fields, maybe Foo1 and Foo2, and I wantFoo1 and Foo2 to also be displayed. Since there may be multiplerecords that match a particular Code1, Code2, Code3, then I just wantone of those to be arbitrarily chosen.

View 3 Replies View Related

Transact SQL :: Query To Convert Single Row Multiple Columns To Multiple Rows

Apr 21, 2015

View 6 Replies View Related

One Receipt Number, Query Multiple Tables Gives Multiple Data.

Sep 8, 2006

I have just taken over the job of sorting out a rather poorly designed database. It looks like it was 'upsized' from an access database to the SQL server. The SQL server is the 2000 version.

Now I am trying to generate a report of what the students in the database are owing by referencing the Receipt table and then all the available payment methods and allocations. I was wondering if there was anyway to work out data being displayed twice (Let me demonstrate)

Note1: All the tables are linked by a key of ReceiptNo. From what I can see there is a table for every payment type and allocation but no link between the two other then the receipt number.

Using the query:
SELECT T_Receipt.ReceiptNo, T_cheque.Amount AS Chq_Amount, T_credit.Amount AS Cre_Amount, StandingOrder.Amount AS Stn_Amount,
T_BankTransfer.amount AS Bnk_Amount, T_cash.TotalAmount AS Cas_Amount, T_RentPayment.AmountPayed AS Ren_Paid,
T_AdminPayment.AmountPaid AS Adm_Paid, T_InternetBilling.Total AS Int_Paid, T_Utilities.AmountPaid AS Util_Amount,
T_InvoicePayment.amountPaid AS Inv_Paid, T_OtherPayments.paymentAmount AS Oth_Paid, T_parkingBill.paymentAmount AS Prk_Paid,
T_TelephoneBills.TelephoneCredit AS Tel_Paid, T_DepositPayment.[Deposit payment] AS Dep_Amount, T_Receipt.cancelled AS Canceled,
T_Receipt.RemittanceReceiptNo AS Rec_Ref, T_Receipt.Student
FROM T_Receipt INNER JOIN
T_DepositPayment ON T_Receipt.ReceiptNo = T_DepositPayment.receiptNo LEFT OUTER JOIN
T_RentPayment ON T_Receipt.ReceiptNo = T_RentPayment.RentPaymentNo LEFT OUTER JOIN
StandingOrder ON T_Receipt.ReceiptNo = StandingOrder.ReceiptNo LEFT OUTER JOIN
T_TelephoneBills ON T_Receipt.ReceiptNo = T_TelephoneBills.ReceiptNo LEFT OUTER JOIN
T_parkingBill ON T_Receipt.ReceiptNo = T_parkingBill.ReceiptNo LEFT OUTER JOIN
T_OtherPayments ON T_Receipt.ReceiptNo = T_OtherPayments.ReceiptNo LEFT OUTER JOIN
T_InvoicePayment ON T_Receipt.ReceiptNo = T_InvoicePayment.receiptNo LEFT OUTER JOIN
T_cash ON T_Receipt.ReceiptNo = T_cash.ReceiptNo LEFT OUTER JOIN
T_AdminPayment ON T_Receipt.ReceiptNo = T_AdminPayment.ReceiptNo LEFT OUTER JOIN
T_BankTransfer ON T_Receipt.ReceiptNo = T_BankTransfer.receiptNo LEFT OUTER JOIN
T_Utilities ON T_Receipt.ReceiptNo = T_Utilities.receiptNo LEFT OUTER JOIN
T_credit ON T_Receipt.ReceiptNo = T_credit.ReceiptNo LEFT OUTER JOIN
T_cheque ON T_Receipt.ReceiptNo = T_cheque.ReceiptNo LEFT OUTER JOIN
T_InternetBilling ON T_Receipt.ReceiptNo = T_InternetBilling.ReceiptNo
GROUP BY T_Receipt.Student, T_Receipt.ReceiptNo, T_cheque.Amount, T_credit.Amount, StandingOrder.Amount, T_BankTransfer.amount, T_cash.TotalAmount,
T_AdminPayment.AmountPaid, T_InternetBilling.Total, T_Utilities.AmountPaid, T_InvoicePayment.amountPaid, T_OtherPayments.paymentAmount,
T_parkingBill.paymentAmount, T_TelephoneBills.TelephoneCredit, T_Receipt.cancelled, T_Receipt.RemittanceReceiptNo,
T_DepositPayment.[Deposit payment], T_RentPayment.AmountPayed, T_Receipt.Student
HAVING (T_Receipt.Student LIKE N'06%')

Which gives a result of:

RecNo.
30429
Cheque
250
Deposit
250

30429
679.98
250

This is fine but when I do analysis on this it appears as though the student has paid two deposit payments. I was wondering with out querying each table independently from an application if there was a criteria to specify that I only get one deposit result.
So as such say, give me all the payments but I only want one result from the other tables. I though about discrete but that wouldn't work here.

View 3 Replies View Related

Query Help: Multiple WHERE, Multiple COUNT?

Mar 17, 2006

Howdy folks. I have a SELECT question for you. Or maybe a WHERE question. That I am not sure is part of the problem. My application is ASP.NET 2.0, and I’d like to avoid getting into stored procedures right now, if I could.

I am trying to summarize the order status for each customer in the database (SQL Server 2005, by the way). I want to provide two counts: the number of open jobs per customer, and the number of rush jobs per customer. Something like this:

CustID     CustName     JobOpen     JobRush
----------------------------------------------------------------
22            John Deere     47                3
24            MCP              32                 7
37            BlueON          6                  0

In my noobness, I developed this:

SELECT   CustID, MAX(CustName) AS CustName,
                  COUNT (*) AS JobOpen
FROM   Customers
INNER JOIN   Jobs ON JobCustID = CustID
WHERE   (JobDone = 0)
GROUP BY CustID

As you can see, it finds from the Jobs table all jobs that are not done (JobDone is a T/F field), joins the Jobs and Customers tables, groups by CustID, and counts the totals in each CustID group. It works great in outputting the first three columns that I am looking for.

But I cannot come up with a simple way to add JobRush (also a T/F field), because it needs a different WHERE clause than the one JobOpen uses. It would need

WHERE (JobRush = 1)

So maybe my question should be: how do I use multiple WHERE clauses, each with its own COUNT?

I did mess around with Common Table Expressions, and managed to build two CTEs (one with JobOpen results and the other with JobRush results) that I joined together. It worked in Studio Manager, but my ASP.NET page didn’t like it. I guess that means I could learn stored procedures, but wow I’d love to just have a nice complete SELECT command for my page.

Thanks for reading all this. Any input is greatly appreciated.
Matt

View 3 Replies View Related

DB Engine :: Multiple Execution Of Query Pattern Generates Same Query Plan

Oct 6, 2015

SQL Server 2012 Performance Dashboard Main advices me this:

Since the application is from a vendor and I have no control over its code, how can improve this sitation?

View 3 Replies View Related

SQL Server Admin 2014 :: Estimated Query Plan For A Stored Procedure With Multiple Query Statements

Oct 30, 2015

When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.

1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?

<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>

2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?

View 0 Replies View Related

SP To Perform Query Based On Multiple Rows From Another Query's Result Set

Nov 7, 2007

I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w. one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included)         INSERT INTO @users            SELECT u.userName,u.userID                FROM tableU u               LEFT JOIN tableW w ON w.workgroupID = u.workgroupID               WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is:             SELECT u.userName,u.userID
               FROM tableU u
               LEFT JOIN tableW w ON w.workgroupID = u.workgroupID
               WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!

View 1 Replies View Related

Optional Relationship

Dec 18, 2006

Hi, how can i make optional relationship?
for example: In table A, there is column 1, column 2, column3. In table B, there is column 4, column 5 and column 6.
column 1 and column 2 are primary keys for table A and table B. The relationships between table A and table B are column 2 and column 5; column3 and column 6. but optional (ie. when data exists in column 2, then column3 is null)
how can i set the relationship? because one of the columns data is null each time, error always occurs.

View 6 Replies View Related

Optional Inner Joins

Nov 27, 2004

I have a select proc that will take a bunch or criteria parameters. Based on how many are not null I would like to decide how many inner joins to do.
for example:

select H1.Priceid as HotelPriceId,H2.Priceid as AirPriceId, H1.VendorPackageId from
(
select HA.PriceId, HA.VendorPackageId from Criteria HA Inner Join
(
select VendorPackageId from ValidVendorPackages
where Vendor = 'SBT'
and Sitecode = 'system'
and PackageType = 'AHCF'
)HB on HA.VendorPackageId = HB.VendorPackageId
and
(
CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = 'LAS'
and LengthOfStay = 5
and Ages = 'A2'
and ComponentType = @ComponentType_1
and ValidItemType = @ValidItemType_1
and ItemValue = @ItemValue_1
)
)

)H1 INNER JOIN
(
select HA.PriceId, HA.VendorPackageId from Criteria HA Inner Join
(
select VendorPackageId from ValidVendorPackages
where Vendor = 'SBT'
and Sitecode = 'system'
and PackageType = 'AHCF'
)HB on HA.VendorPackageId = HB.VendorPackageId
and
(
CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = 'LAS'
and LengthOfStay = 5
and Ages = 'A2'
and ComponentType = @ComponentType_2
and ValidItemType = @ValidItemType_2
and ItemValue = @ItemValue_2
)
)
)H2 on H1.Priceid = H2.priceId Inner Join
(
select HA.PriceId, HA.VendorPackageId from Criteria HA Inner Join
(
select VendorPackageId from ValidVendorPackages
where Vendor = 'SBT'
and Sitecode = 'system'
and PackageType = 'AHCF'
)HB on HA.VendorPackageId = HB.VendorPackageId
and
(
CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = 'LAS'
and LengthOfStay = 5
and Ages = 'A2'
and ComponentType = @ComponentType_3
and ValidItemType = @ValidItemType_3
and ItemValue = @ItemValue_3
)
)
)H3 on H2.Priceid = H3.priceId

if values are only passed in from @ComponentType_1,@ValidItemType_1,@ItemValue_1 I dont want to do any inner joins.

If its passed in for @ComponentType_1,@ValidItemType_1,@ItemValue_1 & @ComponentType_2,@ValidItemType_2,@ItemValue_2 I want to do the first Inner Join.

and of course if I get all 3 sets of criteria I want to do both the inner joins.
I know I can cut and past this thing 3 times with an if statement but that isn't going to be practical.

View 14 Replies View Related

Optional Argument In UDF?

Jan 27, 2004

Is it possible to define an argument as optional for a UDF? I have a financial calculation that may or may not require a defined date range depending on the status of an individual item. Is there a way to avoid requiring the date range where it's not necessary?

View 10 Replies View Related

Optional Critera

May 5, 2004

I have a SP with many parameters. If not supplied, parameter default value is NULL.

The parameters are to be used in the WHERE clause:

ALTER PROCEDURE dbo.SearchFlight

(
@DepartureDate datetime = null,
@ReturnDate datetime= null,
@MaxPrice money= null,
@Country int= null,

)

AS
SELECT *
FROM myTables with joint
WHERE
DepartureDate = @DepartureDate
AND ReturnDate = @ReturnDate
AND MaxPrice = @MaxPrice
AND Country = @Country

If a parameter is null, I dont want it to be in the WHERE clause.
I dont want to use a string parameter and execute a "string" select.
I dont want to use a combination of IF to check everything ...

Is ther a solution ? using Case statement ???

Thanks for your help

View 10 Replies View Related

Optional Parameters

Sep 15, 2005

Is there an option in a stored procedure whereby a parameter can be flagged as optional? I have a stored procedure with 2 parameters, Product and Date, and I would like to be able to just pass the Product, or pass Product and Date from an Access project. Is this possible?

View 5 Replies View Related

Optional Parameter

Apr 13, 2006

Hello,
Is it possible to define optional parameters for a stored procedure?
What I want is to create a search with about 8 parameters, but most users will only use 3 or 4, so It would be nice If I could only provide the used parameters in my code. And have sql give the unused parameters a default value (possibly %)
thx.

View 4 Replies View Related

Optional Package Run In DTS

Jul 20, 2007

Hello everyone.

I have a package in DTS that I am tryinig to work with. We have a few queries that will export the same exact table to a text file. I am trying to make it so we can pull a previously created text file from a central "CommonFile" folder. In this package I have a Execute Package Task. This second package is only for creating this common txt file. I want this step to only run if the file is non-existant (i.e. someone deleted/moved it by accident) or if it is over a week old (the database we pull it from it updated on sundays). I can run it so the comment txt file get created first thing on monday, but I still want a failsafe in there incase the file is deleted/moved or the package to create it fails.

I have an ActiveX script set up which checks if the file exists and if it does then checks to see how old it is Then from there I would like to jump to a different area of my package:

Code Snippet

Dim pkg
Dim stpbegin

Set objFSO = CreateObject("Scripting.FileSystemObject")

If objFSO.FileExists(".txt") = TRUE THEN
msgbox("COPY")
Set objFile = objFSO.GetFile(".txt")

Dim today
Dim thisweek

today = date()
thisweek = (Weekday(date())-2)

IF thisweek < 0 THEN thisweek = 6 'Sunday Weekday() = 1 so this will put it at the end of the week.

IF (date() - thisweek) <= objFile.DateLastModified THEN
msgbox("COPY")
set pkg = DTSGlobalVariables.Parent
set stpbegin = pkg.Steps("DTSStep_DTSActiveScriptTask_4")
stpbegin.ExecutionStatus = DTSStepExecStat_Waiting
ELSE
msgbox("CREATE")
set pkg = DTSGlobalVariables.Parent
set stpbegin = pkg.Steps("DTSStep_DTSExecutePackageTask_1")
stpbegin.ExecutionStatus = DTSStepExecStat_Waiting
END IF

ELSE
msgbox("CREATE")
set pkg = DTSGlobalVariables.Parent
set stpbegin = pkg.Steps("DTSStep_DTSExecutePackageTask_1")
stpbegin.ExecutionStatus = DTSStepExecStat_Waiting
END IF

Main = DTSTaskExecResult_Success
End Function

The problem I am having is despite the ActiveX script the package will run the execute package task no matter what (i dont see a way to post pics so I will try to draw bellow). I think this is due to me having a success between the ActiveX and the EPT. This is the only way I know of how to step this up. If i do a completion it will also do the EPT and if I do a failure it won't do anything at all after it.

DTSActiveScriptTask_3 (from above) --> success --> DTSExecutePackageTask_1 --> success --> DTSActiveScriptTask_4

So basically I want this pagage to jump over the DTSExecutePackageTask_1 if the file checks out as okay. Does anyone have a suggestion of how I can set up this package?

View 1 Replies View Related

Use Of (optional) Keyword

Nov 26, 2007

I'm using an application that is generating some SQL scripts for SQL Server 2005. I'm trying tweak it so that I can run it on SQL Server 2000.

The line that I'm having trouble with is:

CREATE INDEX FKFFF41F9960601995 ON alf_access_control_entry (permission_id);(optional)

The key word "(optional)" is causing trouble.
I understand this keyword, when used in SQL Server 2005, let's the script continue and complete when errors are detected.

What is the alternative syntax to use in SQL Server 2000?

Thanks,
-Q

View 1 Replies View Related

Optional Where Clause

Apr 17, 2008

Hi, I'm having a problem trying to do optional items in where clause. I'd appreciate some help, heres the code.

select DFD.Col1 as 'Col1',CMD.Col2 as 'Col2'
from Control as DFD,MetaData as
CMD,Columns as DC,Tables as DT
where DFD.GroupID in
({0}) and

(CMD.MetaDataId = DFD.PrimaryID --
or CMD.MetaDataId = DFD.ForeignID -- These are the problem 3 lines
or CMD.MetaDataId=DFD.HumanColumn) --

and DFD.Disabled='N' and CMD.Disabled='N' and DC.ColumnsId=CMD.ColumnId
and DC.Disabled='N' and DT.TablesId=DC.TableID and DT.Disabled='N'

-- For the problem rows I need to be able to optionally search if they're = to MetaDataID. Right now this query will return only rows that have metadataid = PrimaryID or Foreign, Human. I need to be able to return all rows with the optional dependancies.

View 6 Replies View Related

Query Within Query - Checking For Multiple Fields

Jun 9, 2008

Hi,
I remember seeing a fancy query that checked for multiple fields in a table (I think using a select statement in the where clause but not sure), but can't remember how to do it... here is what I want to do (and maybe there is a much easier way). Thanks!

Table1
id item color
1 shoe red
2 shoe blue
3 coat green
4 coat black

Table2
item color
shoe red
coat green

I want everything in Table1 where item and color are not a match.

So my results should be:
2 shoe blue
4 coat black

I'm sorry if this is a dumb question... it's been that kind of a day!

Thanks!

View 1 Replies View Related

One To One Relationships - One Side Optional

Oct 30, 2006

Is it possible to set up a one to one relationship between two tables, where one side of the relationship is optional? For example, I have two tables "Applicant", and "Client":A client will always have one applicant

View 3 Replies View Related

SPROC With Optional Parameter

Jan 16, 2004

I need to use a parameter in a stored procedure that is optional and if it is optional I need all records returned, even if there is an entry in the field that the parameter is appllied to. Is this possible? I know I can assign a default value. but the value in the field could be one of many choices. I want to be able to specify a choice and have only those records returned or leave it blank and return all records.

Thanks for any help,

View 4 Replies View Related

Normalize Your Database: NOT OPTIONAL!

Feb 28, 2004

I've been away for a while but now that I'm back one if the first things I noticed is how many of the problems on this forum could have been easily avoided by simply normalizing the database in question.

For those of you without formal SQL training, know this: You have to normalize your database. This is part of the process of database development. You can not just whack together a few tables in whatever way is the easiest for the problem at hand, because eventually your requirements will expand and then you need your data to be flexible.

Seriously, in the last 3 weeks I've seen many many questions where it is obvious that the problem is a flawed DB design, but all the so-called SQL gurus here will just answer the question without addressing the fact that the problem lies much deeper.

Here's a link I found on Google:
http://www.cse.unsw.edu.au/~cs3710/PMmaterials/Tutorials/normalise.htm

View 8 Replies View Related

Optional Where In Stored Proc

Aug 5, 2005

I have a web page I want to run a stored procedure from. In the web page I have three drop downs Business Area, Business Unit and Reporting PeriodThe drop downs determine whether all projects are returned or all projects for a certain business unit / business area or month.This means I have to tailor my sql statement accordingly. What I want to know is can I append sections of a sql statement ie add or subtract more where clauses depending on the values pulled in from the web page.At the moment I have only accounted for 2 variables and that has caused me to create 4 IF statements depending on the values. 3 variables would cause even more IF statements and multiple combinations which I am trying to avoid.

View 2 Replies View Related

Optional Feature Not Implemented

Jun 9, 2000

I need some help...
I'm trying to execute a stored procedure and I'm getting this message

Run-Time Error '-2147217887 (80040e21)':

[Microsoft][ODBC SQL Server Driver]Optional feature not implemented

Here is the code:
Public Function D2L(sconnect As Variant, dDate As Variant) As Variant
Dim rsDate As ADODB.Recordset
Dim cmdDate As ADODB.Command
Dim prmDate As ADODB.Parameter

Set cmdDate = New ADODB.Command
Set ADOConn = New ADODB.Connection

ADOConn.Open sconnect

Set cmdDate.ActiveConnection = ADOConn
cmdDate.CommandText = "dbo.UP_CVRT_DATE_TO_LONG"
cmdDate.CommandType = adCmdStoredProc

Set prmDate = New ADODB.Parameter
prmDate.Type = adDate
'prmDate.Size = 32
prmDate.Direction = adParamInput
prmDate.Value = dDate
cmdDate.Parameters.Append prmDate

Set rsDate = cmdDate.Execute()

Thanks in advance for any responses...

View 1 Replies View Related

Optional WHERE In Stored Procedures

Aug 30, 2004

Hello guys,
I want to implement a searching with a stored procedure. As an example I have a table like
> tableA ; Id,Name,Surname,Gender(bit) 0 -Male , 1 - Female

If i want to choose all records that are male, I could simply have a query like this :
SELECT * FROM tableA WHERE Gender = 0
and vice versa for female.
If i want to choose all rows in the table i should omit where clause.
The problem is , when i use a stored procedure i should send a parameter. And i dont know if there is a possible way to to do this. When i try such procedure :
CREATE astoredprc
(@gender bit)
AS
SELECT * FROM tableA WHERE Gender = @Gender
GO
--
In such prc. i can only get rows with a where clause. What if i want to use the same procedure with all rows and omiting where. I have many fields like this and I m unsure if I should use a text query or not ...
Thanks for all of u from now on

View 4 Replies View Related