Parameters in SQL Server 2008

Question

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

I see nothing wrong here. How are you calling the procedure? — Joe Stefanelli
– Joe Stefanelli, Commented Nov 1, 2011 at 14:54
WOW! I'm sorry guys, I had the default value in SSRS set to an invalid name. Stupid Mistake. Thanks for looking into my problem. You all at least let me know my syntax was correct and I was not sure about that. — 2boolORNOT2bool
– 2boolORNOT2bool, Commented Nov 1, 2011 at 15:26

JonH · Accepted Answer · 2011-11-01 14:54:03Z

1

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 ?

answered Nov 1, 2011 at 14:54

JonH

33.2k13 gold badges93 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

2boolORNOT2bool Over a year ago

Yes I do need both conditions to be met so I used the AND operator and yes if the where clause is deleted I get all the data I need but all divisions and all types. If I hard code the parameter it will give me any string I put in. I'm stumped!

JonH Over a year ago

I guess i dont understand what you mean. Just hard code one condition, what is hte result of just one condition? Post sample data it helps us we are not mind readers.

2boolORNOT2bool Over a year ago

You were right the condition was simply not being met because I had a default value set in SSRS and it wasn't correct. This was simply "user error" but you let me know my syntax was correct so I checked you for helping me. Thanks!

JonH Over a year ago

@2boolORNOT2bool - it happens a lot, especially with default values and tinkering with SSRS, I'm glad you got it squared away.

2boolORNOT2bool Over a year ago

Yes, Thanks again to everyone that looked in on this. Sorry to waste your time but I definately appreciate it.

Widor · Accepted Answer · 2011-11-01 14:56:14Z

1

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.

answered Nov 1, 2011 at 14:56

Widor

13.3k7 gold badges47 silver badges67 bronze badges

Comments

gbn · Accepted Answer · 2011-11-01 15:14:12Z

1

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

answered Nov 1, 2011 at 15:14

gbn

434k84 gold badges602 silver badges690 bronze badges

Collectives™ on Stack Overflow

Parameters in SQL Server 2008

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related