sql procedure - pass parameters for sql query

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

General SQL Server Forums

New to SQL Server Programming

sql procedure - pass parameters for sql query

Author

Topic

ashwika
Starting Member

9 Posts

Posted - 2011-06-01 : 17:28:21

I have two fields –

studentID – integer
and Course – string nvarchar(30) in a database with other fields …….

StudentID - integer
Course - nvarchar
Forename - nvarchar
Surname - nvarchar
DateofBirth – datetime
Address1 nvarchar
Address2 nvarchar
Address3 nvarchar
Postcode nvarchar
Telno nvarchar
Email nvarchar
Mobile nvarchar
Accepted nvarchar

I want to search on these two fields and query the Students_database
and output all records that conform to the search criteria entered.

StudentId and Course can be enterd, or course, or studentID can be entered and
Search is performed to execute the query.

The query should only select those records where ‘Accepted = “YES”

This should be done in a SQL procedure – the two parameters are to be passed in the sql procedure - Please guide how this can be achieved ????

Thankyou

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-01 : 17:31:49

Assuming that the two parameters passed into the stored proc are @StudentId and @Course, the query would be something like this:


Select
   StudentID,
   Course,
   ForeName,
   SurName
   --, other columns here
from
   YourTable
where
   @StudentId = StudentId
   and @Course = Course
   and Accepted = N'YES'

ashwika
Starting Member

9 Posts

Posted - 2011-06-02 : 18:24:06

USE [Students]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetStudentsRecsByParm]
@studentid int = 0,
@course nvarchar(30) = Null

AS

BEGIN

SET NOCOUNT ON;

DECLARE @sql nvarchar(MAX),
@paramList nvarchar(4000)

TRUNCATE TABLE tblEndTableResult

BEGIN
SELECT @sql = 'INSERT INTO tblEndTableResult
SELECT
tblStudentsCourseDetails.StudentID
,tblStudentsCourseDetails.Forename
,tblStudentsCourseDetails.Surname
,tblStudentsCourseDetails.Course
,tblStudentsCourseDetails.Email
,tblStudentsCourseDetails.Tel

FROM tblStudentsCourseDetails

WHERE ((tblStudentsCourseDetails.StudentID = @studentid)
OR (tblStudentsCourseDetails.Course = @course))'

IF @studentID > 0
SELECT @sql = @sql + ' AND tblStudentsCourseDetails.StudentID = @studentid '

IF @course IS NOT NULL

SELECT @sql = @sql + 'AND tblStudentsCourseDetails.Course = course '
print @sql

SELECT @paramList = '
@studentid int,
@course varchar'
print @sql

EXEC sp_executesql @sql, @paramList,
@studentid,
@course
END

BEGIN
SELECT
StudentID
,Forename
,Surname
,Email
,Tel
FROM tblEndTableResult

END
END

This procedure works for studentid , but does not retrieve records for course ......?????? ANy suggestions as to why second parameter
does not work ????

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-06-02 : 19:56:22

quote:
Originally posted by ashwika

...
This procedure works for studentid , but does not retrieve records for course ......?????? ANy suggestions as to why second parameter
does not work ????

This line:

SELECT @sql = @sql + 'AND tblStudentsCourseDetails.Course = course '

should be:

SELECT @sql = @sql + 'AND tblStudentsCourseDetails.Course = @course '

CODO ERGO SUM

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-02 : 19:58:59

I have not run the code, but looking at the pattern it seems like you need to change the second where clause to:


....
IF @course IS NOT NULL

SELECT @sql = @sql + 'AND tblStudentsCourseDetails.Course = @course '
.....

ashwika
Starting Member

9 Posts

Posted - 2011-06-03 : 04:10:24

Still no luck !

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-06-03 : 04:50:41

Try this:

CREATE PROCEDURE [dbo].[GetStudentsRecsByParm]
@studentid int = Null, --Changing 0 to null
@course nvarchar(30) = Null

AS

BEGIN

SET NOCOUNT ON;

TRUNCATE TABLE tblEndTableResult

INSERT INTO tblEndTableResult
SELECT
tblStudentsCourseDetails.StudentID
,tblStudentsCourseDetails.Forename
,tblStudentsCourseDetails.Surname
,tblStudentsCourseDetails.Course
,tblStudentsCourseDetails.Email
,tblStudentsCourseDetails.Tel
FROM tblStudentsCourseDetails
Where tblStudentsCourseDetails.StudentID = isnull(@StudentID, tblStudentsCourseDetails.StudentID)
and tblStudentsCourseDetails.Course = Isnull(@Course,tblStudentsCourseDetails.Course)

SELECT
StudentID
,Forename
,Surname
,Email
,Tel
FROM tblEndTableResult

END

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources