t-sql call stored procedure with 3 parameters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

t-sql call stored procedure with 3 parameters

Author

Topic

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2014-04-16 : 17:40:59

In t-sql 2008 r2 I need execute a stored procedure called StudentData and pass 3 parameter values to the stored procedure. The stored procedure will then return 5 values that are needed for the main sql. My problem is I do not know how to have the t-sql call the stored procedure with the 3 parameter values and pass back the 5 different unique data values that I am looking for.

The basic dataset is the following:
SELECT SchoolNumber,
SchoolName,
StudentNumber,
from [Trans].[dbo].[Student]
order by SchoolNumber,
SchoolName,
StudentNumber

I basically want to pass the 3 parameters of SchoolNumber, SchoolName, and StudentNumber to the
stored procedure called StudentData from the data I obtain from the [Trans].[dbo].[Student]. The 3 parameter values will be obtained from the sql listed above.
The columns that I need from the stored procedure called StudentData will return the following data columns
that I need for the report: StudnentName, StudentAddress, Studentbirthdate, StudentPhoneNumber, GuardianName.

Thus can you show me how to setup the sql to meet this requirement I have?

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-17 : 01:19:34

--Question you want to pass ONE Row or Multiple Rows?

--For One Row:


DECLARE @SchoolNumber AS VARCHAR(50)
		,@SchoolName AS VARCHAR(50)
		,@StudentNumber AS VARCHAR(50)

		,@StudnentName AS VARCHAR(50)
		,@StudentAddress AS VARCHAR(50)
		,@Studentbirthdate AS DATE
		,@StudentPhoneNumber AS VARCHAR(50)
		,@GuardianName AS VARCHAR(50)

SELECT TOP 1
		@SchoolNumber=SchoolNumber
		,@SchoolName=SchoolName
		,@StudentNumber=StudentNumber
		 
FROM [Trans].[dbo].[Student] 
ORDER BY SchoolNumber
	,SchoolName
	,StudentNumber

EXEC StudentData @SchoolNumber
				,@SchoolName
				,@StudentNumber
				,@StudnentName  OUTPUT
				,@StudentAddress OUTPUT
				,@Studentbirthdate OUTPUT
				,@StudentPhoneNumber OUTPUT
				,@GuardianName OUTPUT

-- and the stored procedure StudentData is define like this


CREATE PROCEDURE dbo.StudentData
		@SchoolNumber AS VARCHAR(50)
		,@SchoolName AS VARCHAR(50)
		,@StudentNumber AS VARCHAR(50)

		,@StudnentName AS VARCHAR(50)   OUTPUT
		,@StudentAddress AS VARCHAR(50)   OUTPUT
		,@Studentbirthdate AS DATE   OUTPUT
		,@StudentPhoneNumber AS VARCHAR(50)   OUTPUT
		,@GuardianName AS VARCHAR(50)  OUTPUT
AS
BEGIN
 ....
END

sabinWeb MCP

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2014-04-17 : 10:16:21

I would like to pass multiple rows. There will be a lot of student numbers that will be passed to the stored procedure. I will have multiple rows return from the stored procedure.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-18 : 01:02:50

Show us some sample/random data and what is the expected output

One way is to use xml

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-19 : 02:13:12

also you can use a new feature in SQL 2008 : CREATE TYPE
--1 create a type School


CREATE TYPE tpSChool  AS TABLE
( 	        SchoolNumber AS VARCHAR(50)
		,SchoolName AS VARCHAR(50)
		,StudentNumber AS VARCHAR(50))

--then You can rewrite the SP something like this:


CREATE PROCEDURE dbo.StudentData
	@varTypeSchool tpSChool  READONLY
AS
BEGIN
	SELECT
		StudnentName
		, StudentAddress
		, Studentbirthdate
		, StudentPhoneNumber
		, GuardianName
	FROM
		YourTableView AS A
		INNER JOIN @varTypeSchool  AS T
		ON A.StudentNumber=T.StudentNumber
END

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources