|
Simple way:
create procedure update_user
@user_name nvarchar(20),
@password nvarchar(20),
@firstname nvarchar(20),
@lastname nvarchar(20)
as
if @password is null or @password=''
update Users set firstname=@firstname, lastname=@lastname where user_name=@username
else
update Users set firstname=@firstname, lastname=@lastname, password=@password where user_name=@username
|
|
|
|
|
if @password = '' set @password = null
update Users
set [password] = isnull(@password, [password])
etc.
|
|
|
|
|
When using the "Data Source Configuration Wizard" you eventually get to a "Add Connection" dialog box. It has a drop-down box for "Server Name". I do not get any choices for the server name - why not? Next to the "Server Name" there is a "Refresh" button, but this does not help either.
Note - I have both sqlexpress and mssqlserver database engines opened. The micrsoft help does state: "Local SQL Server Express instances are not listed in the listing of available data sources." So I would at least expect my MSSQLSERVER database engine to appear?
|
|
|
|
|
Which ADO version first became a ".NET" version? And what is the current version of ADO.NET called - is it ADO version 2.8?
-- modified at 12:42 Wednesday 15th March, 2006
|
|
|
|
|
ADO.NET started with version 1.0 - There is not a direct upgrade path as the two are separate products that do more or less the same thing. Classic ADO is no longer updated.
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
3/17/06
Please correct me if I am wrong on the following (summarizes ADO versions vs ADO.NET versions, and response from Colin above!)
* Most importantly: ADO and ADO.NET are different products, ADO.NET is not an upgrade of ADO. This can be confusing because they both have "ADO" in the name, but they are still different products.
* The ADO.NET product is not an upgrade in version numbers from the ADO product.
* ADO latest version seems to be 2.8 and ADO will no longer be updated. A book on ADO 2.5 is of limited value for using ADO.NET!
* ADO.NET versions correspond to .NET framework versions? For example .NET 1.0, 1.1, 2.0 are the more-major versions of .NET that have been released.
* ADO is often referred to as classic ADO
|
|
|
|
|
Hi all
Does anyone has good tutorial for SQL Muiltiple Join
chandana
|
|
|
|
|
|
|
|
How to backup data from Online WebServer to My local Server
I am using SQL Server 2000.
N.Rajakumar B.E.,
Application Developer,
www.newdreamz.tk
|
|
|
|
|
Hello,
I am using SQL Server 2005 and I am just wondering if I can build the WHERE clause dynamically. I only want to build WHERE class if the my @VIN count is of exactly 17 charecters. Here is my code
*************************************************
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[SP_SearchGroundedVehicles]
@vin varchar(17)
AS
BEGIN
SET NOCOUNT ON;
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
if count(@vin) = 17
begin
Where VIN = @VIn
end
END
*******************************************************
But when I parse the above stored proc its throwing this message
********
Msg 156, Level 15, State 1, Procedure SP_SearchGroundedVehicles, Line 33
Incorrect syntax near the keyword 'Where'.
********
If it is possible to dynamically build WHERE clause please correct the way I am doing it or if it is not possible what is the best way of writing it?
Thanks
-L
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
