query parameter

I have to create a stored procedure where the criteria is: "All", specific value and, all except one value

 
so far this is what I have:

@Status varchar (50)  -- as parameter from a dropdown box

DECLARE @NewStatus varchar(50)
SET @NewStatus = 
	CASE 
		WHEN @Status ='All' AND @Status <> 'All but closed' THEN '%'
		WHEN @Status <>'All' AND @Status <> 'All but closed' THEN @Status		
	END

and in the stored procedure

..........

WHERE Status like @NewStatus

 
 I am a little confused as to how could I return all values except those that have  the value "Closed"

 

Thanks. 

0
dg_102
3/2/2007 5:16:45 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

2 Replies
432 Views

Similar Articles

[PageSpeed] 15

in your where clause just add

AND status  <> 'closed' or if you had multiple status' status:  not in (x, y, z) if that is not helpful, give me a little more info and i will try and help you some more....--jp


If my post is helpful please mark as answer
Regards -- jp
(http://www.rzrsolutions.com/rzrWeb20/BlogViewScreen.aspx)
0
jdingo
3/2/2007 6:42:29 PM

Finally I came up with this solution:

  

DECLARE @NewStatus varchar(50)
SET @NewStatus = 
	CASE 
		WHEN @Status <>'All' AND @Status <> 'All but closed' THEN @Status
		else
		'%'
	END

DECLARE @NewStatusNot varchar(50)
SET @NewStatusNot = 
	CASE @Status
		WHEN 'All but closed' THEN 'Closed'
		else
			'zz'
	END

.............

WHERE

Status.Status like @NewStatus

AND

Status.Status not like @NewStatusNot
 Not sure if this the best solution but it works Smile

It is the same idea that you have suggested.
0
dg_102
3/2/2007 9:38:39 PM
Reply:

Similar Artilces:

C# Ado.net Sql query parameter query
Hi All, I'm trying to pass in a parameter value from an array in a loop that is used in a sql query and the results are populated to an xml file. The trouble is that I'm only getting the colums values in the outputted xml file. So I feel that the paramter is not being read. So can anyone help as I'm really stuck on this one. The code is as follows : public void DisplayUserInfo() { ArrayList UserIdArrayList = IdentifyUserID(); foreach(string ShowUserIDString in UserIdArrayList) { try { SqlConnection SqlConn = new SqlConnection(DBConnString)...

Passing SQL Parameter Array to Object datasource as parameter
I have a class 'CommonDataAccess' and my method filldataset() accepts a Stored Procedure name and a SQL Parameter Array. This class is to be made as the type for the Objectdatasource which is Bound to a gridview. How do I pass the SQL Parameter Array to this.  Raj Kiran S------------Chennai, India CALLINGBELLS: I have a class 'CommonDataAccess' and my method filldataset() accepts a Stored Procedure name and a SQL Parameter Array. This class is to be made as the type for the Objectdatasource which is Bound to a gridview. How do I pass the SQL Parameter Array to t...

SQL limit of demilted pipe characters passed as parameter to sql query
I am not sure about the architecture of the Issue Tracker and hence not sure if it applies here. But I will post in any case and wait for users on this forums comments as well. ===========Earlier post================== This question is regarding the architecture of TimeEntry. In some programs it builds an arrayList for Master-detail type of relationship and when user is ready to save it by clicking 'submit' it build a variable with pipe delimited fields. This is then passed to a sql query. This to me does not seem to be an efficient manner. Because the max character is 150...

SQL Query Parameters
I am trying to search a SQL database using a TextBox  and a DropDownList. The textbox being where the user enters there search query and the dropdownlist allowing them witch column to search.  If I hardcode in the table column to search everything works fine but as soon as I parameterize the column to search I no longer get any results.   While stepping through the code both parameters do get assigned the correct values to make the sql statement valid.  Any advice would be appreciated. .... code that does not work .....

Datasource Sql query
 Hi, I am a coldfusion developer, but have now moved over to ASP.NET using VS2008 Pro, so trying to learn it quickly. My problem is that I have created a datasource with a simple query and when I run it in the query builder it works fine, however when I run it on the page I get nothing. Here is my query    <asp:SqlDataSource ID="qAllTrucks" runat="server"         ConnectionString="<%$ ConnectionStrings:ConnectionString %>"         ProviderName="<%$ Con...

Can sql queries be nested in sql queries?
I am having problems with a template that uses the information from one sql query to generate a query on another table. Let me be more specific. The template in question receives a category ID value from a web page that provides a menu of several categories to choose from. Each Category ID represents several Items or products to choose from. When the template receives the category ID, the first thing it does is run a query on my item_category table (this table lists all of the items and associates them with the appropriate category). This query returns several itemIDs that are all...

SQL query that works in SQL Server Management Studio, but doesn't on .NET 2.0 page
SELECT favorites.FID, favorites.filename, favorites.username, files.status, files.private, files.views, files.title FROM favorites INNER JOIN files ON favorites.filename = files.filename WHERE (favorites.username = @username) AND (files.status IS NULL) AND (files.private = @private)@private is manually set to 'no'@username is set to profile.usernamewhen I run the above query in microsoft sql server mgmt studio express on the database, it presents all the information i'm asking for very easily. however when i try to implement this query on a aspx .net2.0 page with teh sqldatasourc...

sql or my sql query
hi,in my database  i have the two tables...1)programs                                2)reservedin the programs...two fields 1)program id           2)maxseats................these details wil be entered by adminin the reserved table  two fileds ..1)PRogramid        2)statusmy requirement is .. i need the available seats..meansif any body is intersted in  any program...they wil select the program...then status becomes approved(1)..then i need the o/p as ...

.net + dynamic SQL-query
On our page we have texbox, dropdownlist, database and gridview for it.It DropDownList we have all names of columns from database (in our situation they are Item_Name, Item_Type,  City and Country). In database we have two more columns, but we do not need them in this task.And the task is to choose from DropDownList item/value and deliver it to SQL-query. We have done searching from database, but the column to search from is static, we want to make it dynamic, we want to choose from Dropdownlist column, from which we will search.We are making all in aspx, not in .cs code. We are not fam...

Using parameters in SQL query
Hi all, I am trying to write an sql query in my web application to select some records from the database using parameters. I use SqlClient for that. The query has a WHERE clause where its supposed to values from the various dropdownlists that I have in my application. I bind the SelectedValue from the dropdownlists to the appropriate parameters and the SQL query does not work if all the conditions are satified in the WHERE clause. Can I have an SQL query where I can provide all the parameters in the WHERE clause (with AND conditions) and it takes only those va...

Bound parameters SQL queries.
Hello. I got some problems with the use if parameters in queries asociated with Oracle7, in the programers guide of Optima++ in the page 509 they made references about the use. This use with numerics fields is Ok, but when i try put it ina character fields of a table the query fail, this mean that no bring any value as result. this is the sentences applied: SELECT SECURITY."ID_ROLE", SECURITY."NU_I_PASS" FROM "SECURITY" SECURITY WHERE SECURITY."NU_I_ORG" =? the field nu_i_org is char type. If i use static parameter, this run ok: SE...

sql datasource and update query
well i m using asp.net 2005 and sqlserver 2005,,i have a page called as sme_insert.aspx thr which user can enter new records for sme_id,name,no agency_id ,,etc..i have used sqldatasource and formview for tht,,,well for agency_id i have replaced the textfield with a dropdown list and it displays the agency_id values from the table sme_master..till now everything is working fine,,whn i go for inserting a new record and select a value from the dropdownlist,the value doesnot gets inserted in the table,,so everything is working fine except the dropdownlist,,,another prob is tht the dropdown list ...

SQL Query Parameters #2
 I'm trying to build my item search page and had everything working fine with the code dreamweaver had spit out for me, but I guess my server doesn't like dreamweaver code, so I'm back to the drawing board...I got all my databindings done, but I can't get the SQL line to read the search entries from the URL it generates...I'm using this code to connect to SQL Server <asp:SqlDataSource id="searchres" runat="server" ConnectionString="server=mssql01.1and1.com;database=db254304491;uid=dbo254304491;pwd=baconbits" SelectCom...

Passing Parameter to an SQl Query
I am trying a pass a parameter to a query so that when the page loads the information that is loaded is dependant on the passed parameter. This is the code that is supposed to pass the parameter ID to the SQl query : If Not Page.IsPostBack Then If Request.QueryString("Id") Is Nothing Then Response.Redirect("addsections.aspx") Else Dim Id As Integer Id = Request.QueryString("Id") PageIdLabel.Text = Id   SqlSelectCommand1.Parameters.Add("@id", Id) SqlDataAdapter1.Fill(DataSet11) This is then the query that is supposed to use the code : SELECT     Section...

How to put a parameter in Sql Query?
I have a sql query like this Select * From ZT_TransFmt7_Tail Where isNumeric(tra_date) = 1     And tra_date < Convert(Numeric,Convert(varchar(10),DateAdd(Month,-3,GetDate()),112))-19110000 in this line   → DateAdd(Month,-3,GetDate()),112)) - 19110000-3 is a default value can not be changed. not I want to set it as a parameter and will reference to another column_AA of Table_AA  how to declare ? and how to change this sql query? and ... most important is ... is this way can work? or is impossible to done? thank you very much  Ye...

Web resources about - query parameter - asp.net.sql-datasource

Parameter - Wikipedia, the free encyclopedia
A parameter (from the Ancient Greek παρά , "para", meaning "beside, subsidiary" and μέτρον , "metron", meaning "measure"), in its common meaning, ...

fb_source Parameter - Facebook-Entwickler
This document lists the complete list of values for the fb_source parameter that is returned with th...

Google / Firefox Parameter - Flickr - Photo Sharing!
HS what? www.seroundtable.com/archives/017105.html

Cat 8: TIA performance standards committee chairman previews parameters of forthcoming twisted-pair cable ...
Category 8, the next-generation twisted-pair cabling specification, is still in development, but the outlook looks quite positive that it will ...

Philippine president asks for 'parameters' of Mayon Volcano tours
Philippine president asks for 'parameters' of Mayon Volcano tours People's Daily Online ... 26, 27, 28, 29, 30, 31. Jan, Feb, Mar, Apr, May, ...

Alternatives to boolean parameters
... ) { PrintLine ( item . Name ); if ( printDetails ) { PrintLine ( item . Description ); } } } Aha! Now can see that the boolean parameter ...

Exporting Error (Error in Parameter...: Apple Support Communities
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability ...

Apple tech monitors device aging, adjusts operating parameters to ensure optimal performance
... describing a method of monitoring the aging of a device's electronics, comparing the data with a global average and modifying operating parameters ...

Is it poor programming practice to pass parameters as objects?
... a free, community-powered network of 100+ Q&A sites . Risser asked: So, we've got a guy who likes to write methods that take Objects as parameters, ...

Parameter Short Film Competition sponsored by Grolsch
Grolsch presents Parameter The A.V. Club Short Film Competition Please Enter Your Date of Birth Ineligible entry Enter For people over the age ...

Resources last updated: 12/21/2015 7:35:07 PM