Is there any way to conditionally assign value to a variable in sql server, Like
SET SDate = Isnull((Select StartDate from SData), Select EDate from Table2)
Your version is pretty close. You can write this as:
SET SDate = Isnull((Select StartDate from SData), (Select EDate from Table2) );
A subquery always needs to be surrounded by its own parentheses.
That said, your query presupposes that both tables have at most one row. This does not seem like a good assumption. If either has more than one row, you'll get a "subquery returns more than one row" error.