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