First, thank you for your message.

I am sure you know what you are talking about. But I have one stored procedure - used a lot - where I don't have the SET @Var = Select ... but instead simply rely on the SQL Select to set the value. As I wrote in my original message the code is:

select @FieldVar1 = FIELD_NAME, @FieldVar2 = FIELD2 from TableName where some condition

I know that based on the "some condition" only one record is selected. And the code above works.
Are you saying that even though the code above works, it is better to use the:

set @FieldVar1 = ( select FIELD_NAME from TableName where somecondition)

I am just confused that I didn't get bad results with the "old" syntax. And I need to understand all ramifications if I change to the syntax as you recommend.

If fact, in my case, even if 2 or more records are selected, I get no error. Maybe just "wrong" values.
Correct?

Thank you.

>Martina gave you the answer, but just to add 2 cents....
>
>Technically, you weren't "filling in" anything with your original code
>
>You had SELECT @TestPk = RECORD_PK FROM MyTable WHERE...
>
>You need to explicitly populate the variable with a SET statement, as Martina showed in her example.
>
>Also.....let's say that instead of the query producing zero rows, it had produced TWO rows.
>Your original code would (I believe) give you the value of the 2nd row....which could be very misleading.
>
>When you explicitly try to use a SET @Var = (select something)......and that SELECT returns more than 1 row, you'll get an error (which is what you "should" want)
>
>Bottom line...when explicitly populating a scalar from a query, it's best to use SET @Var = (SELECT etc.).....
>
>
>(Now...someone might say, suppose you need to populate six scalar variables from one query...well, that's a slightly different conversation)
>
>
>
>
>
>
>
>
>>Hi Martina,
>>
>>Thank you for your prompt reply. I didn't know that SQL does not fill the value of the variables if the SQL Select does not find a record.
>>
>>>Hi,
>>>
>>>Pleas, put all relevant code.
>>>
>>>

>>>DECLARE @TestPk INT
>>>declare @testreq char(20)
>>>set @testreq = 'XYZABC123' --  value is 'XYZABC123           ' because datatype is char(20)
>>>
>>>SET @TestPk=-1
>>>-- T-SQL engine don't fill @TestPk variable
>>>SELECT  @TestPk = RECORD_PK FROM MyTable WHERE REQUESTOR = @testreq -- no record for this condition
>>>SELECT @@ROWCOUNT -- show: 0
>>>SELECT @TestPk --  show: -1
>>>
>>>-- T-SQL engine do fill @TestPk variable
>>>SET @TestPk=(SELECT  RECORD_PK FROM MyTable WHERE REQUESTOR = @testreq) -- no record for this condition
>>>SELECT @@ROWCOUNT -- show: 0
>>>SELECT @TestPk --  show: NULL
>>>
>>>

>>>
>>>MartinaJ
>>>
>>>>Hi,
>>>>
>>>>I realize that this is a very newbie question. But I would like to know why or at least to know that I understand this correctly.
>>>>
>>>>The following SQL Select should find a record.
>>>>
>>>>

>>>>declare @testreq char(20)
>>>>set @testreq = 'XYZABC123'
>>>>SELECT  @TestPk = RECORD_PK FROM MyTable WHERE REQUESTOR = @testreq
>>>>

>>>>
>>>>The value assigned to the variable @TestPk is 0 (zero) (since the record not found in the table).
>>>>
>>>>Why not NULL? Is this because at the top of the stored procedure the @TestPk is declared as an INT and it cannot be NULL?
>>>>
>>>>TIA