T-SQL SQL Server - Stored Procedure with parameter

Question

Please, the first TSQL works FINE, the second does not. I guess it must be a simple mistake, since I am not used to T-SQL. Thank you for the answers. R Conte.

*** WORKS FINE *********************************** (parm hard-coded)
ALTER PROCEDURE rconte.spPesquisasPorStatus

AS
SET NOCOUNT ON 

SELECT pesId, RTRIM(pesNome), pesStatus, 
           pesPesGrupoRespondente, pesPesQuestionario, 
       pesDataPrevistaDisponivel, pesDataPrevistaEncerramento, 
       pesDono
FROM dbo.tblPesquisas
WHERE (pesStatus = 'dis') 
    ORDER BY pesId DESC

RETURN

    ---------------------------------

Running [rconte].[spPesquisasPorStatus].

pesId       Column1                                                                                                                                                          pesStatus pesPesGrupoRespondente pesPesQuestionario pesDataPrevistaDisponivel pesDataPrevistaEncerramento pesDono     
----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- --------- ---------------------- ------------------ ------------------------- --------------------------- ----------- 
29          XXXXXXXXX xxxxx                                                                                                                                           dis       17                     28                 5/5/2010 08:21:12         5/5/2010 08:21:12           1           
28          Xxxxxxxx xxxxxxxxxxxxx                                                                                                                                                dis       16                     27                 5/5/2010 07:44:12         5/5/2010 07:44:12           1           
27          Xxxxxxxxxxxxxxxxxxxxxxx 

*** DOES NOT WORK ************************ (using a parm; pesStatus is nchar(3))

ALTER PROCEDURE rconte.spPesquisasPorStatus
    (@pPesStatus nchar(3) = 'dis')
AS
SET NOCOUNT ON 

SELECT pesId, RTRIM(pesNome), pesStatus, 
       pesPesGrupoRespondente, pesPesQuestionario, 
       pesDataPrevistaDisponivel, pesDataPrevistaEncerramento, 
       pesDono
FROM dbo.tblPesquisas
WHERE (pesStatus = @pPesStatus) 
ORDER BY pesId DESC

RETURN

---------------------------

Running [rconte].[spPesquisasPorStatus] ( @pPesStatus = 'dis' ).

pesId       Column1                                                                                                                                                          pesStatus pesPesGrupoRespondente pesPesQuestionario pesDataPrevistaDisponivel pesDataPrevistaEncerramento pesDono     
----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- --------- ---------------------- ------------------ ------------------------- --------------------------- ----------- 
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [rconte].[spPesquisasPorStatus]

Answer 1

Try

[rconte].[spPesquisasPorStatus] ( @pPesStatus = N'dis')

or simply

spPesquisasPorStatus N'dis'

Answer 2

Ok, for my second attempt, I went ahead and created the table and the stored procedure in my own local db!

Your code works flawlessly, as you can see in my screenshot. Are you doing something silly, like running the wrong stored procedure accidentally, or maybe running it on the wrong database (this happens to everyone - don't feel bad!)?

Or maybe I am doing something wrong? But I made a table like I imagine yours to be, and used your exact stored procedure. It worked fine with and without the 'dis' parameter. I hope this helps!

You might need to start from scratch on a new database in a new session just for a "sanity check"

Click here for FULL SIZE

Answer 3

When it asks you for the parameters in the VS dialog box, simply put the string in the Value column, do not put it in quotes or with an N in front of it.

i.e. dis rather than N'dis'

Visual Studio automatically puts strings in the correct format when you select "Execute Stored Procedure..." from the right click menu.