CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Senthil Kumar J
Senthil Kumar J

Reputation: 208

'And' 'Or' condition using stored procedure in SQL Server

I have a question about using stored procedure in SQL Server 2005.

This is my stored procedure:

ALTER PROCEDURE [dbo].[tst_user_sp]  
    -- Add the parameters for the stored procedure here 
    @p1 nvarchar = null,    
    @p2 nvarchar = null AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here 
    SELECT state 
    FROM tst_user 
    WHERE user_id = @p1 AND user_pwd = @p2; 
END

My expected output is to get the value of the 'state'. It returned an empty record.

But, after changing the And to Or, I got the output. Why can't I get the output by using And?

My sql query: 

exec tst_user_sp 'lackh', 's'

Upvotes: 0

Views: 2254

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I have no idea if this is related to the problem you are seeing, but your stored procedure has a major problem. The definition is:

ALTER PROCEDURE [dbo].[tst_user_sp]  
-- Add the parameters for the stored procedure here 
@p1 nvarchar = null,    
@p2 nvarchar = null AS

This uses the default length for the nvarchar fields, and the default varies by context. You should always use a length when using string types in SQL. Something like:

ALTER PROCEDURE [dbo].[tst_user_sp] (
-- Add the parameters for the stored procedure here 
    @p1 nvarchar(4000) = null,    
    @p2 nvarchar(4000) = null
) AS

Given what you are doing in the procedure, any use of the default values will result in no value being returned, because of the way the where clause is defined.

Upvotes: 3

Related Questions