I have a stored procedure that updates about a dozen rows.
I have some overloaded functions that I should update different combinations of the rows - 1 function might update 3 rows, another 7 rows.
Do I have to write a stored procedure for each function or I can I handle it in the Stored Procedure. I realise I can have default values but I the default values could overwrite actual data if the values are not supplied but have been previously written.
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.
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.
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
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.
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?
Everyone,I have an application that pulls a list of employees into a web form. I use the same stored procedure to search for employees by various demographic characteristics, and have set all of these search parameters to be optional in the stored procedure.Everything works fine when I pull the entire list (without declaring any select parameters). However, on my Search page, I have declared a number of SelectParameter (ControlParameters, to be specific) objects and mapped each one to its respective control (text boxes and a checkbox). When I attempt a search, the SqlDataSource does not select any records unless I specify an argument for ALL of the search parameters. If I omit even a single search value, the page just posts back and does not pull any information from the database. I have verified with SQL Profiler that it is not attempting to hit the database.I have experimented with various declarations in the ControlParameter object, including the DefaultValue and ConvertEmptyStringToNull items. If I specify a non-empty string as a DefaultValue for all of the parameters, it will perform the query, but it does not query if I use an empty string as a default value - unless my user specifies a value in the form. Any ideas?ThanksTim
Hi I want to give the user the ability to search based on a number of criteria, however i do not know how to build my statement properly this is what i have so far; ALTER PROCEDURE [dbo].[stream_StoreFind] -- Add the parameters for the stored procedure here @StoreName varchar(250),@subCategoryID INT AS SELECT Stores.StoreName ,StoreCategories.storeIDFROM Stores INNER JOIN StoreCategoriesON Stores.storeID = StoreCategories.storeID INNER JOIN SubCategories ON StoreCategories.subCategoryID = SubCategories.subCategoryID WHERE
My problem is how will i pass the parameters into the statement, taking into fact that sometimes they may be optional. Thank you
In which system table the information about the optional parameters passed to stored procedure are stored.I know about the tbl_columns and all that stuff. From where can i can come to know the parameter is mandatory or optional.--Message posted via http://www.sqlmonster.com
I've created a search page in my asp.net app that allows the user to enter optional parameters to narrow down the result set. It looks something like:Find all parts where: manuafacturer: <dropdownlist>ANY | manufacturer 1 |... </dropdownlist> model: <dropdownlist>ANY | model 1 |... </dropdownlist> cost: between <textbox> and <textbox> dollarsCurrently I create the SQL command on the fly building the WHERE based on what the user selects. For example if in the form above they select manufacturer = manufacturer1 model = ANY cost = between 10 and 15the WHERE string is ... WHERE manufacturer='manufacturer1' AND cost BETWEEN 10 AND 15Since the user doesn't care about model I leave it out of the WHERE. OK so here is my question. I want to move my queries to strored procedures however I'm not sure how to create the query since it changes based on what the user enters. Using the example above I'm assuming I can create one query with 4 parameters however what value would I use for ANY? parameter1 (manufacturer) = "manufacturer1" parameter2 (model) = ??? parameter3 (price low) = 10 parameter4 (proce high) = 15I see there is an ANY operator in T-SQL but it doesn't look like the right thing to use. Should I use LIKE '%'? Seems that using LIKE would result in addition overhead.ThanksSimon
Code Block CREATE PROCEDURE udsp_td_queryowner @state varchar(10) = NULL, @businesstype varchar(20) = NULL, @size int = NULL, @sortorder varchar(20) = 'state' AS SELECT gl_t.state AS [State], gl_t.business_type AS [Business Type], gl_t.lowsize AS [Low Size], gl_t.highsize AS [High Size], e.InternetAddress AS [Owner] FROM gl_territory gl_t JOIN employee e ON gl_t.employeenumber = e.EmployeeNumber WHERE state = COALESCE(@state, state) AND business_type = COALESCE(@businesstype, business_type) AND COALESCE(@size, lowsize, highsize) between lowsize AND highsize ORDER BY CASE WHEN @sortorder = 'state' THEN gl_t.state WHEN @sortorder = 'business type' THEN gl_t.business_type WHEN @sortorder = 'owner' THEN RTRIM(e.FirstName) + ' ' + RTRIM(e.LastName) END ASC, CASE WHEN @sortorder = 'low size' THEN gl_t.lowsize WHEN @sortorder = 'high size' THEN gl_t.highsize END ASC, CASE WHEN @sortorder = 'statedesc' THEN gl_t.state WHEN @sortorder = 'business typedesc' THEN gl_t.business_type WHEN @sortorder = 'ownerdesc' THEN RTRIM(e.FirstName) + ' ' + RTRIM(e.LastName) END DESC, CASE WHEN @sortorder = 'low sizedesc' THEN gl_t.lowsize WHEN @sortorder = 'high sizedesc' THEN gl_t.highsize END DESC
What it allows me to do is enter in any number of the variables when running the stored procedure. For example, EXECUTE udsp_td_queryowner @state = 'IA' would give me everything in the table in the state of IA regardless of the other field values. Likewise, if I ran EXECUTE udsp_td_queryowner @state = 'KY', @size = 15 it would return the records that are in KY and the size of 15 is in the range of the low and high value.
If I run the first example in Query Analyzer I get 53 records from the data I have. It returns every record that has IA as the state. I run the same thing in Reporting Services and all I get is 3 records. Just the 3 records for the state of IA where the business type is either null or blank (I can't tell.) If I allow all of the variables to accept Nulls then it returns the correct data. However, I would like to find a better alternative because when the report is run it returns all of the records in the table initially and if they user wants to enter in any parameters they have to toggle off the null box for the corresponding paramter.
If a procedure is known but all parameters are not known, can I handle this using CommandBehavior.RetrieveParameters. If yes, do I need to incur an extra round trip to server.
I have created a stored proc for a report, which works fine. I want to have the user enter a project ID to filter the report, so set the stored proc accordingly. However, I want the user to enter a 10 digit ID, which is the equivilent of two fields in the stored proc. My where statement is :
Where actv.ProjID + '-' + actv.Activity = @project
This works fine under the data tab, I can enter a full project and get the results I want. But I cannot preview the report without getting an error. I'm not sure how to add this to the report parameters, as it is two fields concatenated together. Any help would be appreciated!
I'm having a strange problem that I can't figure out. I have an SQL stored procedure that updates a small database table. When testing the Stored Procedure from the Server Explorer, it works fine. However, when I run the C# code that's supposed to use it, the data doesn't get saved. The C# code seems to run correctly and the parameters that are passed to the SP seem to be okay. No exceptions are thrown. The C# code: SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["touristsConnectionString"].ConnectionString); SqlCommand cmd = new SqlCommand("fort_SaveRedirectURL", conn); cmd.CommandType = CommandType.StoredProcedure; Label accomIdLabel = (Label)DetailsView1.FindControl("lblID"); int accomId = Convert.ToInt32(accomIdLabel.Text); cmd.Parameters.Add("@accomId", SqlDbType.Int).Value = accomId; cmd.Parameters.Add("@path", SqlDbType.VarChar, 250).Value = GeneratePath(); try { conn.Open(); cmd.ExecuteNonQuery(); } catch(Exception ex) { throw ex; } finally { conn.Close(); } The Stored Procedure: ALTER PROCEDURE developers.fort_SaveRedirectURL ( @accomId int, @path varchar(250) ) AS DECLARE @enabled bit, @oldpath varchar(250)
/* Ensure that the accommodation has been enabled */ SELECT @enabled = enabled FROM Experimental_Accommodation WHERE Experimental_Accommodation.id = @accomId
IF (@enabled = 1) BEGIN /* Now check if a path already exists */ SELECT @oldpath = oldpath FROM Experimental_Adpages_Redirect WHERE Experimental_Adpages_Redirect.accom_id = @accomId
IF @oldpath IS NULL BEGIN /* If Path already exists then we should keep the existing URL */ /* Otherwise, we need to insert a new one */ INSERT INTO Experimental_Adpages_Redirect (oldpath, accom_id) VALUES (@path,@accomId) END END RETURN
Interestingly enough, I haven't come across this before. I have a SQL stored procedure which takes four parameters; periodstartdate (datetime), periodenddate (end time), hsgradyearstart (int), hsgradyearend (int)
[dbo].[CalculateActivityTotal]
-- Add the parameters for the stored procedure here
@periodstartdate datetime = '2007-01-01',
@periodenddate datetime = '2007-01-08',
@hsgradyearstart int = 1900,
@hsgradyearend int = 2007 AS...
If I run the stored procedure and pass the parameters using EXEC or
the stored proc takes well over ten minutes to run (it does a bunch of aggregation). If I modify the stored procedure to take no parameters, however, and I hardcode the dates in the stored proc using declare and set then it runs in 13 seconds. What could be causing my problem and how I can I go about resolving this? I need to pass the parameters via reporting server. Thanks!
I have the following ASP code that builds part of the example SQL statement below (it's the same SQL as in my earlier thread here (http://www.dbforums.com/showthread.php?t=1214044) but a very different question):
if sFindTicketEventId > 0 then sSQL = sSQL & " AND [tblEvents].[id]=" & sFindTicketEventId if sFindTicketStandId > 0 then sSQL = sSQL & " AND [tblStands].[id]=" & sFindTicketStandId
SELECT [tblC].[id] AS CombinationID, [tblC].[availability], [tblC].[description], [tblC].[price] AS combinationPrice, [tblC].[combination_open], [tblT].[TicketID] AS TicketID, [tblT].[price] AS ticketPrice, [tblT].[availability], [tblT].[ticket_open], [tblT].[quantity], [tblT].[event_name], [tblT].[event_open], [tblT].[stand_name], [tblT].[stand_open], [tblT].[admission_start_date], [tblT].[admission_end_date], [tblT].[date_open], [tblT]., [tblT]., [tblT2].[description], [tblT2].[admin_description] FROM( SELECT [tblCombinations].[id], [tblTickets].[id] As TicketID, [tblTickets].[price], [tblTickets].[availability], [tblTickets].[ticket_open], [tblCombinations_Tickets].[quantity], [tblEvents].[event_name], [tblEvents].[event_open], [tblStands].[stand_name], [tblStands].[stand_open], [tblAdmissionDates].[admission_start_date], [tblAdmissionDates].[admission_end_date], [tblAdmissionDates].[date_open], [tblBookingDates].[booking_start_date], [tblBookingDates].[booking_end_date] FROM [tblCombinations] LEFT JOIN [tblCombinations_Tickets] ON [tblCombinations_Tickets].[combination_id] = [tblCombinations].[id] LEFT JOIN [tblTickets] ON [tblCombinations_Tickets].[ticket_id] = [tblTickets].[id] LEFT JOIN [tblEvents] ON [tblEvents].[id] = [tblTickets].[event_id] LEFT JOIN [tblStands] ON [tblStands].[id] = [tblTickets].[stand_id] LEFT JOIN [tblAdmissionDates] ON [tblAdmissionDates].[id] = [tblTickets].[admission_date_id] LEFT JOIN [tblBookingDates] ON [tblBookingDates].[id] = [tblTickets].[booking_date_id] LEFT JOIN [tblTicketConcessions] ON [tblTicketConcessions].[id] = [tblTickets].[ticket_concession_id] LEFT JOIN [tblBookingQuantities] AS [tblBookingMinQuantities] ON [tblBookingMinQuantities].[id] = [tblTickets].[booking_min_quantity_id] LEFT JOIN [tblBookingQuantities] AS [tblBookingMaxQuantities] ON [tblBookingMaxQuantities].[id] = [tblTickets].[booking_max_quantity_id] LEFT JOIN [tblMemberships] ON [tblMemberships].[id] = [tblTickets].[membership_id] WHERE 1=1 [B]AND [tblEvents].[id]=2 [B]AND [tblStands].[id]=3 --AND [tblAdmissionDates].[id]=@admissionDateId --AND [tblBookingDates].[id]=@bookingDateId --AND [tblTicketConcessions].[id]=@concessionId --AND [tblBookingMinQuantities].[id]=@bookingMinQuantityId --AND [tblBookingMaxQuantities].[id]=@bookingMaxQuantityId --AND [tblMemberships].[id]=@membershipId GROUP BY [tblCombinations].[id], [tblTickets].[id], [tblTickets].[price], [tblTickets].[availability], [tblTickets].[ticket_open], [tblCombinations_Tickets].[quantity], [tblEvents].[event_name], [tblEvents].[event_open], [tblStands].[stand_name], [tblStands].[stand_open], [tblAdmissionDates].[admission_start_date], [tblAdmissionDates].[admission_end_date], [tblAdmissionDates].[date_open], [tblBookingDates].[booking_start_date], [tblBookingDates].[booking_end_date] ) as [tblT] JOIN [tblCombinations] as [tblC] on [tblT].[id]=[tblC].[id] LEFT JOIN [tblTickets] as [tblT2] on [tblT].[TicketID]=[tblT2].[id]
I want to turn this SQL into a stored proc; there are currently about 8 parameters that I want to pass into it. The field value for each will be either NULL or a positive integer, and the paramater will be passed in as an integer.
If the passed parameter value is a positive integer then it should return all records where the corresponding field value matches that integer. If the passed parameter is 0, it should return all rows regardless of whether the field value is an integer or NULL.
And I can't for the life of me figure out how to do it. Do I need an IF statement in there or something?
Hello, I have a number of multi-select parameters which I would like to send to a stored procedure within the dataset for use in the stored procedure's IN() statement which in turn is used to filter on or out particular rowsets.
I considered using a hidden string parameter set = " ' " + join(parameter.value, ',') + " ' " so that the hidden parameter would then contain a comma delimiated string of the values selected, which would then be sent on to the stored proc and used in the WHERE clause of one of the queries internal to the stored proc. But before I start dedicating time to do this I wanted to inquire if anyone here with far more expertise could think of a faster or less system heavy method of creating a single string of comma delimited parameter selections?
I would like to write 1 proc that can take additional criteria if its sent in. An example is:
select HA.PriceId, HA.VendorPackageId from Criteria HA Inner Join ( select VendorPackageId from ValidVendorPackages where Vendor = @VENDOR and Sitecode = @SITECODE and PackageType = @PACKAGETYPE )HB on HA.VendorPackageId = HB.VendorPackageId and CriteriaId in ( select CriteriaID from ValidItemCriteria where Destination = @DESTINATION and LengthOfStay = @LENGTHOFSTAY and Ages = @AGE and ComponentType = @COMPONENTTYPE_1 and ValidItemType = @VALIDITEMTYPE_1 and ItemValue = @ITEMVALUE_1 )
Multiple @COMPONENTTYPE, @VALIDITEMTYPE,@ITEMVALUE can be sent in. Instead of making multiple procs or copying the proc multiple times with an if statement at the top checking the number of parameters that aren't =''. Is there a way to exectue:
and CriteriaId in ( select CriteriaID from ValidItemCriteria where Destination = @DESTINATION and LengthOfStay = @LENGTHOFSTAY and Ages = @AGE and ComponentType = @COMPONENTTYPE_1 and ValidItemType = @VALIDITEMTYPE_1 and ItemValue = @ITEMVALUE_1 ) and CriteriaId in ( select CriteriaID from ValidItemCriteria where Destination = @DESTINATION and LengthOfStay = @LENGTHOFSTAY and Ages = @AGE and ComponentType = @COMPONENTTYPE_2 and ValidItemType = @VALIDITEMTYPE_2 and ItemValue = @ITEMVALUE_2 ) and CriteriaId in ( select CriteriaID from ValidItemCriteria where Destination = @DESTINATION and LengthOfStay = @LENGTHOFSTAY and Ages = @AGE and ComponentType = @COMPONENTTYPE_3 and ValidItemType = @VALIDITEMTYPE_3 and ItemValue = @ITEMVALUE_3 )
Ignoring the 2nd 2 selects if @COMPONENTTYPE_2, @VALIDITEMTYPE_2,@ITEMVALUE_2 and @COMPONENTTYPE_3, @VALIDITEMTYPE_3,@ITEMVALUE_3 are = ''
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?
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 .
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.
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
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.
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.
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...
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'
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
Hello,,,I need to update table but not all fields in stored proc have to be with value (some of fields will be NULL), so i need to avoid updating any field that his parameter with null value.I need the syntax for (IF) statement to optionally update that field.something like this :CREATE PROCEDURE [dbo].[MyUpdate] ( @ID int, @Field1 nvarchar(50), @Field2 nvarchar(50)=null, @Field3 nvarchar(50)=null, @Field4 nvarchar(50) ) AS
UPDATE MyTable SET Field1 = @Field1, if (@Field2<>null) Field2 = @Field2, if (@Field2<>null) Field3 = @Field3, Field4 = @Field4 WHERE ID = @ID I saw example before but i can't remember where. Thank you in advance
I wrote a stored proc to be implemented in a datagrid. I went and used it on a record to update some information and it updated THE WHOLE DATABASE WITH THAT ONE RECORD..
IF anyone could shead some light on what I'm doing wrong that would be great.
Hi, I've been reading all sorts of info on the ntext field. I needthis to store xml documents in sql server via a stored proc.Because of its size, I apparently can not use SET (as in UPDATE)therefore I'm trying to do an INSERT of the row with this field (afterdeleting the old row).CREATE PROCEDURE dbo.UpdateXmlWF(@varWO varchar(50)@strWF ntext@varCust varchar(50)@varAssy varchar(50))ASINSERT INTO tblWorkOrders (WorkOrder, Customer, Assy, xmlWF) VALUES(@varWO, @varCust, @varAssy, @strWF)I'm using MSDE so I can't tell what's wrong...it just won't save theproc.PLEASE HELP!Thanks, Kathy
