>>>>Hi,
>>>>
>>>>The only way I found that I can assign a value selected in SQL Select to a variable as following:
>>>>
>>>>
>>>>select @MyVar = (select MyColumn1 from MyTable where MyColumn2 = 'SomeValue')
>>>>
>>>>
>>>>Is the above the only way to do it? Or there is a better approach? TIA.
>>>
>>>Another way is
>>>
>>>select @MyVar = MyColumn1 from myTable
>>>
>>>The difference between these two methods is that the first will always change the @MyVar variable. In the second case, if the select statement does not produce rows, the original value of @MyVar will not change.
>>
>>
>>Can you think of why the following SLQ Select (selecting from an XML string):
>>
>>
>>SELECT @Var1 = [Fld FldName].value('.','varchar(20)'), [Fld FldName].value('@Name','varchar(20)') AS FldName
>> FROM @myxml.nodes('/TABLENAME/Fld') Fld([Fld FldName]) where [Fld FldName].value('@Name','varchar(20)')='Field1'
>>
>>
>>gives error:
>>
>>A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
>>
>
>You can not mix assigning the variables and selecting a column in the same statement. It should be
>
>SELECT @Var1 = [Fld FldName].value('.','varchar(20)'), @Var2 = [Fld FldName].value('@Name','varchar(20)')
> FROM @myxml.nodes('/TABLENAME/Fld') Fld([Fld FldName]) where [Fld FldName].value('@Name','varchar(20)')='Field1'
I understand now. Thank you very much!
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham