?
Solved

setting a variable in a stored procedure

Posted on 1999-07-21
16
Medium Priority
?
141 Views
Last Modified: 2013-12-25
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
Comment
Question by:denam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
16 Comments
 
LVL 1

Author Comment

by:denam
ID: 1864229
Edited text of question.
0
 
LVL 5

Expert Comment

by:mayhew
ID: 1864230
If you're using SQL Server, the date function to return today's date is GETDATE().

Give that a try.


select @sUpdated = getdate()
0
 
LVL 1

Author Comment

by:denam
ID: 1864231
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
Plesk WordPress Toolkit

Plesk's WordPress Toolkit allows server administrators, resellers and customers to manage their WordPress instances, enabling a variety of development workflows for WordPress admins of all skill levels, from beginners to pros.

See why 2/3 of Plesk servers use it.

 
LVL 5

Expert Comment

by:mayhew
ID: 1864232
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
 
LVL 5

Expert Comment

by:mayhew
ID: 1864233
oops.

Also please replace the DATE with GETDATE().
0
 
LVL 1

Author Comment

by:denam
ID: 1864234
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
 
LVL 5

Expert Comment

by:mayhew
ID: 1864235
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
 
LVL 1

Author Comment

by:denam
ID: 1864236
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
 
LVL 1

Author Comment

by:denam
ID: 1864237
ok.  I replaced 'DATE' with 'GETDATE'  but I'm still getting the
invalid column name error (only now it says 'GETDATE' :)

Dena
0
 
LVL 5

Expert Comment

by:mayhew
ID: 1864238
What version of SQL Server are you using?
0
 
LVL 1

Author Comment

by:denam
ID: 1864239
ok.  I replaced 'DATE' with 'GETDATE'  but I'm still getting the
invalid column name error (only now it says 'GETDATE' :)

Dena
0
 
LVL 1

Author Comment

by:denam
ID: 1864240
it's mssql server 6.5
0
 
LVL 1

Author Comment

by:denam
ID: 1864241
it's mssql server 6.5
0
 
LVL 5

Expert Comment

by:mayhew
ID: 1864242
Did you put the parentheses after getdate?

It should look exactly like:

select @sUpdated = getdate()
0
 
LVL 1

Author Comment

by:denam
ID: 1864243
Mayhew,

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

Dena
0
 
LVL 5

Accepted Solution

by:
mayhew earned 200 total points
ID: 1864244
I'm glad that worked!  :)

Good luck with your project!
0

Featured Post

URL rewriting in AWS CloudFront

A quick how-to guide to implement with a Lambda function!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Color can increase conversions, create feelings of warmth or even incite people to get behind a cause. If you want your website to really impact site visitors, then it is vital to consider the impact color has on them.
This article was originally published on Monitis Blog, you can check it here . Today it’s fairly well known that high-performing websites and applications bring in more visitors, higher SEO, and ultimately more sales. By the same token, downtime…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question