T-SQL SQL Server - Stored Procedure with parameter

Posted by Ricardo Conte on Stack Overflow See other posts from Stack Overflow or by Ricardo Conte
Published on 2010-05-10T23:13:30Z Indexed on 2010/05/10 23:34 UTC
Read the original article Hit count: 312

Filed under:

t-sql

|

stored

|

procedure

|

parameter

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]

Developer IT