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.
| Author |
Topic |
|
JAdauto
Posting Yak Master
160 Posts |
 Posted - 2007-02-26 : 23:29:05
|
| I have a stored procedure that is not returning data consistently. When I paste the SQL SELECT statement from my stored procedure into Query Analyzer and declare and set the parameter values, then I get 23 records returned from my dataset. When I paste the name of the stored procedure and the parameter values, I get 0 records returned. I have tested other stored procedures this way and it seems to work fine, except with this one sp. Is there a limit on the number of parameters that a sp can have (I have 7 on this one)? Has anyone ever seen anything like this where the exact SQL statement works except when called from the SP name? If this doesn't make sense, I can post the actual text I am pasting into QA.Thanks so much in advance,JAdauto |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-02-27 : 00:07:37
|
| 7 parameters is not a problem.Are you passing dates? If they are character then the convertion will depend on the localle.Pass them as yyyymmdd and not dd/mm/yyyy, yyyy-mm-dd, mm/dd/yyyy, ......==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
csundaresh
Starting Member
5 Posts |
Posted - 2007-02-27 : 10:05:50
|
quote:
Originally posted by JAdauto
I have a stored procedure that is not returning data consistently. When I paste the SQL SELECT statement from my stored procedure into Query Analyzer and declare and set the parameter values, then I get 23 records returned from my dataset. When I paste the name of the stored procedure and the parameter values, I get 0 records returned. I have tested other stored procedures this way and it seems to work fine, except with this one sp. Is there a limit on the number of parameters that a sp can have (I have 7 on this one)? Has anyone ever seen anything like this where the exact SQL statement works except when called from the SP name? If this doesn't make sense, I can post the actual text I am pasting into QA.hanks so much in advance,JAdauto
There is no limit on the number of parameters you pass to a stored procedure. Your problem seems to be in converting one or more parameters into the correct format. If you can give the text of your store procedure, may be it will become easier to diagnose your problem. |
|
|
|
JAdauto
Posting Yak Master
160 Posts |
Posted - 2007-02-27 : 12:32:50
|
| I appreciate the responses. I had started to post all my text in here when I found the problem. When I pasted the SQL SELECT statement into QA, I declared my variables and set their values that would normally be the parameters. And when I declared my nvarchar, I was setting the size: DECLARE @vs_EntitySubString nvarchar (100). I was NOT declaring the size at the top of my stored procedure when I was setting the parameter. So I set the size in my CreateProcedure for that parameter and now it works. This just goes to show me that sometimes I make things entirely too difficult... when it could be staring me right in the face. I appreciate the responses however. I always find such wonderful info on this site. |
|
|
|
|
|
|
|
|
Calling a Stored Procedure