September 1, 2008 at 4:51 am
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.
September 1, 2008 at 5:07 am
The problem is in the following select
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
As the error says, you cannot combine data retrieval (returning a resultset) with assigning a variable. Remove iTableFieldId from the select list and it will work.
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply