CODEWITHSUNDEEP
sqlsql-servert-sql
Bill Greer
Bill Greer

Reputation: 3156

Understanding SQL syntax

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'

Upvotes: 2

Views: 121

Answers (2)

devlead
devlead

Reputation: 5010

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')

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269863

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.

Upvotes: 5

Related Questions