Syntax help with exec command - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Syntax help with exec command

Author

Topic

Samadhi69
Starting Member

20 Posts

Posted - 2009-02-06 : 17:29:51

This exec statement works as intended:

exec ('
insert into Table1Temp 
select * from Table1 
where ID_Table1 > 5555
')

I want to replace Table1Temp, Table1, and ID_Table1 with char(50) params and 5555 with a bigint param. I'm having trouble with the > and with the right amount of '

For example, (ignoring the other two that work for troubleshooting)

exec ('
insert into Table1Temp
select * from Table1 
where ' + @field + ' > ' + @id + '''
')

Gives me: Incorrect syntax near '>'
Unclosed quotation mark before the character string ''

I'm pretty sure it has to do with casting, but I'm having trouble overcoming it.

----------------

If con is the opposite of pro, what's the opposite of progress?

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2009-02-06 : 17:43:30

declare @SQL Nvarchar(2000)
declare @field NVarchar(50)
declare @id bigint
select @field = 'ID_Table1', @id = 5555

set @SQL = 'insert into Table1Temp select * from Table1 where ' + cast(@field as varchar) + ' > ' + Cast(@id as varchar)
print @SQL
exec sp_executesql @SQL

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Samadhi69
Starting Member

20 Posts

Posted - 2009-02-06 : 19:42:01

It works on smaller strings it seems but not longer ones. At a certain point @SQL is just two spaces (and maybe a CRLF I haven't checked).

If con is the opposite of pro, what's the opposite of progress?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-06 : 21:14:29

Use nvarchar(4000) for @sql, nvarchar(256) for @field and then:

set @SQL = 'insert into Table1Temp select * from Table1 where ' + @field + ' > ' + cast(@id as varchar(10))

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-07 : 08:55:17

quote:
Originally posted by Samadhi69

This exec statement works as intended:

exec ('
insert into Table1Temp
select * from Table1
where ID_Table1 > 5555
')

I want to replace Table1Temp, Table1, and ID_Table1 with char(50) params and 5555 with a bigint param. I'm having trouble with the > and with the right amount of '

For example, (ignoring the other two that work for troubleshooting)
exec ('
insert into Table1Temp
select * from Table1
where ' + @field + ' > ' + cast(@id as varchar(15)) ~~+ '''~~
')

Gives me: Incorrect syntax near '>'
Unclosed quotation mark before the character string ''

I'm pretty sure it has to do with casting, but I'm having trouble overcoming it.

----------------

If con is the opposite of pro, what's the opposite of progress?

wont this be enough?

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2009-02-07 : 10:35:26

There you go making life easy again.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-07 : 10:51:38

quote:
Originally posted by visakh16

quote:
Originally posted by Samadhi69

This exec statement works as intended:

exec ('
insert into Table1Temp
select * from Table1
where ID_Table1 > 5555
')

I want to replace Table1Temp, Table1, and ID_Table1 with char(50) params and 5555 with a bigint param. I'm having trouble with the > and with the right amount of '

For example, (ignoring the other two that work for troubleshooting)
exec ('
insert into Table1Temp
select * from Table1
where ' + @field + ' > ' + cast(@id as varchar(15)) ~~+ '''~~
')

Gives me: Incorrect syntax near '>'
Unclosed quotation mark before the character string ''

I'm pretty sure it has to do with casting, but I'm having trouble overcoming it.

----------------

If con is the opposite of pro, what's the opposite of progress?

wont this be enough?

Isn't that what I posted?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-07 : 10:54:25

Ah..my bad...i didnt notice your suggestion...sorry

Samadhi69
Starting Member

20 Posts

Posted - 2009-02-08 : 00:19:15

Thanks, I'll let you know on Monday how it goes.

If con is the opposite of pro, what's the opposite of progress?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-08 : 00:55:14

And what about SQL Injection?
Quote your variables like this...

set @SQL = 'insert into Table1Temp select * from Table1 where ' + quotename(@field) + ' > ' + quotename(@id, '''')

exec(@sql)

E 12°55'05.63"
N 56°04'39.26"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-08 : 10:46:13

You'd handle the SQL injection part in the application layer.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-08 : 12:46:45

Poor Valter Borges, if sql injection handling is not made 100% at client side.