My first stored procedure

[madanthrax]

Hi guys,

I have a problem with a select stored procedure.

This runs fine using the query - EXEC dbo.sp_getFileCategory @FileID = 4 (FileID is seed PK)

CREATE PROCEDURE dbo.sp_getFileCategory
@FileID INT = NULL
AS
BEGIN
SET NOCOUNT ON

SELECT
FileID,
FileName,
LinkText,
FileCategory,
UploadDate,
UploadTime,
Notes
FROM
tbl_ItalyFiles
WHERE
FileID = @FileID
END
GO

Row 4 shows up fine.
But what I really want to do is run this: EXEC dbo.sp_getFileCategory @FileCategory = 'filechemical'


CREATE PROCEDURE dbo.sp_getFileCategory
@FileCategory varchar
AS
BEGIN
SET NOCOUNT ON

SELECT
FileID,
FileName,
LinkText,
FileCategory,
UploadDate,
UploadTime,
Notes
FROM
tbl_ItalyFiles
WHERE
FileCategory = @FileCategory
END
GO

Nothing shows up in the results pane, where have I gone wrong please?

Thanks in advance.

_{"Nothing is impossible until proven otherwise"}​

 

[gmmastros]

CREATE PROCEDURE dbo.sp_getFileCategory 
    @FileCategory  varchar[!](SomeNumber)[/!]
AS

When using a varchar data type, you should ALWAYS define the size. In this case, SomeNumber should match the field size of FileCategory in the tbl_ItalyFiles table.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jbenson001]

what datatype is FileCategory?

 

[gmmastros]

As a learning experience, open Query Anaylzer, copy/paste this code, and run it. I suspect the result is not what you expected.

Declare @var varchar
Declare @better VarChar(100)

Set @var = 'hello world'
Set @better = 'hello world'

Select @var, @better

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[madanthrax]

Great! Thank you George for the help...the devil is in the detail...

_{"Nothing is impossible until proven otherwise"}​

 

[SQLDenis]

Yep and it's inconsistent

Declare @var varchar
Declare @better VarChar(100)

Set @var = 'hello world'
Set @better = 'hello world'

Select @var, @better,convert(varchar,@better)

I believe the default is 30 for this

convert(varchar,@better)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/