retrive data from stored procedure

Apurva Singh

8y900

I have two table , now i want to two or more column from second table in store procedure.

when i add column name in stored procedure then i am getting error.

i am providing my stored procedure, please help

CREATE PROCEDURE sp_Productlist --'1' ,'10','1'

@PageIndex INT = 1

,@PageSize INT = 10

,@PageCount INT OUTPUT

BEGIN

SET NOCOUNT ON;

SELECT ROW_NUMBER() OVER

(

ORDER BY Pro_id ASC

)AS RowNumber,

Product_Master.Pro_id,

Product_Master.Pro_Name,

Product_Master.Pro_MRP,

Product_Master.Pro_Description,

Product_Master.Pro_Discount,

(select top(1) product_pic.Pro_Pic_Url

from product_pic Here i want to get more column from table product_pic

where product_pic.Pro_id=Product_Master.Pro_id)

as Image

INTO #Results

FROM Product_Master

DECLARE @RecordCount INT

SELECT @RecordCount = COUNT(*) FROM #Results

SET @PageCount = CEILING(CAST(@RecordCount AS DECIMAL(10, 2)) / CAST(@PageSize AS DECIMAL(10, 2)))

PRINT @PageCount

SELECT * FROM #Results

WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1

DROP TABLE #Results

END

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Packiaraj Santhiyagu8y ago
Accepted answer
Hi Apurva,

I work out and bring the result. It is working.

I create this SP in MSSQL server. So, run in MSSQL server

Read the command carefully to clear understand
CREATE PROCEDURE sp_Productlist --'1' ,'10','1'
@PageIndex INT = 1
,@PageSize INT = 10
,@PageCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP(1) product_pic.Pro_Pic_Url, product_pic.Pro_id into #temp
FROM product_pic inner join Product_Master
ON product_pic.Pro_id=Product_Master.Pro_id -- THIS IS TEMP TABLE TO TAKE ONLY ONE ROW, LATER I WILL USING FOR JOIN
-- ALSO YOU CAN ANY NO OF COLUMN HERE
SELECT
ROW_NUMBER() OVER
(
ORDER BY Product_Master.Pro_id ASC
)AS RowNumber,
Product_Master.Pro_id,
Product_Master.Pro_Name,
Product_Master.Pro_MRP,
Product_Master.Pro_Description,
Product_Master.Pro_Discount,
#temp.Pro_Pic_Url as image,
#temp.Pro_id
INTO #Results
FROM Product_Master inner join #temp on #temp.Pro_id=Product_Master.Pro_id
DECLARE @RecordCount INT
SELECT @RecordCount = COUNT(*) FROM #Results
SET @PageCount = CEILING(CAST(@RecordCount AS DECIMAL(10, 2)) / CAST(@PageSize AS DECIMAL(10, 2)))
PRINT @PageCount
SELECT * FROM #Results
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
DROP TABLE #Results
DROP TABLE #temp
END
Note:

You can add any number of column in line number 09.

If you need burther clarification comment me detailed
Jignesh Kumar8y ago
Hi Apurva,

You can use inner join if you want data from two table and put where clause according to your filter criteria.
Kapil Singh Kumawat8y ago
This is not a correct way,

You can use CTE for it.

Like this -
With CTE AS
select top(1) product_pic.Pro_Pic_Url
from product_pic
...........................
,
CTE2 AS

SELECT ROW_NUMBER() OVER
(
ORDER BY Pro_id ASC
)AS RowNumber,
Product_Master.Pro_id,
Product_Master.Pro_Name,
Product_Master.Pro_MRP,
Product_Master.Pro_Description,
Product_Master.Pro_Discount,
FROM Product_Master
....

And then merge it using Union or Union All