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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -