none
Stored Procedure and VarChar parameter

    Question

  • I'm having the following (abbreviated) stored procedure:

    Code Snippet

    CREATE PROCEDURE proc_SomeSmartName @SomeVariable VARCHAR AS
    BEGIN SELECT COUNT(ID) AS SomeLabel, SomeField
    FROM SomeTable
    GROUP BY SomeField
    HAVING SomeField = @SomeVariable
    END


    Now my problem: It doesn't seem to work if I give the @SomeParameter a string to work with, neither via SqlCommandObject nor directly in the Management Studio. The following returns zero rows:

    Code Snippet

    DECLARE    @return_value int
    EXEC    @return_value = [dbo].[proc_SomeSmartName]
            @SomeVariable = 'MyText'
    SELECT    'Return Value' = @return_value


    Funny enough, when I have the following query, it works perfectly:

    Code Snippet

    SELECT COUNT(ID) AS SomeLabel, SomeField
    FROM SomeTable
    GROUP BY SomeField
    HAVING SomeField = 'MyText'


    Returning one row as it should. SomeField is an NVarChar field, but I tried casting it to VarChar without any benefit, and I also supplied the parameter as NVarChar to test, both without further success. And 'MyText' does exist in the database, in both cases when I run the stored procedure and when I run the SQL statement directly.

    What am I doing wrong?
    Monday, March 03, 2008 2:02 PM

Answers

  •  

    My first thought is your varchar definition will only pass in one character because it's defined as varchar without a length (e.g. varchar(150)).

    Have you run sql profiler to see what is actually being executed by sql server?

    Monday, March 03, 2008 2:06 PM

All replies

  •  

    My first thought is your varchar definition will only pass in one character because it's defined as varchar without a length (e.g. varchar(150)).

    Have you run sql profiler to see what is actually being executed by sql server?

    Monday, March 03, 2008 2:06 PM
  • Awesome, thanks Big Smile

    Great, fast and working answer Smile

    And the next time when I have such a problem I'll remember the profiler suggestion Smile
    Monday, March 03, 2008 2:08 PM
  •  

    Glad I could help. Do you mind setting this thread as "Answered"?

    Thanks!

    Monday, March 03, 2008 2:13 PM