MSSQL database: Stored Procedure Learning

Stored Procedure | data | database
In this example, we take the first two rows from the two tables and merge them into a table.

In reality we often encounter situations where two tables exist in one database, assuming table 1 stores the quarterly sales information for the company's products, and table 2 stores the amount of money owed by the company this quarter. In a page we want to show these two messages. The usual approach is to do two SQL queries in the program, returning two result sets, which are very cumbersome to display separately.

Here is the code to implement this feature:

CREATE PROCEDURE Test
As
SET NOCOUNT on--Indicates that the stored procedure does not return the number of rows affected by the query
DECLARE @col1c varchar, @col2c varchar, @index int
SET @index = 1
CREATE TABLE #tmptbl--Creates a temporary table to store our results
(
ColID int IDENTITY (1,1) PRIMARY KEY CLUSTERED,
col1 varchar (20),
col2 varchar (20)
)

DECLARE cur1 CURSOR for SELECT top 2 customerid from Orders
DECLARE cur2 CURSOR for SELECT top 2 regiondescription from region
OPEN Cur1
OPEN CUR2
FETCH CUR2 into @col2c
FETCH Cur1 into @col1c
While @ @FETCH_STATUS = 0
BEGIN
INSERT into #tmptbl (col1, col2) VALUES (@col1c, @col2c)
FETCH NEXT from Cur1 into @col1c
FETCH NEXT from CUR2 into @col2c
End
Close Cur1
Close CUR2
Deallocate Cur1
Deallocate CUR2
SELECT * from #tmptbl
DROP TABLE #tmptbl
Go

Description

@ @FETCH_STATUS, returns the last cursor state executed by the FETCH statement.

Return value: 0-fetch statement executed successfully
The 1-fetch statement failed, or the row is no longer in the result set.
2-The fetched row does not exist.