CODEWITHSUNDEEP
sql-server-2008
2boolORNOT2bool
2boolORNOT2bool

Reputation: 567

Parameters in SQL Server 2008

I have a stored procedure that pulls data for a report. I'm having a problem with the parameters. I have a couple temp tables and some joins that work so I have omitted them below. The problem is this line:

WHERE
SeminarDivision = @SeminarDivision AND SeminarType = @SeminarType

When I put this where clause in to use my seminar parameters the stored proc returns nothing But I need to generate a report based on those two parameters. So where do the parameters go? Can anyone help?

@StartDate DateTime,
@EndDate DateTime,
@SeminarDivision VARCHAR(50),
@SeminarType VARCHAR(50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

      ... OMITTED    
SELECT 
    WL.PID,
    CONVERT(varchar(20), upper(substring(FirstName,1,1))+
        LOWER(substring(FirstName,2,19))) AS FirstName,
    CONVERT(varchar(20), upper(substring(LastName,1,1))+
        LOWER(substring(LastName,2,19))) AS LastName,
    S.SeminarDivision,
    S.SeminarType,
    S.StartDate,
    S.SeminarLocation
FROM 
    @tblWaitList WL
    INNER JOIN @tblSeminar S ON WL.SeminarGuid=S.SeminarGuid
WHERE
    SeminarDivision = @SeminarDivision AND SeminarType = @SeminarType
ORDER BY
    LastName,FirstName,StartDate

Upvotes: 1

Views: 111

Answers (3)

gbn
gbn

Reputation: 432180

You are not setting parameters correctly for the call.

Try this in SSMS, change values accordingly

EXEC Proc '20110101', '20111101', 'PossibleDivision', 'PossibleType'

If this fails, then show us "OMITTED" code

if this works, show us how you are calling this from the client code

Upvotes: 1

Widor
Widor

Reputation: 13275

There's nothing wrong with the 'location' of your params.

If you're getting no data back, it's either because you've not populated @tblWaiList or @tblSeminar or because the records simply don't match your WHERE clause.

Check your params have the value you think they do by executing print @SeminarDivision etc.

SELECT * FROM @tblSeminar may give you a clue too.

Upvotes: 1

JonH
JonH

Reputation: 33141

First and foremost there is nothing wrong with your code, when asking where do these parameters go, they go exactly where you put them. The question is - is the data coming in for SeminarDivision and SeminarType the right type of data? For instance just as a test, copy the code into a new sql code query inside the editor. Run the command without the where, if you get values great. Now change the where to

WHERE SeminarDivision = "Possible_Value"

Where Possible_Value should be a possible value...If it returns rows, good...now add the second condition also hardcoding a value:

WHERE SeminarDivision = "Possble_Value" AND SeminarType="Possible_Value_2"

Getting any data? Is it possible you want OR rather then AND ?

Upvotes: 1

Related Questions