Stored procedure problem

Question

Post reply

Stored procedure problem

Nuts

SSCrazy

Points: 2547
More actions
November 14, 2006 at 5:22 pm

#114193

hi guys
I am having problems running a stored procedure where i am using two input parameters
my stored procedure is as follows
ALTER
procedure [dbo].[enterdhbnameDhbService]
(
@dhb_service
char, @dhbname char
)
as

SELECT
dbo.DHBMappingTable.[DHB Name], dbo.Agency.DHB_service, dbo.PurchaseUnitMappingTable.PU,
SUM(dbo.[NMDS Data for IDF Report].[Number of caseweighted discharges]) AS Expr1, dbo.AdmissionMappingTable.Admission
FROM
dbo.DomicileCodes INNER JOIN
dbo
.[NMDS Data for IDF Report] ON dbo.DomicileCodes.[Domicile code] = dbo.[NMDS Data for IDF Report].[Domicile Code] INNER JOIN
dbo
.PurchaseUnitMappingTable ON dbo.[NMDS Data for IDF Report].[Purchase Unit] = dbo.PurchaseUnitMappingTable.PU INNER JOIN
dbo
.AdmissionMappingTable ON
dbo
.[NMDS Data for IDF Report].[Admission Type Description] = dbo.AdmissionMappingTable.[Admission Type Description] INNER JOIN
dbo
.Agency ON dbo.[NMDS Data for IDF Report].[Agency Name] = dbo.Agency.Agengy INNER JOIN
dbo
.DHBMappingTable ON dbo.DomicileCodes.[DHB area] = dbo.DHBMappingTable.[DHB Code]
WHERE
(dbo.[NMDS Data for IDF Report].[Financial Year] = '20062007')
GROUP
BY dbo.DHBMappingTable.[DHB Name], dbo.Agency.DHB_service, dbo.PurchaseUnitMappingTable.PU, dbo.AdmissionMappingTable.Admission
HAVING
(dbo.Agency.DHB_service = @dhb_service) and
AND
(dbo.DHBMappingTable.[DHB Name] = @dhbname )

The values of " @dhb_service" and "@dhbname" need to be entered when the stored procedure is executed. Now when I execute the stored procedure through the following statement:

exec enterdhbnameDhbService
@dhb_service = 'canterbury' ,@dhbname = 'south canterbury'
SQL does not give me any results, only empty table gets displayed. I have checked the combination.. This combination does exist in my table

pls help guys

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

Try adding a length specification to the variable definitions:

@dhb_service char(255), @dhbname char(255)

or make them varchar(255), or whatever matches the datatype you're looking up.

And then again, I might be wrong ...
David Webb

Robert Davis One Orange Chip Points: 28027 More actions · Answer 2

Also, try specifying the owner name with the procedure call like you did in the procedure. My guess is that there is probably another version of this procedure in the database named YourUserName.enterdhbnameDhbService.

exec dbo.enterdhbnameDhbService

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

ijaz SSCommitted Points: 1912 More actions · Answer 3

Hi,

As you declare your parameters with the datatype char and default length of the char datatype is one. But you are sending the values greater than this.

You should need to specify the estimated length for the parameters as you required.

I believed that there is no stored procedure with the different owner as david mentioned but its good to check this.

Cheers

cheers