Hi all - I have this sotored proc that when I test the seperate bits I get
the correct results, but when I run the whole thing i get nothing back.

Can anyone see where I am going wrong. s far as I can tell this should work.

ALTER PROCEDURE [dbo].[5D_CaseLetterGeneration]

(@LetterType varchar,

@LenderName varchar)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;



declare @CaseID as varchar;



set @CaseID = (Select top (1) pre_CaseAndReportOrLetterID

from Filteredpre_ReportAndLetter

where statecode = 0 and fived_ReportSentToReportServer = 0

and pre_AccountAndReportorLetteridName = @LenderName and pre_LetterType =
@LetterType)

select * from [Reporting]

where CaseID = @CaseID;

SET NOCOUNT OFF

END

GO

Saira,

You are omitting the len in the parameters and variable declaration. In this
case SQL Server will use 1.

Are those column VARCHAR(1)?

AMB


"Saira" wrote:

> Hi all - I have this sotored proc that when I test the seperate bits I get
> the correct results, but when I run the whole thing i get nothing back.
>
> Can anyone see where I am going wrong. s far as I can tell this should work.
>
> ALTER PROCEDURE [dbo].[5D_CaseLetterGeneration]
>
> (@LetterType varchar,
>
> @LenderName varchar)
>
> AS
>
> BEGIN
>
> -- SET NOCOUNT ON added to prevent extra result sets from
>
> -- interfering with SELECT statements.
>
> SET NOCOUNT ON;
>
>
>
> declare @CaseID as varchar;
>
>
>
> set @CaseID = (Select top (1) pre_CaseAndReportOrLetterID
>
> from Filteredpre_ReportAndLetter
>
> where statecode = 0 and fived_ReportSentToReportServer = 0
>
> and pre_AccountAndReportorLetteridName = @LenderName and pre_LetterType =
> @LetterType)
>
> select * from [Reporting]
>
> where CaseID = @CaseID;
>
> SET NOCOUNT OFF
>
> END
>
> GO
>
>
>

Did you try this?

USE tempdb;
GO

CREATE PROCEDURE dbo.Oops
@LetterType VARCHAR,
@LenderName VARCHAR
AS
BEGIN
SELECT @LetterType, @LenderName;
END
GO

EXEC dbo.Oops
@LetterType = 'blat splunge',
@LenderName = 'juan epstein';
GO

DROP PROCEDURE dbo.Oops;
GO

On May 21, 10:40 am, "Saira" <sa...@5d.com> wrote:
> Hi all - I have this sotored proc that when I test the seperate bits I get
> the correct results, but when I run the whole thing i get nothing back.
>
> Can anyone see where I am going wrong. s far as I can tell this should work.
>
> ALTER PROCEDURE [dbo].[5D_CaseLetterGeneration]
>
> (@LetterType varchar,
>
> @LenderName varchar)
>
> AS
>
> BEGIN
>
> -- SET NOCOUNT ON added to prevent extra result sets from
>
> -- interfering with SELECT statements.
>
> SET NOCOUNT ON;
>
> declare @CaseID as varchar;
>
> set @CaseID = (Select top (1) pre_CaseAndReportOrLetterID
>
> from Filteredpre_ReportAndLetter
>
> where statecode = 0 and fived_ReportSentToReportServer = 0
>
> and pre_AccountAndReportorLetteridName = @LenderName and pre_LetterType =
> @LetterType)
>
> select * from [Reporting]
>
> where CaseID = @CaseID;
>
> SET NOCOUNT OFF
>
> END
>
> GO

In addition to Alejandro's and Aaron's replies, you use TOP(1) without
ORDER BY clause, which makes your results unpredictable.