|
thanks
|
|
|
|
|
I have a question about stored procedures Vs dynamic SQL.
I'm a noob but from the googling I've done this seems to be a hot topic so I dont want to start an arguement but I'm stuck.
The website I am working on has a data access layer that takes in a SP name & parameters. It runs the SP and returns the resulting data to the page that requested it.
This is easy & quick to maintain for me.
However, my users want to be able to search the customer using search criteria parameters that can be different each time. By that I mean they want to be able to search for customers using whatever data they have. Sometimes they will have a last name, other times a last name & birth date, sometimes a phone number and a first name.
This doesnt seem to lend itself to a SP - the parameters change each time so the solutions seem to be:
1. A SP that has every criteria in it and use LIKE '%%' when the customer doesnt use one of them
2. Have the data access layer dynamically build the required SQL and then insert it into the SP somehow. (Which sorta kinda sounds like SQL injection)
3. Build an SP for each group of search criteria (Name, DOB, Phone numbers, TIN) and then only allow the users to use one search criteria group at a time.
Neither solution sounds that great to me. (There may be others but like I say I'm a noob)
Which leads me to think that maybe in this case I should use Dynamic SQL - e.g. have the data acccess layer create the SQL statement and then execute it against the database without using an SP.
But then that breaks my rule of only using SP (which I originally created cos I thought SP ran quicker but then I read that according to some guys at MS that dynamic SQL runs as quickly as SP in the .Net environment) so now I'm confused.
|
|
|
|
|
If you want to continue using SPs, you could always check the value of the parameter to see if it is null or not in the where clause.
For instance:
SELECT ....
FROM table1
WHERE
(Col1 = @Col1 OR @Col1 IS NULL) AND
(Col2 = @Col2 OR @Col2 IS NULL)
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
OK so it ignores the where col1 = @col2 part of the statement if @col1 is null ?
|
|
|
|
|
Effectively yes because it evaluates the boolean to true. If you want to shortcircuit it, you can swap the order of the tests around.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Sorry - I'm not sure what you mean by shortcircuit it. Could you explain that please ?
|
|
|
|
|
Short circuiting simply refers to the case where a boolean condition halts before it evaluates the next part. A simple C# demonstration should show this:
DateTime? dtToday = null;
if (dtToday == null || dtToday == DateTime.Now) The second part never executes because dtToday is null and so the conditions for an or statement is satisfied. In the case of an AND condition, the second part of the statement would not be executed if the first part is false.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
SQL Server compiles an execution plan for a query the first time it's run. That plan is then cached and reused. It will be discarded either when it's aged out of memory (i.e. there was enough memory pressure for query plans to be removed from memory, and this was less recently used/important than other cached plans) or when the statistics on the index columns have changed enough to force a recompile, or you add or remove some indexes on tables used in the query.
The same is true for stored procedures - their query plans are just weighted higher than dynamic SQL. It's got nothing to do with the .NET environment, it applies to all interfaces.
I'm concerned that queries using LIKE '%%' or OR @param='' etc will have a query plan constructed based on the first set of arguments supplied, but which will then be a poor plan for a different set of arguments, and SQL Server will reuse that initial plan rather than construct a better one. This is something I don't have data on, however - you should profile this. In this case I think dynamic SQL could be better.
You can mark a stored procedure to be recompiled every time by specifying WITH RECOMPILE. However, you incur the overhead of computing the plan every time; caching the dynamic SQL's plan might work better.
If your query does not use parameters, SQL Server will attempt to auto-parameterise the query so that it can reuse the plan for more queries. That is, it will replace explicit literal values with parameters. However, it may be better to mark the parameters yourself if there are literals in the query which don't change.
Stored procedures are useful as a security boundary - you can GRANT a user permission to EXECUTE a stored procedure even if they're denied permission to perform the operations of the SP directly.
|
|
|
|
|
Hello
I have a computer with sql server 2000 and sql server 2005.
for some reason the instance of sql server 2005 is not published in the network appropriately.
When I try to connect to the database engine I am going to the server name and I browse.
I go to network servers and I can find the instance of SQL Server 2000 but I cant find the instance of sql server 2005.
Does any one know what are all the options for registering the sqlserver instance ?
Clint
|
|
|
|
|
Can't you connect to it? Or you just can't find it in the list of servers in connection dialogs?
for MS SQL Server 2005 to be browsable, you must run the SQL Server browser service.
Start->SQL Server Configuration Tools->SQL Server Configuration manager
make sure that the SQL Server browser service is running.
I Hope this helps.
|
|
|
|
|
|
Need a little more info here.
What is your database?
If it is Sql server then:
First you will need a SqlConnection object:
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx[^]
Then you will need a Sqlcommand object:
http://msdn2.microsoft.com/en-us/library/system.data.dataset.aspx[^]
In your Sqlcommand object you set the stored procedure name, add the parameters and set them.
Next the question is: Does this stored procedure return data or insert records or what?
Based on that answer you will do a ExecuteReader or ExecuteNonQuery.
Hope that helps.
Ben
|
|
|
|
|
Googled it[^] 
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy)
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
jayvaishnav82 wrote: how we can use stored proceder with asp.net
You don't. ASP.NET is a framework for building web applications.
ADO.NET is the component of the .NET Framework that deals with databases.
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "StoredProcedureName";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = myConnection;
cmd.ExecuteNonQuery();
SqlDataReader reader = cmd.ExecuteReader();
|
|
|
|
|
Good one Collin. Now if only we had a bitch-slap smiley down below. 
|
|
|
|
|
Dear sir
thanx for responding pls send me full method for storedproceder with parameters i m beginer in asp.net(c#).
jayant vaishnav
jayant.vaishnav@rediffmail.com
thanx
|
|
|
|
|
jayvaishnav82 wrote: method for storedproceder with parameters
My previous post gave away enough information for you to have been able to look up the answer on MSDN.
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "StoredProcedureName";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = myConnection;
cmd.Parameters.Add("@parameterName", parameterValue);
cmd.ExecuteNonQuery();
SqlDataReader reader = cmd.ExecuteReader();
|
|
|
|
|
select * INTO [Instance1].targetDB.dbo.TargetTable from [Instance2].SourceDB.dbo.SourceTable
What I would like to do is copy the table from one instance to another.
How do I specify copying from one instance to another. I have already created the database.
The target is Sql 2000 and I have full control.
The source is Sql 2005 and I have only select permissions.
How would I forulate a correct select into statement?
Thanks,
Nick
--------------------------------------------------------
1 line of code equals many bugs. So don't write any!!
My mad coder blog
|
|
|
|
|
It should be an insert statement
Insert into [Instance1].targetDB.dbo.TargetTable
select * from [Instance2].SourceDB.dbo.SourceTable
Hope that helps.
Ben
|
|
|
|
|
the problem is, how do I specify the Instances because "[Instance2].SourceDB.dbo.SourceTable" doesn't work. It has too many qualifiers.
I think I add a linked server on my side. But how do I specify in one line which instance to use?
--------------------------------------------------------
1 line of code equals many bugs. So don't write any!!
My mad coder blog
|
|
|
|
|
Your [Instance2] includes the sqlserver name right?
You know it might just be easier to DTS the table that is the way I do most of my table moving. Then you don't need to worry about link servers or any of that.
It is the Import export Data. If you have access to sql server 2005 management studio, you go to the database you want to export a table from right click -> tasks -> export data.
Then you just need to enter your destination sql server and database. Then select the table you want to copy.
Hope that helps.
Ben
|
|
|
|
|
You need to link the two instances together before you can do that. See: sp_addlinkedserver in the books online.
|
|
|
|
|
Hi
What is the best way to manage the user logins in the database for a school information system where there are students, teachers, system administrators, etc. Would you have separate tables for students, teachers and administrators, each with columns for username and password or one Users table with all the info including usernames and passwords? The problem is then that teachers and students may have different information that is captured for the database.
Kobus
|
|
|
|
|
kbalias wrote: Would you have separate tables for students, teachers and administrators, each with columns for username and password or one Users table with all the info including usernames and passwords?
I prefer to use single table with all info
kbalias wrote: The problem is then that teachers and students may have different information that is captured for the database.
Add one column in the user table for identifying the user type. It can be something like user_type and store the user type in that colunmn
|
|
|
|
|
I would normalise that out a bit more, and have a role table with the user table having a foreign key to the role table. By doing this, you can associate permissions to roles so you could have a student being able to view an item, and a teacher being able to view or edit it.
Deja View - the feeling that you've seen this post before.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
