SqlDataSource And Optional Stored Procedure Parameters
Oct 12, 2006
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?
Thanks
Tim
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?
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
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.
Hi, I'm developing a website using vwd express and I have created a GridView that bounds data from a stored procedure. The stored procedure takes one parameter. I tested it by using a default value and it works fine. Now, instead of the default value i want to pass the current logged in user name as a parameter. How do i do this. All the info i found around are for passing parameters to the select command of sqldatasource but i cant get it to work when i use a stored procedure. Thanks, M.
I have a Gridview bound to a SQLDataSource that uses a Stored Procedure expecting 1 or 2 parameters. The parameters are bound to textbox controls. The SP looks up a person by name, the textboxes are Last Name, First Name. It will handle last name only (ie. pass @ln ='Jones' and @fn=null and you get all the people with last name=Jones. I tested the SP in Management Studio and it works as expected. When I enter a last name in the Last Name textbox and no First Name I get no results. If I enter a Last Name AND First Name I get results. I don't understand. Here's the HTML View of the page. The only code is to bind the Gridview when the Search button is pressed. <%@ Page Language="VB" AutoEventWireup="true" CodeFile="Default.aspx.vb" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> <asp:TextBox ID="TextBox1" runat="server" TabIndex=1></asp:TextBox> <asp:TextBox ID="TextBox2" runat="server" TabIndex=2></asp:TextBox> <asp:Button ID="Button1" runat="server" Text="Search" TabIndex=3 /> <hr /> </div> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" DataSourceID="SqlDataSource1" DataKeyNames="EmpID" CellPadding="4" EnableSortingAndPagingCallbacks="True" ForeColor="#333333" GridLines="None" AutoGenerateColumns="False"> <Columns> <asp:BoundField DataField="EmpID" HeaderText="Emp ID" ReadOnly="True" SortExpression="EmpID" /> <asp:BoundField DataField="FullName" HeaderText="Full Name" SortExpression="FullName" /> <asp:BoundField DataField="Nickname" HeaderText="Nickname" ReadOnly="True" SortExpression="Nickname" /> <asp:BoundField DataField="BGS2" HeaderText="BGS2" SortExpression="BGS2" /> <asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" /> <asp:BoundField DataField="email" HeaderText="Email" SortExpression="email" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:EmpbaseConnectionString %>" SelectCommand="GetByName" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameter ControlID="TextBox2" Name="fn" PropertyName="Text" Type="String" ConvertEmptyStringToNull=true/> <asp:ControlParameter ControlID="TextBox1" Name="ln" PropertyName="Text" Type="String" ConvertEmptyStringToNull=true/> </SelectParameters> </asp:SqlDataSource> </form> </body></html>
Hello, I'm having trouble executing a Stored Procedure when I leave the input field empty on a 'search' criteria field. I presume the error is Null/Empty related. The Stored Procedure works correctly when running in isolation. (with the parameter set to either empty or populated) When the application is run and the input text field has one or more characters in it then the Stored Procedure works as expected as well.
Hi,I have a stored procedure that takes 3 parameters. I am using a sqldatasource to pass the values to the stored procedure. To better illustrated what I just mention, the following is the code behind:SqlDataSource1.SelectCommand = "_Search"SqlDataSource1.SelectParameters.Add("Field1", TextBox1.Text)SqlDataSource1.SelectParameters.Add("Field2", TextBox2.Text)SqlDataSource1.SelectParameters.Add("Field3", TextBox3.Text)SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedureGridView1.DataSourceID = "SqlDataSource1"GridView1.DataBind()MsgBox(GridView1.Rows.Count) It doesn't return any value. I am wondering is that the correct way to pass parameters to stored procedure?Stan
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.
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 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.
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
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)
I want the procedure to check for the existence of a paramter and if it isthere, it will process these instructions, otherwise it will process theseinstructions. Any ideas? Thanks for your advice.Regards,CK
If I do this with a function and multiple inline sql statements, I could probably do it much easier, but here at work, sprocs are required, and I can't seem to stretch my knowledge and Google searches far enough to find the answer. Plus, I don't really think that creating 4 separate sProcs is the most efficient way of doing this I need to select and return 8 columns from a table, but the problem is I need to feed the sProc parameters in such a way, that I can use different criteria in the Where Clause. for instance, I need to combine these 4 select statements into one:1. Select (fields) from (table) Where TechID=@TechID and Status=@Status)2. Select (fields) from (table) Where TechID=@TechID3. Select (fields) from (table) Where OrdNum=@OrdNum3. Select (fields) from (table) Where CustNum=@CustNum In all instances, the fields and the table are the same - how can I combine all these possible Where clauses (if/then - Select Case?) so that it's only one Stored Procedure? (or, is this even possible?)
Hi, I'm having an issue trying to send parameters to a SQLDatasource object. I"m creating it in code (and I need it to stay there) I have determined that the stored procs called with no parameters work fine. I start having problems when I have any parameters. Interestingly, it doesn't crash. It just fails to run (the onselected event never fires). dsUser is a protected SQLDatasource property repMain is a repeater on the aspx sheet lstUser is a stored proc with one varchar(10) parameter called cSort Here is my current code that will result in no called to StatementCompleted and no data. Pull cSort parameter from the proc and it works just fine. Obviously I'm setting it wrong. string cSelect = "dbo.lstUser"; dsUser = new SqlDataSource(); dsUser .ConnectionString = GetConStr(); dsUser .SelectCommand = cSelect;
Hi all, From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlDbType
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")
Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)
testCMD.CommandType = CommandType.StoredProcedure
Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)
Console.WriteLine("Number of Records: " & (NumTitles.Value))
End Sub
End Class
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.
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>
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)
Hi everybody, I am having trouble how to fixed this code. I am trying to supply the parameterinside a stored procedure with a value, and displays error message shown below. If I did not supply the parameter with a value, it works. How to fix this?Error Message:Procedure or function <stored proc name> has too many arguments specified.Thanks,den2005 Stored procedure:
Alter PROCEDURE [dbo].[sp_GetIdeaByCategory] @CatId <span class="kwd">int</span> = 0 AS BEGIN SET NOCOUNT ON;
Select I.*, C.*, U.* From Idea I inner join IdeaCategory C on I.CategoryID = C.IdeaCategoryID inner join Users U on I.UserID = U.UserID Where I.CategoryID = @CatId Order By LastModifiedDate Desc End
Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.
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
