denam
asked on
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
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
If you're using SQL Server, the date function to return today's date is GETDATE().
Give that a try.
select @sUpdated = getdate()
Give that a try.
select @sUpdated = getdate()
ASKER
Mayhew, that doesn't work. It gives me an error: invalid column name 'GETDATE'
I'm not sure the variable declaration is correct.
Dena
I'm not sure the variable declaration is correct.
Dena
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.
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.
oops.
Also please replace the DATE with GETDATE().
Also please replace the DATE with GETDATE().
ASKER
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
==========================
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
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.
I got in a cut and paste frenzy and forgot the change DATE to GETDATE().
Give that a try.
ASKER
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
==========================
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
ASKER
ok. I replaced 'DATE' with 'GETDATE' but I'm still getting the
invalid column name error (only now it says 'GETDATE' :)
Dena
invalid column name error (only now it says 'GETDATE' :)
Dena
What version of SQL Server are you using?
ASKER
ok. I replaced 'DATE' with 'GETDATE' but I'm still getting the
invalid column name error (only now it says 'GETDATE' :)
Dena
invalid column name error (only now it says 'GETDATE' :)
Dena
ASKER
it's mssql server 6.5
ASKER
it's mssql server 6.5
Did you put the parentheses after getdate?
It should look exactly like:
select @sUpdated = getdate()
It should look exactly like:
select @sUpdated = getdate()
ASKER
Mayhew,
That worked. Thanks! Please submit an answer and I'll accept.
Dena
That worked. Thanks! Please submit an answer and I'll accept.
Dena
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER