Optional Relationship
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 Complete Forum Thread with Replies
Related Forum Messages:
How Can I Create A One-to-one Relationship In A SQL Server Management Studio Express Relationship Diagram?
How can I create a one-to-one relationship in a SQL Server Management Studio Express Relationship diagram? For example: I have 2 tables, tbl1 and tbl2. tbl1 has the following columns: id {uniqueidentifier} as PK name {nvarchar(50)} tbl2 has the following columns: id {uniqueidentifier} as PK name {nvarchar(50)} tbl1_id {uniqueidentifier} as FK linked to tbl1.id If I drag and drop the tbl1.id column to tbl2 I end up with a one-to-many relationship. How do I create a one-to-one relationship instead? mradlmaier
View Replies !
Optional Inner Joins
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 Replies !
Optional Argument In UDF?
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 Replies !
Optional Critera
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 Replies !
Optional Parameters
Hi, I am in the midst of creating some reports for our new SQL RS server and am wondering if it is possible to hide some parameters (ie not have them there to be used) dependant on the selection of previous parameters? eg: a staff directory might have a parameter for division and one for office location. If a certain division is selected then the office parameter refreshes to show only offices where that division has employees. This is fine. When the division parameter is selected to show all divisions, is it possible to have the office parameter disabled or hidden as this is then irrelevant, the user wants to see all staff? i am guessing the solution to this would be something like vbscript in the actual rdl file? TIA
View Replies !
Optional Where Clause
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 Replies !
Optional Package Run In DTS
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 Replies !
Use Of (optional) Keyword
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 Replies !
Optional Parameters, Again
There is a number of topics in this forum about using optional parameters in the Report Designer. I wonder if someone has an idea how to create optional parameters in the following scenario. I am reporting off the stored procedure that does not take any parameters. It returns the resultset which I want to apply filter to, to display only records that user needs. The filter will contain multiple criterias, most of them must be optional (user chooses which criterias to apply and leaves others to be NULL). How do I create such filter in the Dataset Filter screen? AFAIK it is not possible to define optional filters in this screen, all I can do is to compare database values or variables to input parameters or other values. Denis P.S. I actually have a solution or workaround for this, but maybe someone will come up with more "natural" solution?
View Replies !
Optional Parameter
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 Replies !
Optional Parameters
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 Replies !
Optional Where In Stored Proc
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 Replies !
Normalize Your Database: NOT OPTIONAL!
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 Replies !
SPROC With Optional Parameter
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 Replies !
Unique Key With Optional Field
Is it possible to have the field as a unique key and a optional one?It is like.. for example, office code has to be unique (cannot beduplicated with the same code) and it could be null too.
View Replies !
SQLMANGR.EXE Optional Parameters?
Dear GroupI'd like SQLManager to start on Win98. I've added it to StartUp and itshows in the right-bottom corner of the desktop upon operating systemstart but the database still shows as stopped. Is there's a command Ican use like SQLMANGR.EXE /start or SQLMANGR.EXE /run from the commandline?Thanks very much for your efforts and sharing your expertise!Martin
View Replies !
Optional Parameter In Sql Query
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 Replies !
Can FK Be Nullable/optional By Design?
Hi All!General statement: FK should not be nullabe to avoid orphans in DB.Real life:Business rule says that not every record will have a parent. It isimplemented as a child record has FK that is null.It works, and it is simpler.The design that satisfy business rule and FK not null can beimplemented but it will be more complicated.Example: There are clients. A client might belong to only one group.Case A.Group(GroupID PK, Name,Code…)Client(ClientID PK, Name, GroupID FK NULL)Case B(more cleaner)Group(GroupID PK, Name, GroupCode…)Client (ClientID PK, Name, ….)Subtype:GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)There is one more entity in Case B and it will require an additionaljoin in compare with caseAExample: Select all clients that belongs to any groupSummary Q: Is it worth to go with CaseB?Thank you in advance
View Replies !
Optional WHERE In Stored Procedures
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 Replies !
Dealing With Optional Variables
I'm looking for opinions here: I have a stored procedure that has one required variable, and two optional variables, like this: CREATE PROCEDURE sp_tariff_rule @tariff_id INT, @start_date DATETIME = NULL, @end_date DATETIME = NULL ...etc... I want the procedure to process 1) all data is no dates are presented 2) all data after the start date, if no end date is supplied 3) all data before the end date if no start date is supplied 4) all data between the start and end dates if both are supplied Now, instead of an elaborate conditional, I added this to the WHERE clause of my SQL statement: AND ((@start_date IS NULL OR service_date >= @start_date) AND (@end_date IS NULL OR service_date <= @end_date)) It works fine, but I want to know if anyone has a different/better way of doing it, or if there is a big bug waiting to happen here. I typically don't like to create multipurpose routines in my code, but this is a better approach for my in a non-object-oriented world of SQL.
View Replies !
Optional Feature Not Implemented
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 Replies !
CLR Functions With Optional Parameters
I'm rewriting a T-SQL function that is called "Proper Case" which takes in a sentence and returns the sentence with the first letter of each word capitalized. The new CLR(C#) function implements new functionality where it can take a string and turn it into a properly cased string, sentence cased string, lower cased, upper cased, and toggle cased. The function takes in one parameter, the string, and the casing type, a string also. I want the casing type to be optional where if it's not passed in it defaults to "proper". The reason why I want this is so that I can easily replace the existing T-SQL function without having to add an extra parameter to each call to make life easier. I know you can do it for SQL stored procedures, but I seem to be having trouble with the function. Things I've tried: 1) Overloading the function in C# doesn't work because SQL functions don't like overloaded functions. 2) Attempting to set a "default" value to the parameter. a) casingType nvarchar(4000) = 'proper' b) default casingType nvarchar(4000) = 'proper' Any ideas would be greatly appreciated. Thanks!
View Replies !
Multiple/optional Parameters
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 Replies !
Handling Optional Parameters
On many reports I have optional parameters, for example if the employee is filled in it will run for that employee but if it's null it runs for all employees. Which is the best way to handle this? The two options I am currently looking at are: select * from employee where (employee.id = @EmpID or @EmpID is Null) select * from employee where isnull(@empID, employee.id) = employee.id Anyone else have a different solution? Currently we use the OR approach and I wanted to see if anyone had any thoughts before switching to using IsNull, which seems a bit better.
View Replies !
Optional Report Parameters
Is there a way to create optional parameters in Reporting. For example, I have two tables Region and Country, with drop downs in report displaying the list of each. The stored proc for Country list expects an input parameter RegionID but does NOT require one for the result set to be fetched. The stored procs run fine in sql, but when run with the report, the designer always complains that the RegionID Parameter must be provided for the Country DataSet/List to be populated. Is there a way to create this RegionID parameter as optional such that the country list is populated with all rows in the Country table if no RegionId is provided, and filters if a RegionId is provided?? here is some code that I am using my stored procs Code Snippet For Regions SP1 Create Proc... spGetRegions Begin... Select RegionID, RegionName From Region Order By RegionName End Code Snippet For Countries SP2 Create Procedure dbo.spGetCountries @RegionID int = NULL, AS Begin Select CountryID, CountryName From Country C WHERE C.RegionID = CASE WHEN @RegionID is null THEN C.RegionID ELSE @RegionID END ORDER BY C.CountryName END
View Replies !
Optional OLEDB Parameters : Need Help
I am using Oracle 7.3 against SSRS. I have created an inline query with 7 unnamed parameters. I have named them in SSRS parameters window and selected NULL and ALLOW BLANK check boxes for all the parameters. a.) What I think should happen is: I should be able to pass the combination of parameters NOT all of them, because I have selected NULL and Blank check boxes. But in my case the query is not giving me any results if I pass 2 of 7 parameters. I can see the results only when I pass all 7 parameters. Please Help me... b.) Is there a way I can create a Dynamic WHERE condition using ORACLE 7.3 as database and OUT REF cursor as out parameter for generating parameters. An Example would be great....... I am much familar with SQL Server and creting a dynamic query is no problem. Because of this new assignment in Oracle 7.3 I am pulling my hair to solve this perticlaur problem... Please guys / gals help.. Thanks, Deepak
View Replies !
Dynamic Optional Parameters
Good day, I have an issue on constructing dynamic WHERE conditions that use OPTIONAL parameters. SP_SOMETHING ( 1) @DateFrom datetime, @DateTo datetime, 2) @Param1 char(8), 3) @Param2 char(3), 4) @Param3 tinyint ) I would like to use a where clause that can make use of any combination of the 4 parameters (the two dates should be together) 1 2 3 4 / x x x x / x x x x / x where x = not supplied / = supplied a value (and so the list continues) Can anybody assist me or give me insights on how to go about this complicated WHERE construct without listing all the probable combinations of the supplied parameters in series of IF statements. thank you
View Replies !
Function With Optional Parameters
Hi, I wish to create a user defined funtion in sqlserver2005 with optional parameter list. So at the time of function calling the parameters should be a optional one. How can i do this? please help me .
View Replies !
Optional Paranmeters In Stored Procedure
Hi I have a textbox, and a dropdown list, What i am trying to do is give the user the option of searching based on both (textbox and dropdownlist) or one or the other(textbox or dropdownlist), What i am experiencing though is that when i enter a word into the textbox and select a value from a dropdownlist i get no search results even though the word is "like" the text in the database, it will only work when i type out the full word. What is wrong with my stored procedure, here it is; SELECT S.storeID, S.storeName FROM dbo.Stores AS SINNER JOIN dbo.storeCategories AS SC ON S.storeID = SC.storeID left JOIN dbo.SubCategories AS SU ON SC.subCategoryID = SU.subCategoryID WHERE S.storeName like ISNULL(@storeName + '%', S.storeName) AND SU.subCategoryID = ISNULL( @subCategoryID, SU.subCategoryID)
View Replies !
SqlDataSource Optional Parameter Problem
Hi, I'm having some issues with the SqlDataSource. I want to use it to populate a GridView, but using an optional parameter to filter the results. This is what I have right now (hopefully haven't made any typos - can't copy/paste): <asp:SqlDataSource ID="test1" runat="server" SelectCommand="SELECT * FROM SomeTable WHERE (@MyParam IS NULL OR MyColumn = @MyParam) ORDER BY SomeColumn" ConnectionString="<% ConnectionStrings:MyConnString %>" > <SelectParameters> <asp:ControlParameter Name="MyParam" ControlID="DropDownList1" PropertyName="SelectedValue" Type="String" ConvertEmptyStringToNull="True" DefaultValue="" /> </SelectParameters> </asp:SqlDataSource> <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True"> <asp:ListItem Selected="True" Value="">All</asp:ListItem> <asp:ListItem>AnotherValue</asp:ListItem> <asp:ListItem>SomeElse</asp:ListItem> <asp:ListItem>Whatever</asp:ListItem> </asp:DropDownList> <asp:GridView ID="GV" runat="server" DataSourceID="test1" AutoGenerateColumns="False" DataKeyNames="SomeID" <columns> <asp:BoundField DataField="SomeColumn" HeaderText="A Title" SortExpression="SomeColumn" /> (more bound columns here...) </columns> </asp:GridView> When I test the SQL query in the query designer it works (returns only rows having the value passed as a parameter when one is specified, otherwise it returns all rows), so it seems like that part is OK. The "All" (as in "return all"/no filtering) entry in the DropDownList has a value of a zero lenght string, and the ControlParameter has the convert empty string to null to true (and the default value is the same), so it should get converted to a null when "All" is selected, hence returning all rows. But it doesn't work. It works fine for all the entries with text, but the zero lenght string somehow doesn't work - I get no results at all instead of it returning all rows (but the query itself worked fine when I tested it). What am I missing? I just can't find what I'm doing wrong. Any ideas/hints? (I also need to do the same with an ObjectDataSource, so hopefully I can get this to work!) I can't think of an easy way to find out if the zero lenght string gets converted to a null or not (I've even tried adding OR @MyParam = '' to the query and it still didn't work....) Right now I'm stuck.... (Also posted this question on microsoft.public.dotnet.framework.adonet newsgroup) Thanks a lot in advance for the replies! Carl B.
View Replies !
SQL Query Multiple/Optional Feilds
Hello Group I am having a little trouble with a search. I have 12 checkbox controls and 12 textbox controls. I want to be able to choose which textbox to query from. Most of the time the search may be from one or a couple of textboxes. My problem is I can get the SQL String right. Below is what I have for a string. I know I could write an SQL string for every possible combination and put in all 144 in a SELECT CASE, but I would think there is an easier way to do this. Is there anything that would let me build a SQL string depending what checkbox are checked true? Dim SQL_SL As String = "SELECT * FROM BuildZone" & _ " WHERE Parcel = '" & strPercelS & "'" & _ " ORDER BY Parcel Asc" & _ " SQL_SL As String = SELECT * FROM BuildZone" & _ " WHERE fldNameLast = '" & strNameLastS & "'" & _ " UNION" & _ " SQL_SL As String = SELECT * FROM BuildZone" & _ " WHERE fldDateMonth = " & intDateYearS & "" & _ " AND fldDateYear = " & intDateYearS & "" & _ " UNION" & _ " SQL_SL As String = SELECT * FROM BuildZone" & _ " WHERE fldNameLast = '" & strNameLastS & "'" & _ " UNION" & _ " SQL_SL As String = SELECT * FROM BuildZone" & _ " WHERE fldPermitNum = " & intPermitS & "" & _ " UNION" & _ " SQL_SL As String = SELECT * FROM BuildZone" & _ " WHERE fldStructureType = " & intStructureTypeS & "" & _ " UNION" & _ " SQL_SL As String = SELECT * FROM BuildZone" & _ " WHERE fldConstructionCost >= " & intCCost1S & "" & _ " AND fldConstructionCost <= " & intCCost2S & "" & _ " UNION" & _ " SQL_SL As String = SELECT * FROM BuildZone" & _ " WHERE fldRange = " & intRangeS & "" & _ " AND fldTownship = " & intTownshipS & "" & _ " AND fldSection = " & intSectionS & "" & _ " UNION" & _ " SQL_SL As String = SELECT * FROM BuildZone" & _ " WHERE fldZoningDistricts = " & intZoneS & "" & _ " UNION" & _ " SQL_SL As String = SELECT * FROM BuildZone" & _ " WHERE fldTwsp = " & intTwspS & "" & _ " UNION" & _ " SQL_SL As String = SELECT * FROM BuildZone" & _ " WHERE fldRWD = " & intRWDS & "" & _ " UNION" & _ " SQL_SL As String = SELECT * FROM BuildZone" & _ " WHERE fldWW = " & intWWS & "" & _ " UNION" & _ " SQL_SL As String = SELECT * FROM BuildZone" & _ " WHERE fldAccessApplic = " & intAccessAppS & "" Thanks
View Replies !
Optional Where Parameters On Null Data
I'm new to SQL Server, so if I'm doing anything stupid don't bemean :)I have a procedure that I use to return data based on optionalparameters. It works fine, except when the underlying data contains anull on one if the fields being searched.My system uses a default wildcard for all parameters, so this excludessuch records. I need a way to add in " OR fldName IS NULL " where theparameter is empty or '%'. I've looked at using CASE WHEN, but itdoesnt seem to like SQL Keywords being part of the WHEN clause.I'd hate to have to resort to executing concatonated strings made fromIF and ELSE statements. Just too messy and not at all pretty!Any Ideas? Here's what I've got:ALTER PROCEDURE [dbo].[procFindUnits]@strUnitIDnvarchar = '%',@strProjectNamenvarchar = '%',@strAddressnvarchar = '%',@strTenancynvarchar = '%',@strTenurenvarchar = '%'ASBEGINSET NOCOUNT ON;SELECTtblUnits.strUnitID,tblProjects.strProjectName,qryAddresses.Address_OneLine,lkpTenancyTypes.strTenancyType,lkpTenureTypes.strTenureTypeFROM tblUnits INNER JOINtblProjects ON tblUnits.intProjectID = tblProjects.intProjectIDLEFT OUTER JOINlkpTenancyTypes ON tblUnits.intTenancyType =lkpTenancyTypes.intTenancyType LEFT OUTER JOINlkpTenureTypes ON tblUnits.intTenureType =lkpTenureTypes.intTenureTypeID LEFT OUTER JOINqryAddresses ON tblUnits.strUnitID = qryAddresses.strUnitIDWHERE(tblUnits.strUnitID LIKE @strUnitID)AND (tblProjects.strProjectName LIKE @strProjectName)AND (qryAddresses.Address_OneLine LIKE @strAddress)AND (lkpTenancyTypes.strTenancyType LIKE @strTenancy)AND (lkpTenureTypes.strTenureType LIKE @strTenure)END
View Replies !
Optional Values In A Stored Proc
The following SP gives an error of:Server: Msg 245, Level 16, State 1, Procedure spSelectSEICData, Line26Syntax error converting the varchar value '@' to a column of data typeint.In the Procedure I am using the Select * for testing purposes.Here is the proc.CREATE PROCEDURE spSelectSEICData(@IndivNo int,@CommType SmallInt,@BeginDate as SmallDateTime)ASDeclare @SqlStr as char(1)Set @SqlStr = ''If ((@BeginDate <> ' ') and (@CommType <> ' '))BeginSet @SqlStr = '@IndivNo AND [SEIC-COMMENT-TYPE] = @CommType'EndIf ((@BeginDate <> ' ') and (@CommType = ' '))BeginSet @SqlStr = '@IndivNo AND [SEIC-COMMENT-DATE-MCYMD] =@BeginDate'EndIf ((@BeginDate = ' ') and (@CommType <> ' '))BeginSet @SqlStr = '@IndivNo AND [SEIC-COMMENT-DATE-MCYMD] = @BeginDateAND[SEIC-COMMENT-TYPE] = @CommType 'EndIf ((@BeginDate = ' ') and (@CommType = ' '))BeginSet @SQlStr = '@IndivNo 'EndSELECT *FROM SEICWHERE [SEIC-INDIVIDUAL-NO] = @SqlStrGOThe optional values are the @CommType and the @BeginDate. Where did Igo wrong or is there a better way of doing this?Thanks in advanceBill
View Replies !
Optional Parameters In A Stored Proc?
Title speaks for itself really. Is it possible to write a stored proc with optional parameters? For example consider the following SELECT SELECT FLD1, FLD2 FLD3 FROM TBL1 I'd like to add optional parameters to that statement so that if they wanted to narrow down the results by providing criteria for some fields they could - but didn't have to. Is this possible?
View Replies !
MDAC Optional ODBC Upgrade In NT And SQL FP #5 And 5a
This question relates to the optional installation component MDAC which upgrades the version of the ODBC driver you use. Microsoft has indicated that any ODBC driver BELOW 2.5 has not been tested and is not year 2000 compliant. Anything between 2.5 and 3.0 they are testing and is not certified. 3.0 and above is compliant. However, the NT 4.0 Fix pack #4 installs version 3.60.0319 and the SQL server Fix pack 5 and 5a install 3.70. something. Most of my Production servers and clients have 2.65. something. How are people handling this issue to ensure year 2000 compliance? We already have one vendor's software who is warning us NOT to install this component because their software will not work. I would like to use the most current version possible. Any input would be appreciated........Craig
View Replies !
Sql Server: Optional Feature Not Implemented
I have asp code calling a com object which in turn accesses a database. in development it worked fine, but on the production boxes, the following error is being returned: [-2147217887] [[Microsoft][ODBC SQL Server Driver]Optional feature not implemented] the error occurs after the execution of the following line oRecordset.Open oCommand where oRecordset = ADODB.Recordset and oCommand = ADODB.Command the code already has created the connection, added the active connection to the command object and set multiple options on the command, connection, and recordset objects... this code has worked on multiple servers... so what do I need to change to correct that error? thanks, warren
View Replies !
Stored Procedures And Optional Parameters
I need to create a SP that will accept a varying number of input parameters. A form that the user completes has a several controls that serve to narrow the number of records returned. The more parameters given, the fewer rows returned. In the past I have accomplished this by dynamically building an SQL statement. I dosen't appear possible to pass an SQL statement in a variable to a SP. Any help or pointers would be appreciated.
View Replies !
Search Query With Optional Parameters
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 Replies !
Excel Source With Optional Columns
Hi: I use a SSIS package to loop thro a folder and load data from multiple excel files to a SQL2005 table. Works fine except when an excel has a missing col. Col names in xls are always a subset of col names in the table. The missing cols are random, else I would just have made another package:-) Once a missing column is found, I get runtime and design time errors, and metadata problems. How can a get SSIS to ignore missing columns? TIA
View Replies !
Optional Command Line Parameter
Hello, I want to use an optional parameter at the command line for my package. I planned to make the first task a script which simply checks the variable (which is a string), and if it is empty, go one way, and if it is not, go another way. Is the best to go? Many thnaks in advane
View Replies !
Optional Characters In Search String
I'm trying to search for commonly abbreviated company titles (ie limited, partnership, and so on). I would like to make my sql statement as short as possible (it's already quite lengthy as is). But I'm having trouble netting the abbreviated forms such as LTD and LMTD for limited (I have no control over the data I get, it comes from different counties with no standardization). I've tried using braketted strings like "L[I,IMI,M,]T[ED,D,]" and all other combinations I can think of, including using single quotes in the each string, and removing the empty placeholder and still can grab all instances. Someone else's insight would be appreciated.
View Replies !
Report Builder And Optional Parameters
Hi, Is it possible to create optional parameters in Report Builder? An *ordinary* parameter is created by marking a filter expression as "prompted". At run-time, the user will be asked to provide a value for this parameter (or accept a default value if it is available). This works fine. What I want is to let the user choose whether to provide a value, or leave the parameter empty. In latter case, the reports should not take the parameter into account and display all available data. Example: I have a list of products grouped by the name of the supplier. I want users to be able to see the entire list, or narrow it down to one supplier only. So I create a free text parameter that will contain a part of the supplier's name. It works ok, but if parameter is left blank, or set to NULL, the report will display no data (all products always have their suppliers). I tried to create a filter formula to check for empty value and bypass the filter if necessary, but the formula only allows me to use the parameter expression once. So I cannot check for empty value and apply the filter in the same expression. As a last resort, I used the following workaround: created a new formula named "Enter 'ALL' to see all suppliers" that would return text constant "ALL". I then added another prompted filter expression for this field and grouped it with my supplier prompt using "Any of" group. User is now able to choose a supplier, or enter "ALL" to the second prompt field. This clumsy approach actually works, but then another problem emerges - although I mark both filter fields as Prompted, the Report Builder will forget this flag for one of the fields when the report is reopened. I think it could be a bug (we use SQL2005 SP1) Anyway, it'd be nice if I could create an optional parameter as one expression, not two. Is there any way to do that? TIA Denis
View Replies !
Optional Parameters In A Stored Procedure
Hi all, Can anyone please tell me what is the best way to handle optional parameters in a stored procedure which will ensure a good execution plan? I ask this as I have to create a stored procedure with six optional parameters and I'm getting a little concerned that, apart from the parameter issue, I'm going to have a lot of if else statements. Is there an easy way of doing all this? Thanks
View Replies !
Help With Optional Parameter Query With IN Statements
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 Replies !
|
TRACKER
