Got Error when executing script.

  • Hi All,

    I got this error when i was executing this script.

    The error is:

    Server: Msg 141, Level 15, State 1, Procedure AlterTableByiTableFieldId, Line 37

    A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

    And the ccript is:

    ALTER PROCEDURE [DBO].[AlterTableByiTableFieldId]

    (

    @iTableFieldId [INT]

    )

    AS

    BEGIN

    /*

    Written By Syed Sanaullah Khadri

    Date: 09-01-2008

    Logic Comments To alter the existing table

    EXEC AlterTableByMenuId

    SELECT * FROM TableField

    SELECT * FROM MainMenu

    */

    SET NOCOUNT ON

    DECLARE @TableName [VARCHAR](100),@SQLString [VARCHAR](2000),@vFieldsName [VARCHAR](100),@vDefault [VARCHAR](50)

    DECLARE @iDataTypeId [INT],@iFieldLength [INT],@iMenuId [INT]

    DECLARE @bIsPrimary [BIT],@bIsIdentity [BIT],@bIsNull [BIT],@bArchive [BIT]

    SET @TableName=NULL

    SET @SQLString=NULL

    SET @iMenuId=NULL

    SET @vFieldsName=NULL

    SET @vDefault=NULL

    SET @iDataTypeId=NULL

    SET @iFieldLength=NULL

    SET @bIsPrimary=NULL

    SET @bIsIdentity=NULL

    SET @bIsNull=NULL

    SET @bArchive=NULL

    SELECT iTableFieldId,@iMenuId=iMenuId,@vFieldsName=vFieldsName,@iDataTypeId=iDataTypeId,

    @iFieldLength=iFieldLength,@bIsPrimary=bIsPrimary,@bIsIdentity=bIsIdentity,

    @bIsNull=bIsNull,@vDefault=vDefault,@bArchive=bArchive

    FROM TableField

    WHERE iTableFieldId=@iTableFieldId

    SELECT @TableName=vRefTable FROM MainMenu WHERE iMenuId=@iMenuId

    IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE xtype='U')

    BEGIN

    -- Adding new column

    SET @SQLString='ALTER TABLE '+@TableName+' ADD '+@vFieldsName+' '+@iDataTypeId+'('+@iFieldLength+') '+@bIsPrimary+' '+@bIsNull

    PRINT @SQLString

    END

    ELSE

    BEGIN

    SELECT 'Table DoesNot Exist'

    END

    SET NOCOUNT OFF

    END

    Please check it carefully and help,

    i have to complete it by today.

    Thanks in advance.

    Syed Sanaullah Khadri.

  • HI there,

    SELECT iTableFieldId,@iMenuId=iMenuId,@vFieldsName=vFieldsName,@iDataTypeId=iDataTypeId,

    @iFieldLength=iFieldLength,@bIsPrimary=bIsPrimary,@bIsIdentity=bIsIdentity,

    @bIsNull=bIsNull,@vDefault=vDefault,@bArchive=bArchive

    FROM TableField

    WHERE iTableFieldId=@iTableFieldId

    In the above line of code, you are assigning values to variables but you are also selecting the value iTableFieldId.

    iTableFieldId needs to be left out or assigned to a variable like the other values.

    Thanks

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • Dulicate post.

    Replies to the following thread please:

    http://www.sqlservercentral.com/Forums/Topic561941-8-1.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks,

    I got it with ur help.

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

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