MS SQL

MS SQL RSS

Accessing a field in SQL

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Jul 2009

Posts: 27

Reputation: Pankaj18 is an unknown quantity at this point

Solved Threads: 0

Pankaj18

Offline

Light Poster

Accessing a field in SQL

-1

Aug 3rd, 2009

Hi,

We have schedule a job through SQL agent

in which

We want to access the data of a specific field from SQL table

For example we have a field named interviewDate, we have to compare it with current date.

now we have to write a stored procedure to read the date field.

This is incorrect syntax which we tried(doesn't work)

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
CREATE PROCEDURE chkinterview
as
SELECT * FROM interview
if(interviewdate!='2/7/1900 12:00:00 AM')
print'ok'
ELSE
print'not ok'
create procedure chkinterview
as
select * from interview
if(interviewdate!='2/7/1900 12:00:00 AM')
print'ok'
else
print'not ok'

what will be the syntax to access all the record of interviewdate field.

Later we have to insert that data into another table through a stored procedure.

Thanks !

Pankaj Singh

Last edited by peter_budo; Aug 3rd, 2009 at 11:55 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

•

Join Date: Feb 2009

Posts: 3,254

Reputation: sknake has much to be proud of

Solved Threads: 579

sknake

Offline

.NET Enthusiast

Re: Accessing a field in SQL

Aug 3rd, 2009

First: use code tags when posting your code:

[code=sql]
...query here
[/code]

Secondly: This is an MSSQL question, not an ASP.NET question. The problem here is your query syntax.

Third: The "not equal" operation in MSSQL is <> and not !=. You also need to select one record from your interview table to compare against .. unless you're trying to compare all dates? Please explain what you are doing. Next with DateTimes and interviews you will likely want to have a Start/End date unless all interviews are of a fixed duration. Please clarify.

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
CREATE PROCEDURE chkinterview
As
BEGIN
 
DECLARE @someValue INT
IF ((SELECT interviewdate FROM interview WHERE someColumn = @someValue) <> Cast('2/7/1900 12:00:00 AM' as DATETIME))
PRINT 'OK'
ELSE
PRINT 'NOT OK'
 
END
Create Procedure chkinterview
As
BEGIN

Declare @someValue int
IF ((select interviewdate from interview Where someColumn = @someValue) <> Cast('2/7/1900 12:00:00 AM' as datetime))
PRINT 'OK'
ELSE
PRINT 'NOT OK'

END

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Jul 2009

Posts: 27

Reputation: Pankaj18 is an unknown quantity at this point

Solved Threads: 0

Pankaj18

Offline

Light Poster

Re: Accessing a field in SQL

Aug 3rd, 2009

This works for a single row but what if there are multiple rows(6000). Do we have a loop for that.
Then
We have to copy the whole row into another table.
Can you please explain on this as well.
Thanks
Pankaj Singh

•

Join Date: Feb 2009

Posts: 3,254

Reputation: sknake has much to be proud of

Solved Threads: 579

sknake

Offline

.NET Enthusiast

Re: Accessing a field in SQL

Aug 3rd, 2009

>> This works for a single row but what if there are multiple rows(6000).
I'm aware and stated that limitation in my original post.

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
CREATE PROCEDURE chkinterview
As
BEGIN
 
DECLARE @someValue INT
IF EXISTS(SELECT * FROM Interview WHERE InterviewDate = Cast('2/7/1900 12:00:00 AM' as DATETIME)) 
  PRINT 'NOT OK'
ELSE
  PRINT 'OK'
 
END
Create Procedure chkinterview
As
BEGIN

Declare @someValue int
IF EXISTS(Select * From Interview Where InterviewDate = Cast('2/7/1900 12:00:00 AM' as datetime)) 
  PRINT 'NOT OK'
ELSE
  PRINT 'OK'

END

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Jul 2009

Posts: 27

Reputation: Pankaj18 is an unknown quantity at this point

Solved Threads: 0

Pankaj18

Offline

Light Poster

Re: Accessing a field in SQL

Aug 4th, 2009

Hi,

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
      CREATE Procedure chkinterview
      AS
      BEGIN
      Declare @someValue int
      IF EXISTS(SELECT * FROM Interview WHERE InterviewDate = Cast('2/7/1900 12:00:00 AM' AS datetime))
      PRINT 'NOT OK'
      ELSE
      PRINT 'OK'
      END
      CREATE Procedure chkinterview
      AS
      BEGIN
      Declare @someValue int
      IF EXISTS(SELECT * FROM Interview WHERE InterviewDate = Cast('2/7/1900 12:00:00 AM' AS datetime))
      PRINT 'NOT OK'
      ELSE
      PRINT 'OK'
      END

In above code:
If the Condition is true ,so we have to copy the selected column(expr1,expr2,expr3..) into another table.
Can you please explain on this as well.
Thanks
Pankaj Singh

Last edited by Ezzaral; Aug 4th, 2009 at 3:37 pm. Reason: Added [code] [/code] tags. Please use them to format any code that you post.

•

Join Date: Feb 2009

Posts: 3,254

Reputation: sknake has much to be proud of

Solved Threads: 579

sknake

Offline

.NET Enthusiast

Re: Accessing a field in SQL

Aug 4th, 2009

Please use code tags when posting on DaniWeb!

For your question -- Not really. I don't think I understand what you are asking.

"In the above code: If the condition is true" -- means that if the appointment already exists you want to copy it to another table?

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
IF EXISTS(SELECT * FROM Interview WHERE InterviewDate = Cast('2/7/1900 12:00:00 AM' AS DATETIME))
BEGIN
  INSERT INTO otherTable (col1,col2,col3)
  SELECT col1,col2,col3
  FROM INTERVAL
  WHERE InterviewDate = Cast('2/7/1900 12:00:00 AM' AS DATETIME)
END
IF EXISTS(SELECT * FROM Interview WHERE InterviewDate = Cast('2/7/1900 12:00:00 AM' AS datetime))
BEGIN
  Insert Into otherTable (col1,col2,col3)
  Select col1,col2,col3
  From Interval
  Where InterviewDate = Cast('2/7/1900 12:00:00 AM' AS datetime)
END