|
hi all
i am a beginner, i have written an insert stored procedure.
i need my SP to check for the ID field whether the data is already present.
pls help ,me.
ALTER PROCEDURE [dbo].[SP_SaveGroupClassification]
@SubGroupName nvarchar(50),
--@GroupId int,
@ParentId int,
@ClassType nchar(2),
@RootId int
as
begin
INSERT INTO GroupClassification
(GroupName, ParentId, ClassType, RootId)
VALUES (@SubGroupName,@ParentId,@ClassType,@RootId)
end
return
Here i need to check the value in (@SubGroupName not to be repeated.it is my key field.
thank u
senthil
|
|
|
|
|
Hi,
what you could do is try to get the ID for the specified @SubGroupName. If the ID does not exisit, you can insert the new row.
DECLARE @ID int<br />
SET @ID = (SELECT ID FROM GroupClassification WHERE GroupName = @SubGroupName ORDER BY ID DESC)<br />
IF(@ID IS NULL)<br />
BEGIN<br />
INSERT INTO GroupClassification(GroupName, ParentId, ClassType, RootId)<br />
VALUES (@SubGroupName,@ParentId,@ClassType,@RootId) <br />
END
|
|
|
|
|
hi,
it is working
Thank u Tobias Schoenig.
senthil
|
|
|
|
|
Use IF NOT EXISTS
IF NOT EXISTS(Select a from table where x=??)
INSERT INTO...
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
kssknov wrote: Here i need to check the value in (@SubGroupName not to be repeated.it is my key field.
You should declare it as your PRIMARY KEY. As soon as you do that the database will do the work for you by ensuring that it remains unique and is not repeated.
|
|
|
|
|
The field should either be a primary key or, or should be associated with a unique index. If a unique index exists for a field (or if it's a primary key, which is effectively a special type of unique index) then any attempt to add a record that already exists is guaranteed to fail. If two attempts are made simultaneously to add the same record, only one will succeed. In that regard, this approach is much better than the approach others here suggested of querying first to see if the record exists (two clients could both check for a record and discover it doesn't yet exist, and then both add it).
There are few stylistic issues to consider:
-1- If the record already exists, checking before doing the insert will avoid having a thrown exception except in the case where two clients attempt to add the same value simultaneously. Code must be prepared to handle the exception even if it checks before the insert, but avoiding the insert will probably be faster than attempting it and having it fail
-2- If the record does not exist, checking the database for it will take a little time; adding the record without pre-checking would probably be faster in that case.
-3- Some databases allow nulls in a unique index; some do not. Microsoft allows exactly one null to appear in (adding a second item with a null value would violate the unique index constraint). This scenario is applicable to cases where, e.g., any number of workers might have no Social Security Number, but no SSN may be shared among workers. If the database does not allow nulls in the field, one may have to store an arbitrary value instead. If there's a unique positive integer record number available, one could set the SSN field to be negative that (e.g. for record 1,234 store -1234 in the SSN). One would have to recognize and handle those numbers when viewing the data, but there would be no problem handling multiple SSN-less employees.
|
|
|
|
|
hi
thank u for ur reply.
As u said i had other ways of doing the same.but the field i need to check is not a primary key field,also i cant set it as PK since other id field is already set to PK.
thank u very much
kssk
senthil
|
|
|
|
|
Hi all,
I want to port the .dbf files into Sqlserver.
The requirement are as such that
The user should be able to specify the name of Database and login Credential prior to porting the files into the Database.
we are using Visual Studio 2005 as developoemnt platform.
Please suggest us the possible way for that i can achieve my target.
Thanks and Regards
Avesh Agarwal
|
|
|
|
|
create a function that check what was the last value
last month for the employee in the field "location" AND "date_in"
and copy all the employee to a new TABLE
generate new location value and new date
(my location value is (1,2,3,4,5) only from 1 to 5)
the employee change location evry month (in the first day of the month)
from 1 to 2 from 2 to 3 from 3 to 4 from 4 to 5
and after to 1
i have a table of employees
like this
----------------------------------------
ID | location | date_in
1234 | 1 01/01/2007
3456 2 03/01/2007
5675 3 04/01/2007
-----------------------------------------
TNX
|
|
|
|
|
Well, since that is written in the imperative I would guess this is homework. You have to show us something before we'll help you. We won't do your homework for you. If you have difficulty in starting then what part don't you understand.
|
|
|
|
|
hi all ,
I want tp populate a combobox with all SQL Server supported currency symols , where can I find this list and how can I read/write money values from a .net app?
Thanks in advance
|
|
|
|
|
DotNetWWW wrote: I want tp populate a combobox with all SQL Server supported currency symols
SQL Server does not care about currency symbols. It is up to you to ensure that data is in the correct currency, or that if it will be in multiple currencies that you supply the appropriate data with regard to that.
DotNetWWW wrote: where can I find this list
There is no list of currency symbols for SQL Server. You can probably google for a list though.
DotNetWWW wrote: how can I read/write money values from a .net app?
A money column in SQL Server will be a decimal in .NET
|
|
|
|
|
Hi all ,
Is it possible to reset an identity field in order to start from the seed?
Thanks in advance ,
|
|
|
|
|
DotNetWWW wrote: Is it possible to reset an identity field in order to start from the seed?
Use DBCC CHECKIDENT
|
|
|
|
|
Hi all ,
What's the best solution to create a custome identity/autoincrement field in sql server 2005?
thanks in advance ,
|
|
|
|
|
DotNetWWW wrote: What's the best solution to create a custome identity/autoincrement field in sql server 2005?
Do you mean other than setting IDENTITY(start, jump) on the appropriate column?
You could possibly create a function and make use the function as the default value for the column. I don't know if that would work. I've never tried it.
|
|
|
|
|
go to design mode in management studio and do the changed according to your needs .....
|
|
|
|
|
hi all
i have problem when connect DBF database file
when i connect database with oledb in .net 2.0(asp.net)
i see if name file DBF smaller than 8 character is Okay if lager 8 character is exception
The Microsoft Jet database engine could not find the object 'demodemodemo.dbf'. Make sure the object exists and that you spell its name and the path name correctly.
How can i connect DBF database file so not depend with name file ????
|
|
|
|
|
Hello Sir,
I want to port the .dbf files into Sqlserver.i m not able to port the .dbf files into Sqlserver. I m a novice in this.
i hope u have done this..Can u please tell me how can i do this.
The requirement are as such that
The user should be able to specify the name of Database and login Credential prior to porting the files into the Database.
i m using Visual Studio 2005 as development platform.
Thanks and Regards
Avesh Agarwal
|
|
|
|
|
Hi All,
Simple questions: New to SQL
I have a SQL stored procedures which has a bunch of input parameters. Three of the input parameters are @LastName, @FirstName, @PhoneNumber.
I want to take the first letter from @LastName and @FirstName and 4 letters from the phone number and create a primary key.
Here is the code snippet:
DECLARE @CustomerLastInitial CHAR
SET @CustomerLastInitial = SUBSTRING(@LastName, 1, 1)
DECLARE @CustomerFirstInitial CHAR
SET @CustomerFirstInitial = SUBSTRING(@FirstName, 1, 1)
DECLARE @CustomerPhoneString CHAR(4)
SET @CustomerPhoneString = SUBSTRING(@Phone, 4, 4)
SET @CustomerID = @CustomerLastInitial + @CustomerFirstInitial + @CustomerPhoneString
But for some reason @CustomerID only has the first letter from the last name and ignores the rest.
Also tried:
@CustomerID = SUBSTRING(@LastName, 1, 1) + SUBSTRING(@FirstName, 1, 1) +
SUBSTRING(@Phone, 4, 4)
What am I doing wrong?
Frustrated
|
|
|
|
|
What data type / length are you using for @CustomerID? It should be char(6).
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hi Paul,
Thanks for the reply.
I am using VARCHAR(10)
Calvin
|
|
|
|
|
Your code works for me (when I set up @LastName, @FirstName, @Phone and @CustomerId). You have not posted the code you are actually using.
DECLARE @LastName varchar(50);
DECLARE @FirstName varchar(50);
DECLARE @Phone varchar(50);
DECLARE @CustomerID varchar(10);
SELECT @LastName = 'Mackay', @FirstName = 'Colin', @Phone = '12345678';
---- START COPY FROM POST ----
DECLARE @CustomerLastInitial CHAR
SET @CustomerLastInitial = SUBSTRING(@LastName, 1, 1)
DECLARE @CustomerFirstInitial CHAR
SET @CustomerFirstInitial = SUBSTRING(@FirstName, 1, 1)
DECLARE @CustomerPhoneString CHAR(4)
SET @CustomerPhoneString = SUBSTRING(@Phone, 4, 4)
SET @CustomerID = @CustomerLastInitial + @CustomerFirstInitial + @CustomerPhoneString
---- END COPY FROM POST ----
SELECT @CustomerID
Additional: Actually, no it doesn't quite work. It does not take the last 4 chars from the phone number. It takes the penultimate 4 characters.
|
|
|
|
|
Colin Angus Mackay wrote: It does not take the last 4 chars from the phone number. It takes the penultimate 4 characters.
It does what you asked it to: takes four characters starting from position 4. That'll be positions 4, 5, 6 and 7. If you actually want the last four characters, use the RIGHT function, e.g. RIGHT(@Phone, 4).
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Here is the complete stored procedure:
I am debuggin and making sure I am sending it the right parameters from the application.
Code:
*
Description: Handles insertion of new customers.
*/
USE VideoSet
GO
IF OBJECT_ID('up_InsertCustomer') IS NOT NULL
DROP PROC up_InsertCustomer
GO
CREATE PROC up_InsertCustomer
@CustomerID VARCHAR = NULL,
@LastName VARCHAR(25),
@MiddleInitial CHAR(1) = NULL,
@FirstName VARCHAR(25),
@UserName VARCHAR(15) = NULL,
@Password VARCHAR(15) = NULL,
@Salutation VARCHAR(4) = NULL,
@Address VARCHAR(50) = NULL,
@City VARCHAR(25) = NULL,
@Province VARCHAR(25) = NULL,
@PostalCode VARCHAR(10) = NULL,
@Country VARCHAR(25) = NULL,
@Phone VARCHAR(15) = NULL,
@Phone2 VARCHAR(15) = NULL,
@Notes VARCHAR(50) = NULL,
@SalesPersonID VARCHAR(10)
AS
IF NOT EXISTS (
SELECT *
FROM Employee
WHERE EmployeeID = @SalesPersonID
)
RAISERROR('The speicified SalesPersonID does not exist! Invalid SalesPersonID!', 11, 1)
IF @CustomerID IS NULL
BEGIN
DECLARE @CustomerLastInitial CHAR
SET @CustomerLastInitial = SUBSTRING(@LastName, 1, 1)
DECLARE @CustomerFirstInitial CHAR
SET @CustomerFirstInitial = SUBSTRING(@FirstName, 1, 1)
DECLARE @CustomerPhoneString CHAR(4)
SET @CustomerPhoneString = SUBSTRING(@Phone, 4, 4)
SET @CustomerID = @CustomerLastInitial + @CustomerFirstInitial + @CustomerPhoneString
INSERT INTO Customer
VALUES (@CustomerID,
@LastName,
@MiddleInitial,
@FirstName,
@UserName,
@Password,
@Salutation,
@Address,
@City,
@Province,
@PostalCode,
@Country,
@Phone,
@Phone2,
@Notes,
@SalesPersonID
)
END
Also I am trying to jump into the stored procedure from the application but for some reason it does not jump into it. I am using VS 2005 and I checked the SQL Debuggin Option in the project properties. I also have a break point in my stored procedure.
Thanks,
Calvin
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
