When you need to retrieve a single row from a table or query, you can use the following syntax in SQL Server: Show DECLARE @name VARCHAR(30); SELECT @name = city FROM cities; But what happens if SELECT returns multiple rows? Assume we have the following table definition and data: CREATE TABLE cities (city VARCHAR(30)); INSERT INTO cities VALUES ('St. Louis'); INSERT INTO cities VALUES ('San Diego'); INSERT INTO cities VALUES ('Seattle'); Let's run SELECT and output data: DECLARE @name VARCHAR(30); SELECT @name = city FROM cities; PRINT @name; -- Prints Seattle SQL Server does not raise an error, and returns the last value. It defines how many rows meet the search criteria, and you can obtain this number using @@ROWCOUNT: DECLARE @name VARCHAR(30); SELECT @name = city FROM cities; PRINT @@ROWCOUNT; -- Prints 3 Note that when you want to retrieve one row, but multiple rows may meet the search condition, you have to check @@ROWCOUNT > 0, not @@ROWCOUNT = 1 to define if a row exists: DECLARE @name VARCHAR(30); SELECT @name = city FROM cities; IF @@ROWCOUNT = 1 PRINT @name; -- Nothing is printed as @@ROWCOUNT is equal to 3 SELECT @name = city FROM cities; IF @@ROWCOUNT > 0 PRINT @name; -- Prints Seattle Since SQL Server tries to find all rows (to calculate @@ROWCOUNT) while you just want to find the first row, consider using TOP 1 in the query: DECLARE @name VARCHAR(30); SELECT TOP 1 @name = city FROM cities; PRINT @name; -- Prints St. Louis When you use TOP 1 you can safely check @@ROWCOUNT = 1 to define if a row was found. Migration ResourcesSummary: in this tutorial, you will learn how to develop stored procedures that return multiple values. MySQL stored function returns only one value. To develop stored programs that return multiple values, you need to use stored procedures with If you are not familiar with
Let’s take a look at the The following stored procedure accepts customer number and returns the total number of orders that were shipped, canceled, resolved, and disputed.
In addition to the To use the After executing the stored procedure, you use the
Calling stored procedures that return multiple values from PHPThe following code snippet shows you how to call the stored procedure that returns multiple values from PHP.
The user-defined variables, which are preceded by the In this tutorial, we have shown you how to develop a stored procedure that returns multiple values and how to call it from PHP.
Was this tutorial helpful? How do I assign multiple values to a variable in MySQL?SET @a := "20100630"; SELECT * FROM wordbase WHERE verified = @a; But it does not work when there are multiple values stored in a variable. SET @a := "'20100630', '20100701' "; SELECT * FROM wordbase WHERE verified in (@a);
How can I get multiple values in one column in MySQL?In this case, we use GROUP_CONCAT function to concatenate multiple rows into one column. GROUP_CONCAT concatenates all non-null values in a group and returns them as a single string. If you want to avoid duplicates, you can also add DISTINCT in your query.
How can I add multiple values in one cell in MySQL?INSERT INTO LOGI (related_id) VALUES('281,283,284,285,286'); However this breaks the whole foreign key paradigm. You won't be able to run SELECT queries and join tables based on this column. Better to create a cross-reference table.
How can I store multiple values in one row in MySQL?MySQL INSERT multiple rows statement
In this syntax: First, specify the name of table that you want to insert after the INSERT INTO keywords. Second, specify a comma-separated column list inside parentheses after the table name. Third, specify a comma-separated list of row data in the VALUES clause.
|