|
|
Stored procedure returns no value |
|
Author |
Message |
LFUTUREALL
|
Posted: Wed Mar 01 04:20:28 CST 2006 |
Top |
SQL Server >> Stored procedure returns no value
Hi,
I have a stored procedure (the code is below) that I use to retrieve
one value from my database. I tested the code in Query Analyzer, and it
works (I get the value I was looking for). However, when I call the
same code from the stored procedure, I get no value. The code that is
executed is the same and the input parameter is the same. Does anybody
have an idea?
The code:
SELECT Top 1
CharID,
CharName,
CategoryName,
CharDescription,
UserID
FROM [Character]
WHERE CharName='Character'
-- returns the right value
The Stored Procedure:
CREATE PROCEDURE SpCharacters_SelectOneByUserName
@UserName NVarChar
AS
SELECT Top 1
CharID,
CharName,
CategoryName,
CharDescription,
UserID
FROM Character
GO
-- returns no value
SQL Server8
|
|
|
|
|
Niels
|
Posted: Wed Mar 01 04:20:28 CST 2006 |
Top |
SQL Server >> Stored procedure returns no value
> I have a stored procedure (the code is below) that I use to retrieve
> one value from my database. I tested the code in Query Analyzer, and
> it works (I get the value I was looking for). However, when I call the
> same code from the stored procedure, I get no value. The code that is
> executed is the same and the input parameter is the same. Does anybody
> have an idea?
>
> The code:
>
> SELECT Top 1
> CharID,
> CharName,
> CategoryName,
> CharDescription,
> UserID
> FROM [Character]
> WHERE CharName='Character'
>
> -- returns the right value
>
> The Stored Procedure:
>
> CREATE PROCEDURE SpCharacters_SelectOneByUserName
> AS
> SELECT Top 1
> CharID,
> CharName,
> CategoryName,
> CharDescription,
> UserID
> FROM Character
> GO
>
> -- returns no value
>
way you have defined it. You need to give it a size in the declaration:
CREATE PROCEDURE SpCharacters_SelectOneByUserName
Niels
--
**************************************************
* Niels Berglund
* http://staff.develop.com/nielsb
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
**************************************************
|
|
|
|
|
ML
|
Posted: Wed Mar 01 04:28:03 CST 2006 |
Top |
SQL Server >> Stored procedure returns no value
When declaring variables of character data type, length must be specified,
otherwise the default of 1 is used.
In your parameter declaration:
@UserName NVarChar
...add appropriate length:
@UserName NVarChar(<length>)
Use the column's data type.
ML
---
http://milambda.blogspot.com/
|
|
|
|
|
nikolacace
|
Posted: Wed Mar 01 05:47:14 CST 2006 |
Top |
SQL Server >> Stored procedure returns no value
Thanks Niels, it solved the issue..
|
|
|
|
|
|
|