How To Set Multiple Variables By Using SELECT In Stored Proc
Very common task when writing SQL Server stored procedure is to set value of some variable by using the SELECT query. Very simple example could look like this:
[ T-SQL ]
DECLARE
@CurrentCategory INT
SET @CurrentCategory = (SELECT
CategoryID FROM Articles
WHERE ID = @ID)
Using the same logic, if we want to set value of two, three or more variables, we could try something like:
[ T-SQL ]
DECLARE
@CurrentCategory INT
DECLARE
@DatePublished DATETIME
DECLARE
@Visits INT
SET
@CurrentCategory =
(SELECT
CategoryID FROM Articles
WHERE ID = @ID)
SET
@DatePublished =
(SELECT
DatePublished FROM Articles
WHERE ID = @ID)
SET @Visits
=
(SELECT Visits
FROM Articles WHERE
ID = @ID)
This is not very efficient, especially if you have big select which looks in many tables or views and/or complicated WHERE clause. Fortunately, you can set multiple SQL variables by using one SELECT query. This approach is much better:
[ T-SQL ]
DECLARE
@CurrentCategory INT
DECLARE
@DatePublished DATETIME
DECLARE
@Visits INT
SELECT @CurrentCategory = CurrentCategory, @DatePublished =
DatePublished, @Visits
= Visits
FROM
Articles
WHERE ID
= @ID
On this way, all three variables are set in the same time, which makes procedure execute faster.
Happy coding!
Related articles:
1. Check If Database Exists Using T-SQL
