SQL Server Performance

Error converting data type nvarchar to int

Discussion in 'General Developer Questions' started by saivenkatesan, Aug 17, 2006.

  1. saivenkatesan New Member

    I am new to sql.This is my procedure.

    ALTER PROCEDURE dbo.spValidateUserSecurityToken
    AS
    BEGIN
    DECLARE @sdbname VARCHAR(10)
    DECLARE @sownername VARCHAR(10)
    DECLARE @servername VARCHAR(10)
    DECLARE @procname VARCHAR(300)

    SELECT
    @servername = servername,
    @sdbname = dbname,
    @sownername = ownername

    FROM
    dbo.TestingProc

    declare @Permission int
    declare @nResult int
    EXEC(@servername+'.'+@sdbname +'.'+@sownername+ '.'+'spCheckAWSiProcedurePermissionTest @nResult='+@Permission+' OUTPUT')
    PRINT @Permission



    i am getting the following error

    Error converting data type nvarchar to int.


    Please someone help me to find.

  2. bijesh New Member

    I guess, the string concatenation you are making with Int datatype, while firing EXEC is causing problem.
    Try typecasting the Int variables to string while concatenating inside the EXEC statement.

    Try this:

    ALTER PROCEDURE dbo.spValidateUserSecurityToken
    AS
    BEGIN
    DECLARE @sdbname VARCHAR(10)
    DECLARE @sownername VARCHAR(10)
    DECLARE @servername VARCHAR(10)
    DECLARE @procname VARCHAR(300)
    DECLARE @Permission INT
    DECLARE @nResult INT

    SELECT
    @servername = servername,
    @sdbname = dbname,
    @sownername = ownername
    FROM
    dbo.TestingProc

    SET @procname=@servername+'.'+@sdbname+'.'+@sownername+'.spCheckAWSiProcedurePermissionTest'
    SET @Permission=0 -- OR whatever value you want to initialise with.

    EXEC @procname @nResult=@Permission OUTPUT

    PRINT @Permission
    END

    Thanks,

    Bijesh
  3. Madhivanan Moderator

    Instead of @Permission in query use cast(@Permission as varchar(20))

    Madhivanan

    Failing to plan is Planning to fail
  4. saivenkatesan New Member

    Thanks bijesh,it is working.
  5. FrankKalis Moderator

    For an explanation, why this happens, look at BOL for "Data type precedence". [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Share This Page