dynamic sql - 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 2005 Forums

Transact-SQL (2005)

dynamic sql

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-07-28 : 09:48:15

Hi,
There is an error in the following dynamic sql which is:
'Incorrect syntax near ')'

set @sql = @sql + convert(varchar(50), + 'f.field1' + ) + ' like ' + convert(varchar(50),@field1) + '%'' AND '

How can it be corrected please?
Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-28 : 10:06:33

So you don't want the solution using numeric values?
field1 >= @field1 and field1 < @field1 +0.1

And why do you need dynamic sql?

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-28 : 10:08:59

We can't tell, since this is a continuation from your previous question and we see only a fraction of your code.

I can spot at least four ways to correct the query, depending on your goal.

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-07-28 : 10:09:08

Hi, this is basically a different query for a diferent business needs...
dynamic query due to business requirement.
Thanks

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-07-28 : 10:19:09

quote:
Originally posted by Peso

We can't tell, since this is a continuation from your previous question and we see only a fraction of your code.

I can spot at least four ways to correct the query, depending on your goal.

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

Hi,
This is basically a totally different question to the previous one and that is why i have created a new post for it here...
I am trying to build a dynamic sql query which is finr until i get to when converting a field to varchar in sql. for example
I can do this:
set @sql = @sql + 's.field1 like ''%' + convert(varchar(50), @field1) + '%'' AND '

but not sure how to correct this line:
set @sql = @sql --+ convert(varchar(50), + 'f.field1' + ) + ' like ' + convert(varchar(50),@field1) + '%'' AND '

If you still think this is related to the previous post then I will place it into the other post.

Thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-28 : 10:20:15

Let's do #1 of 4.

set @sql = @sql + 'f.field1 like ' + convert(varchar(50), @field1) + '%'' AND '

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-07-28 : 10:24:48

quote:
Originally posted by Peso

Let's do #1 of 4.

set @sql = @sql + 'f.field1 like ' + convert(varchar(50), @field1) + '%'' AND '

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

This gives the error 'can not convert...'
I should convert 'f.field1 to varchar in this dynamic sql but do not know how to
Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-28 : 10:35:14

quote:
Originally posted by Peso

Let's do #1 of 4.

set @sql = @sql + 'convert(varchar(50),f.field1) like ' + convert(varchar(50), @field1) + '%'' AND '

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

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-28 : 10:42:17

Glauben Sie nicht, diese Ratespiele sind Spaß?

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-28 : 10:51:11

Jeder Spaß hat auch einmal ein Ende!

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks