stored procedure to get all users or all users of a specific country
hi all
i've got two tables called "webusers" (id, name, fk_country) and "countries" (id, name)at the meantime, i've a search-page where i can fill a form to search users. in the dropdown to select the country i included an option which is called "all countries".
now the problem is: how can i make a stored procedure that makes a restriction to the fk_country depending on the submitted fk_country parameter?
it should be something likeSELECT * FROM webusers
(if @fk_country > 0, which is the value for "all countries")
{
WHERE fk_country = @fk_country
}who has an idea how to solve this problem?
|
0 |
|
KingDario 
asp.net.sql-datasource
29906 articles. 
0 followers.
9 Replies
582 Views
Hi,
Look at the sample below and let me know
SELECT
*
FROM
webusers
WHERE
@fk_country = 0
OR @fk_country = fk_country
Zafar Iqbal
VP Technology
Hansvits
** Please mark as ANSWER if my reply helped you
|
|
0 |
|
ziqbalbh if(@fk_Country = 0)
set @fk_Country = null
select * from WebUsers
where fk_Country = isnull(@fk_Country,fk_Country)
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever.
|
|
0 |
|
ramireddyindia ramireddyindia:
if(@fk_Country = 0)
set @fk_Country = null
select * from WebUsers
where fk_Country = isnull(@fk_Country,fk_Country)To escape extra processing why not simply:
IF (@fk_Country IS NULL)
BEGIN
SELECT * FROM WebUsers
END
ELSE
BEGIN
IF (@fk_Country > 0)
SELECT * FROM WebUsers
WHERE fk_Country = @fk_Country
END
|
|
0 |
|
SergeyS Try in your stored procedure
IF @fk_country IS NULL SET @fk_country = 0
IF @fk_country = 0
SELECT * FROM webusers ORDER BY NAME -- Select all
ELSE
SELECT * FROM webusers WHERE fk_country = @fk_country
Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped.
|
|
0 |
|
TATWORTH What the first "IF" line is needed for? :-)Try in your stored procedure
IF @fk_country IS NULL SET @fk_country = 0
IF @fk_country = 0
SELECT * FROM webusers ORDER BY NAME -- Select all
ELSE
SELECT * FROM webusers WHERE fk_country = @fk_country
|
|
0 |
|
SergeyS SergeyS, In your below query, what happens if the user pass @fk_country value as zero, it doesn't return any results. Please Verify It.
IF (@fk_Country IS NULL)
BEGIN
SELECT * FROM WebUsers
END
ELSE
BEGIN
IF (@fk_Country > 0)
SELECT * FROM WebUsers
WHERE fk_Country = @fk_Country
ENDAnd one more point,
KinqDario Said that he will send the value 0, if user select the Allcountries in dropdownlist.
if he select any particular country, then he will send the CountryId.
So Never he send null to storedprocedure.so always your query never goes into if condition.
it always go into else condition only.
if you check the timecomplexity, it will always execute 3 instructions in your query.
BUt if you check below query, it will execute 3 instructions in one case, and 2 instructions in another case.if(@fk_Country = 0)
set @fk_Country = null
select * from WebUsers
where fk_Country = isnull(@fk_Country,fk_Country)
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever.
|
|
0 |
|
ramireddyindia SergeyS, In your below query, what happens if the user pass @fk_country value as zero, it doesn't return any results. Please Verify It.
IF (@fk_Country IS NULL)
BEGIN
SELECT * FROM WebUsers
END
ELSE
BEGIN
IF (@fk_Country > 0)
SELECT * FROM WebUsers
WHERE fk_Country = @fk_Country
ENDAnd one more point,
KinqDario Said that he will send the value 0, if user select the Allcountries in dropdownlist.
if he select any particular country, then he will send the CountryId.
So Never he send null to storedprocedure.so always your query never goes into if condition.
it always go into else condition only.Yes, I see now he wrote:
I thought "NULL" was for "all" countries. But it seems like he probably meant 0 for "all" countries. So, in this case my query should be:(if @fk_country > 0, which is the value for "all countries")" .IF (@fk_Country > 0)
SELECT * FROM WebUsers
WHERE fk_Country = @fk_Country
ELSE
SELECT * FROM WebUsersif you check the timecomplexity, it will always execute 3 instructions in your query.
BUt if you check below query, it will execute 3 instructions in one case, and 2 instructions in another case.if(@fk_Country = 0)
set @fk_Country = null
select * from WebUsers
where fk_Country = isnull(@fk_Country,fk_Country)Actually, the declarative T-SQL syntax could not be interpreted in the number of instructions that way. Basically my query states
- check if CountryId is supplied
- if yes, then get all the records containing this CountryId value in CountryId field
- if no, then just grab all the records
No processing on each row is performed, except for very fast fetching on the primary key in the case when CountryId is supplied.
On your query always "where fk_Country = isnull(@fk_Country,fk_Country)" processing will ocur even if what we need is just to grab all the records.
|
|
0 |
|
SergeyS The first IF is to convert null values to 0. It thus simplifies the second IF
Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped.
|
|
0 |
|
TATWORTH The first IF is to convert null values to 0. It thus simplifies the second IFIF @fk_country IS NULL SET @fk_country = 0
IF @fk_country = 0
SELECT * FROM webusers ORDER BY NAME -- Select all
ELSE
SELECT * FROM webusers WHERE fk_country = @fk_countryAs far as I understand, the above is equal to
IF (@fk_country = 0) OR (@fk_country IS NULL)
SELECT * FROM webusers ORDER BY NAME -- Select all
ELSE
SELECT * FROM webusers WHERE fk_country = @fk_country
|
|
0 |
|
SergeyS 
Similar Artilces: Hi, I created aspnetdb on Sql Server 2005 and modified my web.config LocalSqlServer connection string to connect to the Sql Server instead of mdf file on Sql Server. When i click Log In button on my login control i am getting the error "Login failed for user ''. The user is not associated with a trusted SQL Server connection" But when i bind a radio button list or any control to select from aspnet_users or roles table data is displayed fine without any login errors (i am using aspnetdbConnectionString given below for binding data). Why is it happening only when i authenticate but...
Get user status stored procedure
Hi i want to get User status updates to show where they are connected i.e they are friends, this is what my tables look like; UserStatusUpdates - ID(PK), UserID(FK), Status Users - ID(PK), Username UserFriends - ID(PK), UserID(FK), FriendUserID(FK) And this is what my stored procedure looks like; SELECT DISTINCT UserStatusUpdates.* FROM UserStatusUpdates JOIN UserFriends on UserStatusUpdates.UserID = UserFriends.FriendUserID order by UserStatusUpdates.AddDate desc The problem is the procedure is returning all the statuses, what do i need to do to get this working, th...
Run Sql Stored Procedure as user
I have a web app which writes to a database. The initial problem was when the database was writing it was writing as "NT AUTHORITY\NETWORK SERVICE" and denied access to writin, after i added relevant permissions this error disappeared. The problem i have now is when the data is entered by the user i need to capture some details from them (username etc). Currently when the INSERT command is run, it runs with "NT AUTHORITY\NETWORK SERVICE" credentials and not the domain user credentials. How could i change it so user credentials are used and not Network Service? Thanks ...
How to authenticate users using user login and password stored in SQL database?
Hi. I have a DetailsView with Bound Fields "Login" and "Password". This informations are stored in SQL database. How to solve such authorization? How to compare password stored in database against passowrd typed by user? Is this a good idea to use CustomValidator control to write some checking procedure?. Regards. Pawel. When you click the login button. Perform a select query to your database that gets the password for the given loginname. Then compare the two passwords. Good luck!Johan TheunissenMCPD, MCSE, MCTS BizTalk 2006==============================Please mark the most helpful rep...
How to authenticate users using user login and password stored in SQL database?
Hi. I have a DetailsView with Bound Fields "Login" and "Password". This informations are stored in SQL database. How to solve such authorization? How to compare password stored in database against passowrd typed by user? Is this a good idea to use CustomValidator control to write some checking procedure?. Regards. Pawel. A custom validator control probably isn’t the best choice to validate the user password. Typically websites avoid acknowledging that the username is in the system if the password is incorrect. I’d take a look at the ASP.Net Membership Provider. You can cr...
A .NET Framework error occurred during execution of user-defined routine or aggregate -While creating a SQL SERVER 2005 Stored prodecure in VS.NET 2005
Running [dbo].[insertlogin] ( @log = hiten, @pas = hiten ).A .NET Framework error occurred during execution of user-defined routine or aggregate "insertlogin": System.Data.SqlClient.SqlException: Must declare the scalar variable "@Log".System.Data.SqlClient.SqlException: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.Pro...
Anomalies in user country info and user country histogram
Hi, I occasionally click on the "Online users" ... *More info ...* link in the forum website home page to see what countries users are from. I have noticed inconsistent and sometimes erroneous country information being displayed. Anomalies include: 1. The country for a specific user sometimes changes between one web page request and another. 2. The country is sometimes indicated as unknown, and sometimes as known. This creates the impression that there is a simple bug in the web app that generates the page - a bug that could easily be corrected. The fact that the error has ...
User IP, User Country
Hi freinds, i'm working whith C# in Visual Studio 2005. i want to know what is the best way to get an user country. i tryed to user 2 webservices , one to get the IP and the other for the country. somthing like: UserCountry=MyWebservice1.GetCountry(MyWebservice1.GetIP); but it always gives me the IP of my Modem. Please Help. Hi, At first, you should have database which store the key value pair about IP and country. Then you should make your webservice to detect ip and then change them into country. However, I am afraid that there would be some wrong infor...
Any SQL or Stored Procedure to list user and table permission mapping?
Without Sybase Central, Is there any SQL or Stored Procedure to list user and table permission mapping? ...
In debug mode I get Login failed for user ''. The user is not associated with a trusted SQL Server connection.
The error msg only appears when I try and debug. When using the site normally there is no problem?? Hello, The reference below is the solution of your problem. The user is not associated with a trusted SQL Server connectionPlease give us feedback no matter whether you get your answer.Please "mark as answer" if it's useful for youSincerely,Jerome...
How to Authenticate User and Access SQL Application Roles for Stored Procedure Execution
I have read a numerous of web sites pertaining to authentication and authorization but it simply is not coming to getther for me-yet. The goal is to authenticate users then allow acces based on their Application Roles. Thus far I have created User Groups which does work.Now I need to be able to access the Application Role for execution of the various stored procedures; somehow the execution of the stored procedures is occuring within authorization via the Application Roles. This is what I have thus far: <connectionStrings> <add name="...
Login failed for user 'Bachelorplace'. The user is not associated with a trusted SQL Server connection. (.Net SqlClient Data Provider)
please tellme how to resolve this error, i m using server studio management tool. and trying to connect using sql server authentication. my pc is over a domain. Login failed for user 'Bachelorplace'. The user is not associated with a trusted SQL Server connection. (.Net SqlClient Data Provider) check whether that sql server is allowing sqlauthentication or not.select that server -> right click it-> select properties -> in the window, select security -> check what is the server authentication. it should be sqlserver and windows authentica...
get error Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.
Hi,my interaction with database is completely lost. I cannot even open database and view tablesI get error Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.I am using VS 2005 Error log: SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.2008-09-07 13:10:03.51 Server Detected 1 CPUs. This is an informational message; no user action is required.2008-09-07 13:10:28.40 Server &...
database user defined fuction call inside stored procedure sql server 2k
How do you call functions inside stored procedures?I tried: "dbname.dbo.funcName(param1,param2)" si "dbname.funcName.." and"dbo.funcName.." i get same error: unkown database object "dbname.dbo.funcName" .I get the error in the aspx page i call the stored procedure from.I already checked permissions for users and functions and it's ok.Maybe someone has a clue.Paul Most of your UDF(user defined function) questions are answered in the link below. Hope this helps.http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htmKind regards,Gift Peddie I re...
Web resources about - stored procedure to get all users or all users of a specific country - asp.net.sql-datasource
Parliamentary procedure - Wikipedia, the free encyclopedia... of the House of Commons of the Parliament of the United Kingdom , from which it derives its name. In the United States, parliamentary procedure ...
Procedure is more a snap than a snipA QUEENSLAND doctor is bidding to set an unusual world record by performing the highest number of vasectomies in one day, with the help of fellow ...
Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...
Are patients charged drastically different prices for the same medical procedure? - Videos - CBS News ...Patients are often charged drastically different prices for the same medical procedures and research may link later bedtimes with gaining weight. ...
Vin Scully To Miss Dodgers Postseason After Medical Procedure... , who has been calling Dodgers games since the Truman administration, will miss the team’s playoff run as he recovers from a medical procedure. ...
'Painless' dental cavity procedure regrows tooth enamel... "cavity" a lot of people sweat thinking about painful injections and relentless drilling. But scientists in Britain have developed a new procedure ...
Kim Zolciak Shares Glam Post-Heart Surgery Selfie, Calls Procedure 'a Success'
Meeting regulatory requirements when using social media requires proper procedures, recordkeeping solutions, sufficient resources to monitor ...
US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor test performances by TSA screeners
Concession: Ryan promises Freedom Caucus he’ll delay discussion of reforming procedure for removing the ...Compromise. Like I said this morning, it sounds like they did a little horse-trading at their summit. Ryan wants them to give up their right ...