December 19, 2014 at 2:40 am
Nice and easy...
December 19, 2014 at 3:33 am
I tried on Sql 2005,
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 4
Must declare the scalar variable "@str1".
Am I testing it in a wrong way?
December 19, 2014 at 3:35 am
the answer is only correct when using SqlServer 2012 and above. Previous versions prompt an error.
December 19, 2014 at 3:36 am
l.vignando (12/19/2014)
I tried on Sql 2005,Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 4
Must declare the scalar variable "@str1".
Am I testing it in a wrong way?
if you try this on 2005 you will need to split the variable assignment, try this:
@str1 VARCHAR(5)
,@str2 VARCHAR(10) ;
select @str1 = NULL, @str2 = 'SQL Strings';
December 19, 2014 at 3:52 am
I use SQL Server 2008 R2 and it works fine..
December 19, 2014 at 4:23 am
SALIM ALI (12/19/2014)
the answer is only correct when using SqlServer 2012 and above. Previous versions prompt an error.
2008 is quite enough.
December 19, 2014 at 4:42 am
erwin.oosterhoorn (12/19/2014)
l.vignando (12/19/2014)
I tried on Sql 2005,Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 4
Must declare the scalar variable "@str1".
Am I testing it in a wrong way?
if you try this on 2005 you will need to split the variable assignment, try this:
@str1 VARCHAR(5)
,@str2 VARCHAR(10) ;
select @str1 = NULL, @str2 = 'SQL Strings';
In-line assignment was introduced in version SQL 2008
DECLARE
{
{ @local_variable [AS] data_type | [ = value ] }
| { @cursor_variable_name CURSOR }
} [,...n]
Prior to SQL 2008, declaration and assignment happens in two different lines.
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
December 19, 2014 at 5:33 am
While yes, initial assignment was introduced in SQL 2008, I think the point of the question was what it prints out and the data types involved. I thought it was a good question that raised a good point.
December 19, 2014 at 7:38 am
Interesting Question, thanks!
December 19, 2014 at 7:58 am
Nice simple question, thanks.
December 19, 2014 at 8:22 am
Thanks for the question. Good way to start Friday.
December 19, 2014 at 10:10 am
I overthought this one but still got it right. Nice question.
Not all gray hairs are Dinosaurs!
December 19, 2014 at 10:59 am
This is of course a limitation with the ISNULL() function. The datatype and in this case str1 is varchar(5) meaning that the replacing statement will be confined to 5 characters. Whereas COALESCE() is not dependent in this way but will return the first not null value of whatever datatype it may be. COALESCE() is the better choice.
December 19, 2014 at 12:37 pm
tjskidmore (12/19/2014)
This is of course a limitation with the ISNULL() function. The datatype and in this case str1 is varchar(5) meaning that the replacing statement will be confined to 5 characters. Whereas COALESCE() is not dependent in this way but will return the first not null value of whatever datatype it may be. COALESCE() is the better choice.
Not quite so. COALESCE uses the data type with the highest data type precedence which can generate unexpected errors if you're not aware of this.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply