Stored Procedure Optional Search Parameters
May 18, 2008
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
View 12 Replies
ADVERTISEMENT
Oct 2, 2007
How do I specify optional parameters when writing CLR based Stored Procedures in C#?
View 16 Replies
View Related
Jan 7, 2008
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 5 Replies
View Related
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?ThanksTim
View 1 Replies
View Related
Jul 23, 2005
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
View 1 Replies
View Related
Jan 12, 2008
I have the following stored procedure:
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.
Any ideas?
View 12 Replies
View Related
Jun 12, 2006
I need to create a stored procedure that will search some tables.
The stored procedure will be passed some parameters that may or may not have a value.
I have googled the best way to do this.
I found this post as an example: Optional Search Parameters
and also found this example : Optional Parameters in T-sQL
I am trying to figure out the best way to do this.
In the past I would build a dynamic query like the following.
SQL Code:
Original
- SQL Code
CREATE PROCEDURE [dbo].[Search_Results]
@SUBCITY VarChar(100) = 'Any'
AS
------------------------------------------------------------------------------------------------------
Declare @SUBCITYString Varchar(200)
If @SUBCITY <> 'Any'
Begin
Set @SUBCITYString = ' AND (Table1.SUBCITY LIKE ''' + @SUBCITY + '%'') '
End
Else
Begin
Set @SUBCITYString = ''
End
-----------------------------------------------------------------------------------------------------
Declare @SQLString As Varchar(500)
Set @SQLString = 'SELECT*
FROMTable1
WHERE Table1.ID IS NOT NULL
' + @SUBCITYString + '
ORDER BY Column ASC'
Execute (@SQLString)
GO
CREATE PROCEDURE [dbo].[Search_Results] @SUBCITY VarChar(100) = 'Any' AS ------------------------------------------------------------------------------------------------------DECLARE @SUBCITYString Varchar(200)IF @SUBCITY <> 'Any' BEGIN SET @SUBCITYString = ' AND (Table1.SUBCITY LIKE ''' + @SUBCITY + '%'') 'ENDELSE BEGIN SET @SUBCITYString = '' END----------------------------------------------------------------------------------------------------- DECLARE @SQLString AS Varchar(500)SET @SQLString = ' SELECT * FROM Table1 WHERE Table1.ID IS NOT NULL ' + @SUBCITYString + ' ORDER BY Column ASC' Execute (@SQLString) GO
However this is really cumbersome to create and is not fun debugging!
Does one of these ways have an advantage over the other? Or is there another way to do this?
Thank you!
View 2 Replies
View Related
Dec 4, 2003
Hi,
I have a repeater control which I populate with search results from SQL Server.
But I can't figure out how to cope with users who submit multiple search items and still use my stored procedure. Is this possible or do you have to build the query with a StringBuilder and execute it manually?
I'm using a stored procedure with parameters:
input parameters <-- PageSize & CurrentPage
output parameter --> TotalRecords
Am using a temporary table to store all records before Select-ing those required for the particular page.
If I compose the query manually then I can't figure out how to get TotalRecords back as a return parameter. Would appreciate help on this one.
Am hoping that stored procedures can cope with an unknown number of parameters.
View 3 Replies
View Related
Mar 5, 1999
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 1 Replies
View Related
May 19, 2004
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 4 Replies
View Related
Dec 3, 2003
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.
Many thanks for any guidance.
Simon
View 5 Replies
View Related
Sep 15, 2005
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
View 2 Replies
View Related
Jun 17, 2008
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 5 Replies
View Related
Mar 7, 2006
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
View 7 Replies
View Related
Apr 21, 2006
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?)
View 4 Replies
View Related
Oct 4, 2015
When using JDBC is it possible to ignore input parameters in a stored procedure if they have default values?
I can create a string and execute a PreparedStatement but I'd like to use a Callable Statement.
View 7 Replies
View Related
Mar 29, 2008
Hi - I'm short of SQL experience and hacking my way through creating a simple search feature for a personal project. I would be very grateful if anyone could help me out with writing a stored procedure. Problem: I have two tables with three columns indexed for full-text search. So far I have been able to successfully execute the following query returning matching row ids: dbo.Search_Articles @searchText varchar(150) AS SELECT ArticleID FROM articles WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText) UNION SELECT ArticleID FROM article_pages WHERE CONTAINS(Text, @searchText); RETURN This returns the ArticleID for any articles or article_pages records where there is a text match. I ultimately need the stored procedure to return all columns from the articles table for matches and not just the StoryID. Seems like maybe I should try using some kind of JOIN on the result of the UNION above and the articles table? But I have so far been unable to figure out how to do this as I can't seem to declare a name for the result table of the UNION above. Perhaps there is another more eloquent solution? Thanks! Peter
View 3 Replies
View Related
Mar 12, 2008
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)
RetValue.Direction = ParameterDirection.ReturnValue
Dim auIDIN As SqlParameter = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11)
auIDIN.Direction = ParameterDirection.Input
Dim NumTitles As SqlParameter = testCMD.Parameters.Add("@numtitlesout", SqlDbType.Int)
NumTitles.Direction = ParameterDirection.Output
auIDIN.Value = "213-46-8915"
PubsConn.Open()
Dim myReader As SqlDataReader = testCMD.ExecuteReader()
Console.WriteLine("Book Titles for this Author:")
Do While myReader.Read
Console.WriteLine("{0}", myReader.GetString(2))
Loop
myReader.Close()
Console.WriteLine("Return Value: " & (RetValue.Value))
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.
Thanks in advance,
Scott Chang
View 29 Replies
View Related
Sep 15, 2005
Is there an option in a stored procedure whereby a parameter can be flagged as optional? I have a stored procedure with 2 parameters, Product and Date, and I would like to be able to just pass the Product, or pass Product and Date from an Access project. Is this possible?
View 5 Replies
View Related
Jun 21, 2006
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 4 Replies
View Related
Jan 19, 2008
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 2 Replies
View Related
Feb 13, 2008
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 3 Replies
View Related
Sep 10, 2005
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 1 Replies
View Related
Jul 27, 2007
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 8 Replies
View Related
Sep 11, 2007
Can I create a report that offers users a choice for the parameter. I want to show a sales report based on either Fiscal year or Calender Year. Can I do that with one report that allows an option on which parameter to choose or do I need two reports.
Thanks.
View 6 Replies
View Related
Nov 8, 2007
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 6 Replies
View Related
Jul 28, 2006
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 5 Replies
View Related
Jun 11, 2007
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 7 Replies
View Related
Apr 25, 2007
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 9 Replies
View Related
Nov 30, 2006
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 34 Replies
View Related
Mar 29, 2007
Hi, I want to write a StoredProcedure with one optional input parameter of Date and when it is missing I want current date to be used.
I have written the following StoredProcedure, but getdate function doesn`t work. If I replace it with a constant date, it works.
ALTER PROCEDURE [dbo].[LinksFees_Record]
@Date datetime = getdate
AS
INSERT INTO LinkSearchFees
(LinkID, Price, [Date])
SELECT ID AS LinkID, SearchDayFee AS Price, @Date
FROM Links
WHERE (SearchDayFee > 0)
RETURN
When I call the StoredProcedure the following exception occur: Conversion failed when converting datetime from character string.
How can I fix it?
View 1 Replies
View Related
Jul 5, 2007
Please i have lots of reports, i would like to pass parameters, i may pass or i will not pass.
but all of my reports are using stored procs from sql server database.
please guys can some one give me an idea how can i have optional parameters defined in the stored procedure.
in the reports layout collections property where i have to specify the parameters need to run the report there how can i define. wether it is optional of required parameter.
i see the parameters collections object there are hidden, pass null or pass a default value or pass the info via query etc.
please help thank you very much for the information.
View 1 Replies
View Related
Jun 14, 2007
Hi friends,
I am developing reports using SQL Server 2005 Reporting service
I want to pass optional parameters to Report using dropdown
I filled dataset using EmpId and EmpName. and assigned this dataset to
query the values.
I checked properties for Report Parameters of Allow Null, Allow Blank values
Even i checked this properties, it enforces me to Enter some value for dropdown while running or previewing the report
I don't want to enforce the user that value must be selected.
In short, How we can able to pass multiple parameters which are not mandatory.
Pls reply me ASAP
Any suggestion is appreciated
Thanks in Advance.
Regards
Suds
View 9 Replies
View Related
