Hi friends, I want to pass getdate as a parameter when executing my stored procedure but i am getting an error like must declare scalar variable  create proc get_date @empno int, @ename varchar(20), @hiredate1 datetime as --set @hiredate1=GETDATE() Set @hiredate1 = Coalesce(@hiredate1 , GetDate()) insert into emp_new(empno,ename,hiredate)values(@empno,@ename,@hiredate1) exec get_date 23,rama,@hiredate1

That only makes little sense to me. You are calling the procedure from the same itself ? What do you want to achieve ?

-Jens

I want to pass getdate date to hiredate column 

 

It looks like your rama is out of quotes...

the correct way is to put the rama within the single quotes like the below 

exec get_date 23,'rama',@hiredate1

Also below is the way the code has to be written , if the intention of the exec statement is to test the procedure.

create proc get_date
@empno int,
@ename varchar(20),
@hiredate1 datetime
as
--set @hiredate1=GETDATE()
Set @hiredate1 = Coalesce(@hiredate1 , GetDate())
insert into emp_new(empno,ename,hiredate)values(@empno,@ename,@hiredate1)

Go

Declare @hiredate1 datetime

exec get_date 23,'rama',@hiredate1

 

Alternatively, one can specify a default value of NULL for @hiredate1, making it an optional parameter.  That way, @hiredate1 need only be specified when a value other than GETDATE() is desired: 

 

CREATE PROC dbo.insert_emp_new
	@empno int,
	@ename varchar(20),
	@hiredate1 datetime = NULL
AS
SET NOCOUNT ON;
INSERT INTO dbo.emp_new(empno,ename,hiredate)
	VALUES(@empno,@ename,COALESCE(@hiredate1 , GETDATE()));
RETURN @@ERROR;
GO

EXEC dbo.insert_emp_new 23, 'Rama';
EXEC dbo.insert_emp_new 23, 'Dan', NULL;
EXEC dbo.insert_emp_new 23, 'Sorna', '20110820';

 

why didn't you specify GETDATE as default data type in dbo.emp_new table? it would automatically create current data time of new employer insertion?

Hi friends,

 Try this for pass datatime parameter to null and declare the variable name while executing the stored procedure

alter proc get_date
@empno int,
@ename varchar(20),
@hiredate1 datetime=NULL
as
--set @hiredate1=GETDATE()
Set @hiredate1 = Coalesce(@hiredate1 , GetDate())
insert into emp_new(empno,ename,hiredate)values(@empno,@ename,@hiredate1)

declare @hiredate1 datetime
exec get_date 23,rama,@hiredate1