Post reply

What is the correct syntax for this dynamic sql statement?

  • October 29, 2007 at 8:37 am

    #199511

    Hi,

    I am preparing for a stored proc that will receive 2 parms @AuditItem and @status

    @AuditItem is varchar(50) and @status is int

    My trial test got this error:

    Conversion failed when converting the varchar value 'SELECT customer, host, timestamp, dashboardtimestamp ,BIOS where BIOS=' to data type int.

    Declare @sql VarChar(1000)

    SELECT @sql = 'SELECT customer, host, timestamp, dashboardtimestamp ,'

    SELECT @sql = @sql + @AuditItem

    SELECT @sql = @sql + ' where ' + @AuditItem + '=' + @status

    Exec ( @sql)

    I am preparing for a stored proc that will receive 2 parms @AuditItem and @status

    @AuditItem is varchar(50) and @status is int

    Thanks

  • October 29, 2007 at 8:49 am

    #746270

    I found the answer

    SET @sql = .... ' + CAST(@Status AS VARCHAR(10))

    Thanks

  • October 29, 2007 at 8:56 am

    #746276

    Hi there,

    you have this statement:

    SELECT @sql = @sql + ' where ' + @AuditItem + '=' + @status

    and you say that @AuditItem is VARCHAR and @status is INT.

    Because you have "equals" between these 2 parameters you will get an error because you are trying to compare a varchar with an int. So for this you have to convert the @status paramater to VARCHAR (example CONVERT(VARCHAR(50),@Status).

    One more thing .. i don't see in this dynamic query .. when you build it .. the FROM keyword ... :ermm:

    I hope i understood your question and i could help,

    Oana.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply