none
Error that varchar can not convert to int for userid ?

    Question

  • Hello

    my stored procedure is below. It give me error that varchar can not convert to int for user id. How can i fix it ? Thanks.

    ALTER PROCEDURE dbo.cashselectdeneme
    		@userid int,
    	@category varchar(20),
    	@subcategory varchar(20),
    	@datetmin smalldatetime,
    	@datetmax smalldatetime,
    	@incoming varchar(5),
    	@outgoings varchar(5),
    	@sifir decimal=0,
    	@komut varchar(5000)
    AS
    	 SET NOCOUNT ON 
    	 SET Statistics io ON
    	 
    
    	
    set @komut='SELECT 
    [id]=[id], [userid]=[userid], [category]=[category], [subcategory]=[subcategory], 
    [explanation]=[explanation], [incoming]=[incoming], [outgoings]=[outgoings],[datet]=[datet]
    FROM cash 
    WHERE 
    userid='+@userid+'
    and
    ((NULLIF('+@category+','') is null)  or (category = '+@category+'))
    and 
    ((NULLIF('+@subcategory+','') is null)  or (subcategory = '+@subcategory+'))
    and
    ((NULLIF('+@datetmin+','') is null)  or (datet >='+ @datetmin+'))
    and
    ((NULLIF('+@datetmax+','') is null)  or (datet<='+@datetmax+'))
    and
    ((NULLIF('+@incoming+','') is null)  or (incoming>'+@sifir+'))
    
    and
    ((NULLIF('+@outgoings+','') is null)  or (outgoings>'+@sifir+'))
    Order by datet desc, id'
    exec @komut
    
    
    

    Saturday, April 20, 2013 3:56 PM

Answers

All replies

  • Hello,

    Do you get the error for the SP call or inside the SP?

    Why do you use dynamic SQL for this, you can write it as a normal SELECT Statement?


    Olaf Helper

    Blog Xing

    Saturday, April 20, 2013 4:01 PM
  • just as Olaf said..try  this 
    ALTER PROCEDURE dbo.cashselectdeneme
    		@userid int,
    	@category varchar(20),
    	@subcategory varchar(20),
    	@datetmin smalldatetime,
    	@datetmax smalldatetime,
    	@incoming varchar(5),
    	@outgoings varchar(5),
    	@sifir decimal=0
    AS
    	 SET NOCOUNT ON 
    	 SET Statistics io ON
    	
    SELECT 
    [id]=[id], [userid]=[userid], [category]=[category], [subcategory]=[subcategory], 
    [explanation]=[explanation], [incoming]=[incoming], [outgoings]=[outgoings],[datet]=[datet]
    FROM cash 
    WHERE 
    userid=@userid
    and
    ((NULLIF(@category,'') is null)  or (category = @category))
    and 
    ((NULLIF(@subcategory,'') is null)  or (subcategory = @subcategory))
    and
    ((NULLIF(@datetmin,'') is null)  or (datet >= @datetmin))
    and
    ((NULLIF(@datetmax,'') is null)  or (datet<=@datetmax))
    and
    ((NULLIF(@incoming,'') is null)  or (incoming>@sifir))
    and
    ((NULLIF(@outgoings,'') is null)  or (outgoings>@sifir))
    Order by datet desc, id


    Hope it Helps!!


    • Edited by Stan210 Saturday, April 20, 2013 4:02 PM
    Saturday, April 20, 2013 4:02 PM
  • i try to change table name with @komut. I prefer SP to performance.

    Thanks.

    Saturday, April 20, 2013 4:58 PM
  • i try to change table name with @komut. I prefer SP to performance.

    If you build an SQL string dynamically, it does not run faster because you put it in a stored procedure.

    I have an article on my web site that in detail discusses how to build dynamic searches with good performance. Or more precisely, I have two depending on your version of SQL Server:
    http://www.sommarskog.se/dyn-search.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, April 20, 2013 6:41 PM