|
It's a really, really bad idea. SQL Server will generate a query plan for the first set of parameters, cache it, and reuse it for all future calls to that stored procedure regardless of how the parameters change.
Stored procedures work best when the shape of the query - the tables involved, how they're joined, the columns filtered by the WHERE clause, and the order of the output - remains stable. They don't work if you try to change the shape of the query.
SQL Server accepts parameterized SQL text - with @variables in the command text - and it caches the query plans for parameterized queries. It also tries to automatically deduce where parameters should be if no parameters were used - this is referred to as auto-parameterization. It still caches the query plan even if it can't deduce the parameters. However, it ranks the cached query plans so that a plan for a stored procedure is likely to be kept longer than a parameterized query, which is likely to be kept longer than an auto-parameterized query, which in turn is likely to be kept longer than a non-parameterized query.
Unless you really want to prevent users from SELECTing from the base tables - using the security barrier you can erect using stored procedures - I strongly recommend using parameterized, dynamically-constructed queries submitted as text.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
I want some help regarding the Northwind database that comes with MS-Access.
when we opens the database it shows the various forms, reports, tables, queries. I want to know how can i use this database as an application.
Is there a way to build an application of this DB?
Or any other way by which i can use the DB, by creating the application in programming language.
|
|
|
|
|
humdumof wrote: I want to know how can i use this database as an application.
Yes and No:
The No bit - a database is not an application it is just a collection of data.
The Yes bit - see below.
humdumof wrote: Is there a way to build an application of this DB?
Yes - you will need MS-Access or another application that can connect to an MDB database to do this.
humdumof wrote: Or any other way by which i can use the DB, by creating the application in programming language.
See just above - to quote a couple of programming languages you could use - C#, VB.Net... .
You always pass failure on the way to success.
|
|
|
|
|
thanks for replying.
GuyThiebaut wrote: Yes - you will need MS-Access or another application that can connect to an MDB database to do this.
I know i need to use access for using this. But when i open the file it opens up the whole MS-Access, It having various forms, and that also can be used after opening MS-access. Cant we open these forms directly like an application.
I think for this i have to use programming lang?
I am familier with C#.net.
But will need some answer,
a) do i have to create from scratch, or can save my efforts with some tools who can do this.
b)How to use the reports presents in DB, there is tool in Visual Studio crystal reports, can this help me.
Or any suggestion how I start/create my application
|
|
|
|
|
humdumof wrote: I know i need to use access for using this. But when i open the file it opens up the whole MS-Access, It having various forms, and that also can be used after opening MS-access. Cant we open these forms directly like an application.
In the Access database window: drag and drop the forms, reports etc onto the desktop.
You will then be able to open these forms directly without having to go through the switchboard etc.
In answer to a and b: I think it would be a good idea to pick up a manual on Access.
(Access has all the tools you need to write tables,queries,forms,reports within it)
You always pass failure on the way to success.
|
|
|
|
|
Is there any sql query that tells the ip address of the database server on which the query run.
Regards
Rajesh
Rajesh Kumar
modified on Saturday, March 8, 2008 12:54 AM
|
|
|
|
|
Hello!
This should help your cause:
exec master..xp_cmdshell 'ipconfig'
and don't forget to enable xp_cmdshell. Following is the way to enable it:
Goto: Start Menu => All Programs => Microsoft SQL Server 2005 => Configuration Tools => SQL Server Surface Area Configuration
Click Surface Area Configuration for Features
Click xp_cmdShell and enable it.
HTH, Regards,
Adeel
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
|
Thanks...
But I do not have access to tht server. May be it is hosted somewhere in US.
How to proceed ?
|
|
|
|
|
IS THERE ANY PROVISION TO ASCERTAIN NEXT AND PREVIOUS RECORD IN SQL SERVER 2000 ?
Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT.
|
|
|
|
|
Sonia Gupta wrote: IS THERE ANY PROVISION TO ASCERTAIN NEXT AND PREVIOUS RECORD IN SQL SERVER 2000 ?
Databases are set based. There is no concept of next and previous row. Any database that does this is working on a fiction.
ORDER BY can be used to impose an order on the result set, but again this is a fiction to make things easier for the calling application.
It may appear that a database is returning data in a specific order all the time, but this is merely a side-effect of the physical implementation and not something you can rely on.
|
|
|
|
|
Don't shout. Please use mixed case.
You can find next and previous records, but the queries are likely to be fairly "hairy". As Colin states, SQL is set based so you have to figure out how to work on the set.
|
|
|
|
|
oK PeTE, iT's miXeD CAsE fRoM heRE oN In.
|
|
|
|
|
|
what are the minimum tables required to many to many relation ship.?
raja
|
|
|
|
|
You need three tables: two tables that represent entities that are related with each other and a third table to link them.
|
|
|
|
|
|
Hi,
I am woking with SSRS Matrix Control.
I have the column header -- year, Row--Programme and Data -- No of Students.
YEAR
----------------
PROGRAMME | NO OF STUDENTS
The Matrix will display all the years as columns which i have in the dataset. I have tried hiding the colums by giving a condition in the Hidden property. It does work leaving blank spaces between the columns.
Can anyone tell me How to getrid of the blank spaces between the colums or how to delete the colums of a Matix based on a condition Please?
Thanks & regards,
Payal
|
|
|
|
|
Hi all
How i can remove identity property of a column.
I will be graetful to you
Thanks
Sebastian
|
|
|
|
|
If you are Using SQL 2005,
Go to your Table (Object Explorer) and select you table, Right Click and Click Design,after that, Go to the Column that has Identity and Click once or select it, Below you will see its Properties and look for a property name "Identity Specification" user "Table Designer" , Click the "+" to Expand and Under it you will see the property named "(Is Identity" and is selected "Yes", change it and set it to "NO" And Close the Designer and if it ask you to save the changes say "YES" Then your Identity will be gone.
Hope this Helps
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Thanks for your quick reply, but i want to do this in run time, so should i get any query snippet for that
once again thanks
sebastian
|
|
|
|
|
|
ok,i am looking into it, thanks once again
sebastian
|
|
|
|
|
Mention database type(access/sql server2000 / 2005).
Do good and have good.
|
|
|
|
|
its sql server 2005
Thanks for your response
sebastian
|
|
|
|
|
Hi all, thanks for looking
I need a storedprocedure that retrieves rows based on parameters
Example 1:
SELECT * FROM Users WHERE Name LIKE '%' + @str + '%'
This will return all rows that have the word @str in Name column
If i pass '' or '%' it will return all the rows
I need the same but for an int column
Example:
SELECT * FROM Users WHERE UserID = @UserID
If i pass a number, it will return a row of the user with UserID = @UserID
But if no parameter is passed it wont work
My question is
Is there any way i can make this last query to return all the rows if no @UsersID is passed
Ill be calling this SPs from ASP.NET
Im giving this examples, i know they might seem very easy, and you may say, make 2 SPs
But in fact, Ill have more than 5 diferent filters, all applied to the same table, and any combination of this parameters is valid (param 1 and param2, param 1 only, param 5 only, param 1 2 and 4, and so on), so if i implement this in separated SPs ill end up having more than 20 almost equal SPs
Any advice will be appeciated
Thanks in advance
Alexei Rodriguez
|
|
|
|