Local Variables - benefits 1

[dbomrrsm]

Probably an really simple answer to this and probably one I should know !!

The below two statements return the same results so what is the value of using the local variable in the first ?

USE pubs
DECLARE @find varchar(30)
SET @find = 'Ring%'
SELECT au_lname, au_fname, phone
FROM authors
WHERE au_lname LIKE @find

USE pubs
SELECT au_lname, au_fname, phone
FROM authors
WHERE au_lname LIKE 'Ring%'

Thanks

DBomrrsm

 

[Connatic]

In your examples, thier is no difference, both will run exactly the same.

Where Variables come in very useful in the above context is in Stored Procedures.

If you want @find to be dynamic, then create it as a parameter for a stored procedure, then each time you run the Stored Proc, you can pass in any value you want to @Find

e.g. (run this in query analyser)

CREATE PROCEDURE proc_find
(@Find varchar(200))
AS

SELECT au_lname, au_fname, phone
FROM authors
WHERE au_lname LIKE @find + '%'

GO

To execute (then running this, you can pass any value in you want)

EXEC proc_find 'Ring'