Take the 2-minute tour ×

hey im trying to use this code but i dont know why im getting this error :

Msg 8114, Level 16, State 5, Procedure stat_mob_user_spec, Line 23
Error converting data type nvarchar to bigint.

Code is :

ALTER PROCEDURE [dbo].[stat_mob_user_spec] (@bt NVARCHAR(60) = NULL)
    DECLARE @ResultValue FLOAT,
            @test        NVARCHAR(70)


    SET @test = 'and blood_type = ' + @bt;

    IF @bt = 'abc'
          SET @test = '';

    SELECT @ResultValue = COUNT(id_tips)
    FROM   tips,
    WHERE  blood_id = id_blood + @test;

    IF @ResultValue <> 0
          COMMIT TRAN

    RETURN @ResultValue 

this code is working if i put in the parameter ( abc ) ... ill got result .. but im getting error if i try anything else ... any help ?

Im using sql server 2008

share|improve this question
You are trying to concatenate a string @test to your where clause. You need to look at using dynamic sql –  bluefeet Aug 22 '13 at 21:33
add comment

1 Answer

up vote 3 down vote accepted

Your query is:

SELECT @ResultValue = COUNT(id_tips)
from tips,blood 
where blood_id = id_blood + @test ;

You are adding a string value (@test) to whatever id_blood is. I imagine that it is a bigint and this is not allowed.

It looks like you might be thinking dynamic SQL. But this is not how you code dynamic SQL. I would suggest that you talk to someone locally about the correct way to write stored procedures in SQL Server (or at least read the documentation on the select statement). Also, learn proper join syntax. Just say "no" to commas in the from clause.

share|improve this answer
thatss the word .. "dynamic sql ".. thx man ! :D im gonna make some research about it now ! btw im new in sql :P –  jolyTimePopCorn Aug 22 '13 at 21:39
If you want a good example step by step of how to write Dynamic SQL I just answered the question yesterday... though its a different scenario look at my accepted answer for a step by step way of how to write it: stackoverflow.com/questions/18360118/… –  logixologist Aug 22 '13 at 21:52
thank you ! i just writen my first dynamic sql :) –  jolyTimePopCorn Aug 22 '13 at 22:11
add comment

Your Answer


