Got Error when executing script.

Question

Got Error when executing script.

Sanaullah

SSC Eights!

Points: 892
More actions
September 1, 2008 at 4:51 am

#75306

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.

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

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

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

Dulicate post.

Replies to the following thread please:

http://qa.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

Sanaullah SSC Eights! Points: 892 More actions · Answer 3

Sanaullah

SSC Eights!

Points: 892

September 1, 2008 at 5:11 am

#865637

Thanks,

I got it with ur help.