T-SQL SQL Server - Stored Procedure with parameter
- by Ricardo Conte
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]