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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy