Understanding SQL syntax

Question

I'm a C# developer trying to become more familiar with SQL Server stored procedures.

I'm a little confused as to why the syntax in "A" works and "B" does not work with Set @id. What is happening here that makes "B" require Select instead of Set?

Example A (works)

DECLARE @currDateTime DateTime

SET @currDateTime = GetDate()

SELECT @currDateTime

Example B (does not work)

 DECLARE @id int

 SET @id = ID FROM [MyTable] WHERE [Field1] = 'Test'

Example C (works)

 DECLARE @id int

 SELECT @id = ID 
 FROM [MyTable] 
 WHERE [Field1] = 'Test'

FROM is belong to SELECT not to SET. For more information look at this link — Siyavash Hamdi
– Siyavash Hamdi, Commented May 31, 2016 at 11:06
When You retrieve value from table use 'Select' . In case of simple assignment statement You can use 'Set' — Karthikeyan
– Karthikeyan, Commented May 31, 2016 at 11:12

Gordon Linoff · Accepted Answer · 2016-05-31 11:08:35Z

5

SELECT is a built-in type of SQL clause that runs a query and returns a result-set in the format of a table or it assigns variables to the results from a query.

SET is a clause that sets a variable.

The two are very different. SELECT has various other associated clauses, such as FROM, WHERE and so on; SET does not. SELECT returns values as a result table in its normal usage; SET does not.

Admittedly, both look the same in an expression such as:

set @currDateTime = GetDate();
select @currDateTime = GetDate();

However, it is really a coincidence that the syntax for setting a single value happens to look the same.

answered May 31, 2016 at 11:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

devlead · Accepted Answer · 2016-05-31 11:20:26Z

2

It doesn't work because it's incorrect SQL syntax, You need SELECT when fetching data from table/view/table function.

You could use SET when using an expression though i.e:

DECLARE @Id bigint


SET @Id = (SELECT TOP 1 Id
                FROM MyTable
                WHERE Field1 = 'Test')

answered May 31, 2016 at 11:20

devlead

5,02517 silver badges38 bronze badges

Collectives™ on Stack Overflow

Understanding SQL syntax

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related