Call Stored Procedure in Select query in SQL Server

Last Reply one month ago By dharmendr

Posted one month ago

hi,

my first sp create and run perfectly.  please take a look.  

 sp1.

SELECT 
PROGRAMS.AutoNum,
PROGRAMS.OrgCode,
PROGRAMS.OPTitle,
PROGRAMS.OPPubDescrip, 
CASE WHEN LEN(PROGRAMS.OPPubDescrip) > 700 THEN 2 ELSE 1
END AS Number1
INTO TEMP
FROM ORGANIZATIONS 
	INNER JOIN PROGRAMS 
		ON ORGANIZATIONS.OrgCode = PROGRAMS.OrgCode
WHERE ORGANIZATIONS.Status = 'Approved'
AND	  PROGRAMS.OPStatus = 'Active'	
ORDER BY ORGANIZATIONS.OrgCode

i want to insert the "number1" value into the second stored procedure when i call.  please help me to correct the code.

sp 2.

SELECT
PROGRAMS.AutoNum,
PROGRAMS.OrgCode,
exec QAllAgencyProgramsTotal.number1
COUNT(number1) AS number,
PROGRAMS.OPMissionS
FROM

ORGANIZATIONS 
INNER JOIN PROGRAMS 
ON ORGANIZATIONS.OrgCode = PROGRAMS.OrgCode

WHERE ORGANIZATIONS.Status = 'Approved'
AND PROGRAMS.OPStatus = 'Active' 
ORDER BY ORGANIZATIONS.OrgCode

thanks

Posted one month ago Modified on one month ago

Hi phonghue,

You can not call a stored procedure from within a select query.

First created a function that will return the count and then call it inside the select statement.

Else check with the below query.

SELECT AutoNum,OrgCode,OPTitle,OPPubDescrip,number1,COUNT(number1) AS number,OPMissionS 
FROM (
	SELECT	PROGRAMS.AutoNum,
			PROGRAMS.OrgCode,
			PROGRAMS.OPTitle,
			PROGRAMS.OPPubDescrip,
			CASE WHEN LEN(PROGRAMS.OPPubDescrip) > 700 THEN 2 ELSE 1
			END AS number1,
			PROGRAMS.OPMissionS
	FROM ORGANIZATIONS
	INNER JOIN PROGRAMS ON ORGANIZATIONS.OrgCode = PROGRAMS.OrgCode
	WHERE ORGANIZATIONS.Status = 'Approved'
		  AND PROGRAMS.OPStatus = 'Active') t
GROUP BY AutoNum,OrgCode,OPTitle,OPPubDescrip,number1,OPMissionS
ORDER BY OrgCode

Chack the sample query.

SQL

SELECT Name,number1,COUNT(number1) Number FROM (
SELECT EmployeeID,FirstName + ' ' + LastName Name,
CASE WHEN LEN(CONVERT(VARCHAR(MAX),Notes)) > 200 THEN 2 ELSE 1
            END AS number1
FROM Employees)t
GROUP BY EmployeeID,Name,number1
ORDER BY EmployeeID

Output

Name number1 Number
Nancy Davolio 1 1
Andrew Fuller 2 1
Janet Leverling 2 1
Margaret Peacock 2 1
Steven Buchanan 2 1
Michael Suyama 2 1
Robert King 2 1
Laura Callahan 1 1
Anne Dodsworth 1 1