Syntax error near user input - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Syntax error near user input

Author

Topic

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-02 : 11:29:52

Hi,

I am getting a syntax error near @database(insert into UtilizationData_PVZ.dbo.@database) below.

Also at all line where i have it as @database.
I am writing a SP where user has to input the database name, and the rest of the procedure should happen on the user entered database name.

Please help me find the error in the code.

ALTER procedure [dbo].[spAutoDataCollectionPVZ_TEST]
AS
declare @devicename varchar(50)
declare @daterange varchar(50)
declare @database varchar(100)

declare auto1 cursor for select systName, Daterange from Test1, Test2

open auto1
fetch next from auto1 into @devicename, @daterange
while @@fetch_status = 0
begin
insert into UtilizationData_PVZ.dbo.@database
exec Server.DB..sp @devicename, @daterange
fetch next from auto1 into @devicename, @daterange
end
close auto1
deallocate auto1

begin
alter @database
add column deltaTime int

alter @database
add column InterfacesName varchar(151)
end

begin
update @database
set deltaTime = '300000'

update @database
set InterfacesName = InterfaceName
end

begin
update @database
set InterfacesName = substring(InterfacesName, 1, len(InterfacesName)-charindex(' ', reverse(InterfacesName)))
end

begin
select UtilizationData_PVZ.@database.systName,
PadhmaTestDB1.dbo.InterfaceSumry_USCore_UP_Final.Vendor,
PadhmaTestDB1.dbo.InterfaceSumry_USCore_UP_Final.InterfaceDescription,
convert(varchar, convert(datetime, SlotDateTime), 101) + ' ' + convert(varchar, convert(datetime, SlotDateTime), 108)AS SlotDateNTime,
UtilizationData_PVZ.@database.deltaTime,
((UtilizationData_PVZ.@database.LoadPctIn * UtilizationData_PVZ.@database.InterfaceSpeed)/8) AS bytesIn,
((UtilizationData_PVZ.@database.LoadPctOut * UtilizationData_PVZ.@database.InterfaceSpeed)/8) AS bytesOut
into @database_Final
FROM PadhmaTestDB1.dbo.InterfaceSumry_USCore_UP_Final
JOIN UtilizationData_PVZ.@database
ON UtilizationData_PVZ.@database.InterfacesName =
PadhmaTestDB1.dbo.InterfaceSumry_USCore_UP_Final.IVInstanceName

end
GO

Thank you.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-09-02 : 11:33:58

you cannot specify a table as a variable, will have to use dynamic sql in this case.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-02 : 11:35:10

not sure why your table value comes from a variable

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-02 : 11:39:33

Hi,

Isnt @database itself a dynamic one?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-02 : 11:43:30

nope..you cant use variable directly like this in update,alter,... you need to use dynamic sql (exec,sp_executesql) for this

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-02 : 13:07:41

Hi,
I am trying out some examples in dynamic sql.
Below is a small SP.
It would give me error when i try to update. Error saying id is invalid..
ALTER Procedure testdynamic2
@TableName VarChar(100)
AS

Declare @SQL VarChar(1000)
Declare @vary varchar(100)
Declare @final varchar(1000)

SELECT @SQL = 'ALTER TABLE '
SELECT @SQL = @SQL + @TableName
select @vary = 'add column ' + id
select @final = @sql + @vary

Exec ( @final)
GO

Could you please guide me here.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-02 : 13:08:54

whats id? where does that come from?

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-02 : 13:11:31

Thats the column i want to add with type int.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-02 : 13:12:48

but isnt id an int col?

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-02 : 13:16:32

I want to :
Alter table test2
add column ID int

this i want to do via dynamic sql...because the table name will be @tablename input from user.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-02 : 13:20:40

then you need pass value 'id' via variable and append it to alter column

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-09-02 : 14:29:04

before doing

Exec ( @final)

try doing

print @final

just to check if the query you're forming is correct

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-02 : 14:32:21

Hi tried this to update a table:

alter Procedure testdynamic3Update
@TableName VarChar(100)
AS
set nocount on
Declare @SQL VarChar(1000)
Declare @vary varchar(100)
Declare @final varchar(1000)

SELECT @SQL = 'update '
SELECT @SQL = @SQL + @TableName
select @vary = 'set id' + '=' + '300000'
select @final = @sql + @vary
--select @final
Exec ( @final)
GO

error near id.
id id the name of th ecolumn which already exist in the table.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-09-02 : 14:42:12

whats the output of select @final?

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-02 : 14:49:21

it should update the Test2 table's id column with the value 300000

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-02 : 14:50:03

it should do:
update Test2
set id = '300000'

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-09-02 : 15:09:04

right, what I'm asking is to do this in your query

select @final
--Exec ( @final)
GO

and post the output here

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-02 : 15:12:13

when i so select @final
i get this error;Must declare the scalar variable "@final".

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-02 : 16:37:39

Rohit, any suggestions?

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-09-02 : 17:17:20

what i meant is do this

alter Procedure testdynamic3Update
@TableName VarChar(100)
AS
set nocount on
Declare @SQL VarChar(1000)
Declare @vary varchar(100)
Declare @final varchar(1000)

SELECT @SQL = 'update '
SELECT @SQL = @SQL + @TableName
select @vary = 'set id' + '=' + '300000'
select @final = @sql + @vary
select @final
--Exec ( @final)
GO

run this sp and check the output, it will show you the query you're forming, see if its syntatically correct

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-02 : 20:42:01

Yes, thats what i did previously and still got that error.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources