Menu
If I want to set a variable to a field in a table I normally use something like
SELECT @UserIdToUpdate = userId FROM #NewUsers
In this case there will be multiple results and I just want the first one so I tried this but it fails and says invalid syntax top
SELECT @UserIdToUpdate = TOP 1 UserId FROM #NewUsers
If this is this case can I just usethe first example without the top? I assume it will just take the first record? I know it seems like on odd thing to do but the command is in a loop so it will select a record, do something with it, delete it then select the next one.
682
If you want a variable that you can query like a temporary table, you will have to declare a table variable and insert the names into that "table", afterwards you can run a select statement against that variable: Declare @variable table (name nvarchar(128)); INSERT INTO @variable (name) SELECT name FROM sys.
If we want to declare a table variable, we have to start the DECLARE statement which is similar to local variables. The name of the local variable must start with at(@) sign. The TABLE keyword specifies that this variable is a table variable.
Variables in SQL procedures are defined by using the DECLARE statement. Values can be assigned to variables using the SET statement or the SELECT INTO statement or as a default value when the variable is declared. Literals, expressions, the result of a query, and special register values can be assigned to variables.
SELECT @UserIdToUpdate = NULL
SELECT TOP 1 @UserIdToUpdate = userId FROM #NewUsers
The first statement is needed because if the second finds zero rows, then the variable will not get assigned at all and will keep its prior value.
Alternatively,
SELECT @UserIdToUpdate = (SELECT TOP 1 userId FROM #NewUsers)
this will work even if zero rows are found.
182
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
