Stored proceedure for a crystal report

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 2005 Forums

Transact-SQL (2005)

Stored proceedure for a crystal report

Author

Topic

trackjunkie
Starting Member

31 Posts

Posted - 2009-08-18 : 15:49:48

I am writing a S.P. to display a value on a crystal report. The relevant data on the table looks like:

Job Sequence Usage
199 1 .002
199 2 .009
199 3 .852
132 1 .468
132 2 .584
132 3 .241
303 1 .147
303 2 .684

I want the value of "usage" for a given "Job" were "Sequence" =1.
I created a variable called @job (nvarchar, same as in the table) then said:

AS
SELECT TableName.Usage
FROM TableName

WHERE (TableName.Job = @job and TableName.Sequence = 1 )

The syntax checks and it executes, but the returned value on the report gets no value at all. I know if could be a lot of things, But does anyone see anything obvious wrong with the code.

trackjunkie
Starting Member

31 Posts

Posted - 2009-08-18 : 15:52:47

Spaces were removed when I posted which make my table sample hard to read. FYI the values for line one would be Job= 199, Sequence = 1, Usage = .002

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-18 : 15:55:53

>>I created a variable called @job (nvarchar, same as in the table)

Do you mean an input parameter for the SP? exec the SP in a query window to make sure it's working there first. If it isn't working in a query window then post the entire SP as well as the call you are using to exec the SP.

Be One with the Optimizer
TG

trackjunkie
Starting Member

31 Posts

Posted - 2009-08-18 : 16:52:00

Yes, I meant an input parameter. I executed it in a query window and the return value was zero. So it is not working...sorry i'm not very familiar with the available tools.
The entire stored procedure is:

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

ALTER PROCEDURE [dbo].[RPT_GetUsage] (

@job nvarchar(20)
)
AS
SELECT TableName.Usage
FROM TableName

WHERE (TableName.Job = @job and TableName.Sequence = 1 )

I am calling this only as the data source to a crystal report (actually a subreport).

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-18 : 17:01:52

Return value = 0 just means there was no errors. Are you saying that no rows were returned when you called this:

exec RPT_GetUsage @job = N'199'

btw, is the [job] column also nvarchar ? because the data looks numeric - integer specifically.

Are you sure that rows exist in the table for the @job value you entered and sequence=1?

and your table isn't really named [tableName] is it

Be One with the Optimizer
TG

trackjunkie
Starting Member

31 Posts

Posted - 2009-08-19 : 16:20:59

When I execute that command I get no rows returned. I do get the correct column name, but no values. I double checked that job is an NVARCHAR in the table (it is also a primary key). And I made sure the data existed on my table for my test case.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-19 : 16:32:28

Ok - try this:

In a query window run the statement that is in the SP but with constant values instead of parameters


SELECT Usage
FROM   TableName
WHERE  Job = N'199' --assuming 199 is your "test case"
and    Sequence = 1

Do you get the rows then?

>>And I made sure the data existed on my table for my test case
How did you do that?

EDIT:
>>When I execute that command I get no rows returned
Post the exact command you executed

Be One with the Optimizer
TG

trackjunkie
Starting Member

31 Posts

Posted - 2009-08-19 : 16:44:58

Tried the command you suggested and got the same result...the correct column name with no values. I checked that the test case was valid by actually opening the table and viewing the row we are trying to find.
Does the fact that job and sequence are both primary keys make a difference?

BTW, the table name in not actually table name, i'm just making things more generic...

trackjunkie
Starting Member

31 Posts

Posted - 2009-08-19 : 16:49:43

Exact command I entered was:

exec Flex_RPT_NominalUnitWeight @job = N'2462'

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-19 : 17:01:49

>>BTW, the table name in not actually table name, i'm just making things more generic...
Can you please post the actual command you are using? It's hard to help when we don't see the real code.

I suspect the [job] the value(s) your table has leading spaces or some other characters that you can't see. Just SELECT out all the [JOB] values without using a WHERE clause and Copy and Paste your test case value here. (or open the table the way you did before) Make sure to select the entire value - check for leading/trailing spaces. Is your [sequence] column an integer? if it is some kind of character based type then do the same for that as well.

Be One with the Optimizer
TG

trackjunkie
Starting Member

31 Posts

Posted - 2009-08-20 : 09:05:09

Well, that was it. I used the ltrim command in my where statement and it works. Thanks TG. I'm slowing learning....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources