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 like

SELECT * 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
4/30/2008 8:43:48 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

9 Replies
582 Views

Similar Articles

[PageSpeed] 14

 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
4/30/2008 9:11:58 AM

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
4/30/2008 9:22:36 AM

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
4/30/2008 11:19:08 AM

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
4/30/2008 11:30:48 AM
TATWORTH:

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

What the first "IF" line is needed for? :-)
0
SergeyS
4/30/2008 11:49:31 AM

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
  END

And 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
5/1/2008 3:40:48 AM
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
  END

And 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:

(if @fk_country > 0, which is the value for "all countries")" .
 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)
  SELECT * FROM WebUsers
  WHERE fk_Country = @fk_Country
ELSE
  SELECT * FROM WebUsers

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)

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
5/1/2008 5:48:22 AM

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
5/1/2008 11:29:30 AM
TATWORTH:
The first IF is to convert null values to 0. It thus simplifies the second IF

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

As 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
5/1/2008 3:50:09 PM
Reply:

Similar Artilces:

Getting error "Login failed for user ''. The user is not associated with a trusted SQL Server connection" on trying to authenticate a user
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 snip
A 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'
Kim Zolciak Shares Glam Post-Heart Surgery Selfie, Calls Procedure 'a Success'

Wall Street Regulator (FINRA): Procedures, Recordkeeping, Education Key For Compliant Social Media
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 ...
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 ...

Resources last updated: 11/27/2015 11:36:09 PM