|
Home > Archive > MS SQL Server > October 2006 > procedure parameter not working (newbie question)
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
procedure parameter not working (newbie question)
|
|
| pompair 2006-10-24, 6:33 pm |
| Hi!
I have a table Theme:
CREATE TABLE [dbo].[Theme](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](14) COLLATE SQL_Latin1_General_C
P1_CI_AS NOT NULL,
CONSTRAINT [PK_Theme] PRIMARY KEY CLUSTERED
And a stored proc:
ALTER PROCEDURE [dbo].& #91;GetQuestionsForT
heme]
-- Add the parameters for the stored procedure here
@themename nvarchar,
@numofquestions int = 3
AS
BEGIN
SET NOCOUNT ON;
SELECT Question.text
FROM Question, Theme
WHERE Question.themeid=Theme.id AND Theme.name=@themename
END
And I'm using SP like this:
USE GameData;
GO
EXEC dbo. GetQuestionsForTheme
'Love';
The problem is that I get no rows to output! The SP works and returns
rows if I:
1) use Theme.id=@themename (in stead of Theme.name=@themename)
2) change the themeid-parameter to int (in stead of nvarchar).
3) use SP like this: EXEC dbo. GetQuestionsForTheme
1
So, I know the problem lies in the parameter being nvarchar... any
ideas?
-pom-
| |
| John Bell 2006-10-24, 6:33 pm |
| Hi
If there is not an exact match then you may get a hit.
Try
SELECT Question.text
FROM Question
JOIN Theme ON Question.themeid=Theme.id
WHERE Theme.name LIKE @themename + '%'
John
"pompair" wrote:
> Hi!
>
> I have a table Theme:
>
> CREATE TABLE [dbo].[Theme](
> [id] [int] IDENTITY(1,1) NOT NULL,
> [name] [nvarchar](14) COLLATE SQL_Latin1_General_C
P1_CI_AS NOT NULL,
> CONSTRAINT [PK_Theme] PRIMARY KEY CLUSTERED
>
>
> And a stored proc:
>
> ALTER PROCEDURE [dbo].& #91;GetQuestionsForT
heme]
> -- Add the parameters for the stored procedure here
> @themename nvarchar,
> @numofquestions int = 3
> AS
> BEGIN
> SET NOCOUNT ON;
>
> SELECT Question.text
> FROM Question, Theme
> WHERE Question.themeid=Theme.id AND Theme.name=@themename
> END
>
>
> And I'm using SP like this:
>
> USE GameData;
> GO
> EXEC dbo. GetQuestionsForTheme
'Love';
>
>
> The problem is that I get no rows to output! The SP works and returns
> rows if I:
>
> 1) use Theme.id=@themename (in stead of Theme.name=@themename)
> 2) change the themeid-parameter to int (in stead of nvarchar).
> 3) use SP like this: EXEC dbo. GetQuestionsForTheme
1
>
> So, I know the problem lies in the parameter being nvarchar... any
> ideas?
>
> -pom-
>
>
| |
| pompair 2006-10-24, 6:33 pm |
| Actually I found the error myself:
One should denote in parentheses the length of sproc parameter, like
this:
@themename nvarchar(14)
-pom-
|
|
|
|
|