|
This will seem silly but:
ALTER PROCEDURE [dbo].[SP_SearchGroundedVehicles]
@vin varchar(17)
AS
BEGIN
SET NOCOUNT ON;
if count(@vin) = 17
begin
SELECT V.vin
,C.FirstName
,C.LastName
,C.AccountNum
,D.DealerShipName
,Contrt.MaturityDate
FROM ContractInfo Contrt
Inner Join VehicleInformation V ON Contrt.VehicleID = V.VehicleID
Inner Join DealerShips D ON Contrt.DealerShipID = D.DealerShipID
Inner Join Customer C ON Contrt.CustomerID = C.CustomerID
Where VIN = @VIn
end
ELSE
begin
SELECT V.vin
,C.FirstName
,C.LastName
,C.AccountNum
,D.DealerShipName
,Contrt.MaturityDate
FROM ContractInfo Contrt
Inner Join VehicleInformation V ON Contrt.VehicleID = V.VehicleID
Inner Join DealerShips D ON Contrt.DealerShipID = D.DealerShipID
Inner Join Customer C ON Contrt.CustomerID = C.CustomerID
end
You can take a look at sp_executesql to see about building SQL query strings on the fly for more difficult dynamic queries.
|
|
|
|
|
Hi Michael,
No worrys! All we do is try and keep trying until we get it I appreciate you reponse. Yeah upto a little ago I have done the same way to manage the show. But later this is how I did, I just tried building where clause dynamically and it works! May be you want to try if you come across with such scenarios.
WHERE VIN = CASE LEN(@vin) WHEN 17 THEN @Vin ELSE VIN END
Thanks for the reply.
-L
|
|
|
|
|
Yes, that certainly works and saves alot of typing.
In either case, the best 'optimized' version would be 3 separate stored procs. Since the SQL Server optimizes a SP by its first execution (ignoring later re-optimizations due to structure changes), one side of the IF statement will be ignored. In your case, one side is an index lookup and the other is a table scan. It may be best to put each side into its own stored proc and put the IF in another. This way both are optimized properly for execution.
Of course, this is a useless point if your tables are small.
|
|
|
|
|
I have a DetailsView (ID=CtrDetails) in Visual Studio 2005 with the following Columns:
-CustomerID
-Customer
How can I retrieve the value of the columm CustomerID ???
I expect something like "Where CtrDetails.Value=X" or something like that
THANX ¡¡¡
|
|
|
|
|
I have a table:
ID FirstName CreateDate UpdateDate
How would I update the UpdateDate when making an entry in the table? The createDate has the getdate() function allready...
Illegal Operation
Making Computer Software Talk
|
|
|
|
|
is ur UpdateDate field value varchar or datetime and u need to update from ur application or directly in SQL Query
regards
_____________________
Proud to be Albanian
_____________________
|
|
|
|
|
The value of updatedate should be datetime as well because I want to update the date in sql when the entry is made.
This must be executed in the Stored procedure...
Illegal Operation
Making Computer Software Talk
|
|
|
|
|
Investigate the use of insert and update triggers on your table. The insert trigger can set CreateDate and UpdateDate to the current time. The update trigger should ensure that CreateDate is not changed and that UpdateDate is set to the current time.
Chris Meech
I am Canadian. [heard in a local bar]
When I want privacy, I'll close the bathroom door. [Stan Shannon]
BAD DAY FOR: Friendly competition, as Ford Motor Co. declared the employee parking lot at its truck plant in Dearborn, Mich., off limits to vehicles built by rival companies. Workers have to drive a Ford to work, or park across the street. [CNNMoney.com]
Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]
|
|
|
|
|
Hi,
i am having a stored procedure like this:
CREATE PROCEDURE SP1 @arg1 varchar(100)
select * from emp where empid in (@arg1)
the argument @arg1 will depend on the user's choice in the front end. An example of this argument may look like this:
'emp1','emp2','emp6'
My problem is "I am unable to pass this type of argument for executing the stored procedure"
Please help. Thank You.
Please Plant Trees to Save the Mother Earth.
|
|
|
|
|
The above problem i am facing in SQL Query Analyser .
|
|
|
|
|
Would that work in a languge like C# or VB.NET? For example, if I had some code like this:
public void DoStuff(string arg)
{
if (arg)
{
DoSomething();
}
else
{
DoSomethingElse();
}
} where arg is: (this.someField == "Hello") || (this.someOtherField == "World"))
No? So why do people seem to think that kind of thing should work in SQL. (Sorry, I just see this question over and over on various forums)
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Possible solutions:
* You could pass in multiple arguments.
* You could create some dynamic sql, concatenating strings together and executing it. (Not recommended unless you absolutely have no other viable solution as it is a security risk)
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
This worked fine for me:
create proc SP1 @arg1 varchar(100)<br />
as <br />
select @arg1 = 'select * from emp where empid in(' + @arg1 +')'<br />
<br />
exec (@arg1)
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
But remember, there are security issue using dynamic SQL execution.
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Hi,
I my stored procedure I have something like:
SELECT CategoryID_PK, CategoryName FROM tblCategories;
If this select goes through fine, what is the default value returned? Is it 0? Is it necessary to have a RETURN 0 after the SELECT statement? Or when is the RETURN 0 required?
If the stored procedure must just do a simple SELECT statement, must they still be between a BEGIN and END ? When is this required?
I hope someone can help me clear things up.
Regards,
ma se
|
|
|
|
|
Return values are only useful if you read them. No need to return at the end of a stored proc.
BEGIN , END are used for code block seperation such as in an IF/ELSE statement. They are not necessary anywhere else.
|
|
|
|
|
Hello,
I am getting DB_E_DATAFIELD_OVERFLOW run-time error while retriving the value of the field using value property on Field object. I get the error while retrieving the records having value greater than 2147483648. If the data value is suppose 1000023 than all is fine.
Query is like : select F_DOCNUMBER from fndocument where F_DOCNUMBER > 10000000;
Datatype of column is 19 i.e unsigned long..
Please help... Its really urgent
....Thanks in advance.....
Suraj Gupta
|
|
|
|
|
You don't give enough information to help:
* What database are you using?
* What are you using to access the database?
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Firts of all thanks for responding.
By the way we are using SQL Server. And using ADODB to access the database. We have our own OLEDB provider. It pass the recordset to the client in tabular format.
Anything else, that can help you out to know exact problem.
Thanks and Regards,
Suraj Gupta
Suraj Gupta
|
|
|
|
|
Please, if any one can help me out.
Thanks and Regards,
Suraj Gupta
Suraj Gupta
|
|
|
|
|
hi there,
I am connecting to a sql server express 2005 database which is located on another developers machine in the company. he has given me the username and password and i can read and write to this db using vs2005 no problems.
i can also added new tables and everything through the management studio, but when i add stored procedures, it does not seem to commit to the database, when i click save it asks for a local destination but i want it to save onto the sql server.
i can add stored procedures through vs 2005 no problems! this does not make sense?
|
|
|
|
|
NewbieDude wrote: but when i add stored procedures, it does not seem to commit to the database, when i click save it asks for a local destination but i want it to save onto the sql server.
When you click "save" it wants to "save" a file, naturally. You need to "execute" (F5 - if you want the keyboard shortcut) the SQL in order to apply it to the database.
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Hi
I am developing a website in which we sale/purchase vehicles of different categories eg Cars,boats,trucks,aircrafts etc.
I am using MySQL at backEnd with ASP.net. I need to develop a database which should contain all the Makes(BMW,Mercedez etc) and their relevent Models.
From where can I download such database.
Thanks
Lets work it Out.........!
|
|
|
|
|
dont really understand the question. Do you want to know where to download MySQL or where to download an actual database.
You can get mySQL from
http://dev.mysql.com/downloads/ [^]
As for the database the idea is you build it yourself. Shouldnt be too hard for someone with a Masters.
Jon
|
|
|
|
|
I have MySQL and I need to download the database. I have the idea to build the database But i need to escape from entering so much data by my self. As you know there are so many Makes and there Models in the market and it will be very hard to enter entire data by mySelf.
I hope you understand now. Thanks for rply
Lets work it Out.........!
|
|
|
|