Must declare the scalar variable (Help)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Must declare the scalar variable (Help)

Author

Topic

ALSZ37
Starting Member

25 Posts

Posted - 2014-10-06 : 14:42:38

Hi,

I am trying to use a scalar-function and am new to these functions. I received some help from a friend, but not sure what else i'm doing wrong after declaring the values. The database has a getage scalar-function so I was going to try to use that to pull the patients age based on there birthdate.

This is the error i'm receiving.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.
Msg 137, Level 15, State 1, Line 4
Must declare the scalar variable "@BIRTHDATE".
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@BirthDate".

Script
DECLARE @BIRTHDATE,
@TODAY

SET @BIRTHDATE = CONVERT(varchar, p.birth_date, 101),
@TODAY = CONVERT(VARCHAR,GETDATE(),101)

select
[dbo].[GetAge] (@BirthDate,@Today) as Patient_AGE
from patient p

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-06 : 14:58:08

You didn't provide a data type for your variables.
declare @var1 datetime, @var2 int, @var3 varchar(20)

You'll also need to switch the SET to SELECT. With SET, you can only set one variable. With SELECT, you can set multiple variables.

I don't see why you are converting to varchar style 101. Just pass p.birth_date and getdate() to the function. Do not convert to varchar.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ALSZ37
Starting Member

25 Posts

Posted - 2014-10-07 : 00:19:48

quote:
Originally posted by tkizer

You didn't provide a data type for your variables.
declare @var1 datetime, @var2 int, @var3 varchar(20)

You'll also need to switch the SET to SELECT. With SET, you can only set one variable. With SELECT, you can set multiple variables.

I don't see why you are converting to varchar style 101. Just pass p.birth_date and getdate() to the function. Do not convert to varchar.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

I'm still learning and my first time using functions.I used the 101 style to make the format look correct for my SSRS report so I just copied it over. I didn't know it was ok to pass that birthdate over.

I don't completely understand what this means. Are you saying declare @birtdate (datetime)?

declare @var1 datetime, @var2 int, @var3 varchar(20)

DECLARE @BIRTHDATE,
@TODAY

Does this look correct for the SQL statement?
select
@BIRTHDATE = p.birth_date,
@TODAY = GETDATE(),
[dbo].[GetAge] (@BirthDate,@Today) as Patient_AGE
from patient p

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-10-07 : 00:44:48

Try this:

select 
[dbo].[GetAge] (p.birth_date, getdate()) as Patient_AGE
from patient p

Harsh Athalye
http://in.linkedin.com/in/harshathalye/

ALSZ37
Starting Member

25 Posts

Posted - 2014-10-07 : 17:15:55

quote:
Originally posted by harsh_athalye

Try this:

select
[dbo].[GetAge] (p.birth_date, getdate()) as Patient_AGE
from patient p

Harsh Athalye
http://in.linkedin.com/in/harshathalye/

Thank you Harsh! I was able to use this, just had to add the column p.birth_date prior to the function and add a where p.birth_date is not null.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources