• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 144
  • Last Modified:

setting a variable in a stored procedure

How do you set a variable equal to the current date in a stored procedure?

My stored procedure (not working) looks like this:

=============================================================

Create Procedure updMission

@sText text,
@sUpdated varchar(12)

as

select @sUpdated = DATE

            begin transaction
            insert into se_mission
                  (mis_updated, mis_text)
                  values
                  (@sUpdated, @sText)
            commit

=============================================================
      
The error messages make it clear that I'm not declaring
my variables correctly.  Please help :)

Dena
0
denam
Asked:
denam
  • 9
  • 7
1 Solution
 
denamAuthor Commented:
Edited text of question.
0
 
mayhewCommented:
If you're using SQL Server, the date function to return today's date is GETDATE().

Give that a try.


select @sUpdated = getdate()
0
 
denamAuthor Commented:
Mayhew, that doesn't work.  It gives me an error: invalid column name 'GETDATE'
I'm not sure the variable declaration is correct.

Dena
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
mayhewCommented:
Sorry, you're absolutely right.

The way you have your procedure declared, those two variables are parameters to be passed into the proc.

I don't remember the exact syntax, but it should be close to the following:


Create Procedure updMission
as

declare
@sText text,
@sUpdated varchar(12)

begin

select @sUpdated = DATE

begin transaction
insert into se_mission
(mis_updated, mis_text)
values
(@sUpdated, @sText)
commit

end


If that's not it, I'll try it tonight when I get home.

Let me know if you have any luck with it.
0
 
mayhewCommented:
oops.

Also please replace the DATE with GETDATE().
0
 
denamAuthor Commented:
I tried it this way because the parameter called text is passed in.  the parameter called updated is not.

=============================
Create Procedure updMission
     
     @sText text
     
     as

     declare
     @sUpdated varchar(12)

     begin

     select @sUpdated = DATE

     begin transaction
     insert into se_mission
     (mis_updated, mis_text)
     values
     (@sUpdated, @sText)
     commit

     end

====================================

but I'm still getting an error.  it is: invalid column name 'Date'

Dena
0
 
mayhewCommented:
Yes, look at my last comment.

I got in a cut and paste frenzy and forgot the change DATE to GETDATE().

Give that a try.
0
 
denamAuthor Commented:
I tried it this way because the parameter called text is passed in.  the parameter called updated is not.

=============================
Create Procedure updMission
     
     @sText text
     
     as

     declare
     @sUpdated varchar(12)

     begin

     select @sUpdated = DATE

     begin transaction
     insert into se_mission
     (mis_updated, mis_text)
     values
     (@sUpdated, @sText)
     commit

     end

====================================

but I'm still getting an error.  it is: invalid column name 'Date'

Dena
0
 
denamAuthor Commented:
ok.  I replaced 'DATE' with 'GETDATE'  but I'm still getting the
invalid column name error (only now it says 'GETDATE' :)

Dena
0
 
mayhewCommented:
What version of SQL Server are you using?
0
 
denamAuthor Commented:
ok.  I replaced 'DATE' with 'GETDATE'  but I'm still getting the
invalid column name error (only now it says 'GETDATE' :)

Dena
0
 
denamAuthor Commented:
it's mssql server 6.5
0
 
denamAuthor Commented:
it's mssql server 6.5
0
 
mayhewCommented:
Did you put the parentheses after getdate?

It should look exactly like:

select @sUpdated = getdate()
0
 
denamAuthor Commented:
Mayhew,

That worked.  Thanks!  Please submit an answer and I'll accept.

Dena
0
 
mayhewCommented:
I'm glad that worked!  :)

Good luck with your project!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now