Searching a table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Searching a table

Author

Topic

macca
Posting Yak Master

146 Posts

Posted - 2005-06-07 : 09:30:43

I have a table containing a number of columns, one column contains the record numbers for the records being inserted. The record numbers have the format: 2005/a/b/001, 2005/a/b/002 ..... and so on.
There is a possibility that some records may be added in duplicate but throwing an error. What I want to do is search the numbers column before the new record is inserted to see if that record already exists and if it does then I want ot generate new number.
I know how to generate new number but I need help to search the column.

Anyone know how to search the column to see if the record already exists?

Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-07 : 09:36:15

Select col from yourTable where col='yourvalue'
if col has value its already existing else generate new number

Madhivanan

Failing to plan is Planning to fail

macca
Posting Yak Master

146 Posts

Posted - 2005-06-07 : 09:46:35

Thanks man,

Tried that and was fine.

Another question:
I am using VB.net code for this project, how can I return a true value to the code or a false value to the code depending on whether the value is present in the column or not after I have ran the sql code you gave?

raclede
Posting Yak Master

180 Posts

Posted - 2005-06-07 : 23:19:31

use the output parameter of the stored procedure ... then in your VB Code instead of parameterinput change it to output direction.. see BOL for VB.NET Code on Using Stored Procedures.

in your SP

CREATE PROCEDURE Sample
(
@inputvar datatype,
@RetCode INT OUTPUT
)

sample code that uses OUTPUT in C#, we use application blocks for this one:


SqlParameter[] parameters = {new SqlParameter("@imID", System.Convert.ToInt32(imID.Trim())), 
			 new SqlParameter("@cpID", cpID.Trim()),  
                            new SqlParameter("@subCode", subCode.Trim()),  
                            new SqlParameter("@itemCode", itemCode.Trim()),
                            SqlAccessor.SqlParameterBuilder("@RetID", SqlDbType.VarChar,20, ParameterDirection.Output),
                            SqlAccessor.SqlParameterBuilder("@RetcpID", SqlDbType.Char,3, ParameterDirection.Output),
                            SqlAccessor.SqlParameterBuilder("@RetSubCode", SqlDbType.Char,3, ParameterDirection.Output),

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede

macca
Posting Yak Master

146 Posts

Posted - 2005-06-08 : 04:14:14

Thanks Raclede.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources