executed successfully, but no records returned..BS! 2

[embryo]

I have a simple stored procedure that executes successfully, but always says no records returned...this is BS!

I can run the proc in Query analyzer and it also says executed successfully...but returns no resultset..

There's just one parameter being passed to the SP, whose value would be something like: "hot", or "promised", or "brkn proms"...

ALTER Procedure dbo.QryBuildList @Text0 nvarchar(20)
As
	return 
SELECT dbo.TblDebtors.DebtorID, 
    dbo.TblDebtors.AccountName, dbo.TblDebtors.Address1, 
    dbo.TblDebtors.HomePhone, dbo.TblDebtors.CurrentBalance, 
    dbo.TblDebtors.PromisePayAmount, dbo.TblDebtors.terms, 
    dbo.TblDebtors.WorkPhone, dbo.TblDebtors.ClientID, 
    dbo.TblDebtors.PromiseDate, dbo.TblDebtors.PaidToDate,
    dbo.TblClients.ClientName
FROM dbo.TblDebtors INNER JOIN dbo.TblClients 
    ON  dbo.TblClients.ClientID = dbo.TblDebtors.ClientID 
WHERE (dbo.TblDebtors.StatusID=' + @Text0 +')

I've verified that every record in TblDebtors has a value in it's StatusID field, so it's got me pulling my hair out in big frustrated strands...

I've even whittled it down to this and get the same result:

ALTER Procedure dbo.QryBuildList @Text0 nvarchar(20)
As
return
SELECT dbo.TblDebtors.DebtorID, 
    dbo.TblDebtors.AccountName, dbo.TblDebtors.Address1, 
    dbo.TblDebtors.HomePhone, dbo.TblDebtors.CurrentBalance, 
    dbo.TblDebtors.PromisePayAmount, dbo.TblDebtors.terms, 
    dbo.TblDebtors.WorkPhone, dbo.TblDebtors.ClientID, 
    dbo.TblDebtors.PromiseDate, dbo.TblDebtors.PaidToDate
FROM dbo.TblDebtors
WHERE (dbo.TblDebtors.StatusID  = ' + @Text0 +')

What am I doing wrong?

Thanks,



Steve
---------------------------------------
IF WebApplicationProgrammer = True Then
ElectronicSheepHerder = True
End If

 

[Pattycake245]

You don't need quotes and + signs around your variable. You only need those when you are creating queries using dynamic strings.

ALTER Procedure dbo.QryBuildList @Text0 nvarchar(20)
As
return
SELECT dbo.TblDebtors.DebtorID,
dbo.TblDebtors.AccountName, dbo.TblDebtors.Address1,
dbo.TblDebtors.HomePhone, dbo.TblDebtors.CurrentBalance,
dbo.TblDebtors.PromisePayAmount, dbo.TblDebtors.terms,
dbo.TblDebtors.WorkPhone, dbo.TblDebtors.ClientID,
dbo.TblDebtors.PromiseDate, dbo.TblDebtors.PaidToDate
FROM dbo.TblDebtors
WHERE (dbo.TblDebtors.StatusID = @Text0)

Tim

 

[dbomrrsm]

(dbo.TblDebtors.StatusID = ' + @Text0 +')

do you want this if you just want ' before and after do something like

declare @Text0 nvarchar (20)
set @Text0 = 'bill'
select test = ''''+@Text0+''''

ie

(dbo.TblDebtors.StatusID  = ''''+@Text0+'''')

I thin maybe

(dbo.TblDebtors.StatusID  = @Text0)

might work though

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[embryo]

Hi Tim-

I should've mentioned that I'd already tried removing the tics and plus's, but it seemed to me that since these are text values, not numeric, that I had to encapsulate with the tics..in either case, though, I get the same message indicating it executed successfully but didn't return any records.

Here's the real kicker...when I run the following in query analyzer, I still just get "The command(s) completed successfully."..but with no records returned.....and I KNOW that there are at least 200 records with the value that I hardcoded below:

ALTER Procedure dbo.QryBuildList @Text0 nvarchar(20)
As
return
SELECT dbo.TblDebtors.DebtorID, 
    dbo.TblDebtors.AccountName, dbo.TblDebtors.Address1, 
    dbo.TblDebtors.HomePhone, dbo.TblDebtors.CurrentBalance, 
    dbo.TblDebtors.PromisePayAmount, dbo.TblDebtors.terms, 
    dbo.TblDebtors.WorkPhone, dbo.TblDebtors.ClientID, 
    dbo.TblDebtors.PromiseDate, dbo.TblDebtors.PaidToDate
FROM dbo.TblDebtors
WHERE (dbo.TblDebtors.StatusID  =  'hot')

DBomrssm, that first bit of advice seems like the long way around what I did in the code above, right??..just using a hard-coded value pretty much..

Steve
---------------------------------------
IF WebApplicationProgrammer = True Then
ElectronicSheepHerder = True
End If

 

[embryo]

as another footnot, however, when I JUST run the select in QA, it returns records as I'd expect:

SELECT dbo.TblDebtors.DebtorID, 
    dbo.TblDebtors.AccountName, dbo.TblDebtors.Address1, 
    dbo.TblDebtors.HomePhone, dbo.TblDebtors.CurrentBalance, 
    dbo.TblDebtors.PromisePayAmount, dbo.TblDebtors.terms, 
    dbo.TblDebtors.WorkPhone, dbo.TblDebtors.ClientID, 
    dbo.TblDebtors.PromiseDate, dbo.TblDebtors.PaidToDate
FROM dbo.TblDebtors
WHERE (dbo.TblDebtors.StatusID  =  'hot')

...what the heck???

Steve
---------------------------------------
IF WebApplicationProgrammer = True Then
ElectronicSheepHerder = True
End If

 

[SQLSister]

HOw exactly are you executing the sp?

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[embryo]

executing it from a Report in an Access 2003 ADP....

I just now used Stored Procedure designer in my ADP and re-created the SPROC, and it works fine???...go figure...

The original SPROC was the result of having upsized an MDB query...perhaps it was just corrupt or something...
Sorry to have been such a bother, and thanks very much for trying to help-


Steve
---------------------------------------
IF WebApplicationProgrammer = True Then
ElectronicSheepHerder = True
End If

 

[Pattycake245]

ALTER Procedure dbo.QryBuildList @Text0 nvarchar(20)
As
return
SELECT dbo.TblDebtors.DebtorID,
dbo.TblDebtors.AccountName, dbo.TblDebtors.Address1,
dbo.TblDebtors.HomePhone, dbo.TblDebtors.CurrentBalance,
dbo.TblDebtors.PromisePayAmount, dbo.TblDebtors.terms,
dbo.TblDebtors.WorkPhone, dbo.TblDebtors.ClientID,
dbo.TblDebtors.PromiseDate, dbo.TblDebtors.PaidToDate
FROM dbo.TblDebtors
WHERE (dbo.TblDebtors.StatusID = 'hot')

This may be a shot in the dark but the fact that you have a return statement right after the ALTER PROCEDURE portion would cause the sproc to terminate, and give the message completed sucessfully. I just tested this theory on one of my sprocs and this is what happened.

 

[SQLSister]

Good spot Pattycake; I never even noticed that. Yes return would indeed end the execution of the sp.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[Pattycake245]

Thanks, The question is though will embryo check back. Oh well, feels good to help anyway

 

[embryo]

I checked back, and lo and behold, I've again been HELPED!!!

Thanks a gazillion!

Steve
---------------------------------------
IF WebApplicationProgrammer = True Then
ElectronicSheepHerder = True
End If