using CONTAINS with sp_executesql ...

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)

using CONTAINS with sp_executesql ...

Author

Topic

von
Starting Member

7 Posts

Posted - 2007-02-23 : 09:03:42

Does anyone know the correct syntax to use the CONTAINS function while implementing sp_executesql?

I've tried a variety of combinations, but I can not figure out how to get it to work. I'm leaning towards maybe this isn't possible.

example......

SELECT @SQL = @SQL + ' AND CONTAINS(Column1, @Column1$)'

SELECT @SQL = @SQL + ' AND CONTAINS(Column1, "@Column1$")'

SELECT @SQL = @SQL + ' AND CONTAINS(Column1, "@Column1$*")'

Thanks in advance

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-23 : 09:08:38

[code]SELECT @SQL = @SQL + ' AND CONTAINS(Column1, ''' + @Column1$ + ''')'[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

von
Starting Member

7 Posts

Posted - 2007-02-23 : 09:15:34

But isn't this using Dynamic SQL which negates the benefit/security of using sp_executesql?

I'm trying to use sp_executesql instead of Dynamic SQL to accomplish this task.

In the end I'm trying to do

Exec sp_executesql @SQL,
N'@Column1$ VARCHAR(2000)',
@Column1$ = @Column1

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-23 : 09:44:43

[code]Declare @Column1$ varchar(2000)

Set @Column1$ ='something'

SELECT @SQL = @SQL + N' AND CONTAINS(Column1, @Column1$)'

Exec sp_executesql @SQL, N'@Column1$ VARCHAR(2000)', @Column1$[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

von
Starting Member

7 Posts

Posted - 2007-02-23 : 10:26:37

That works except now I'm trying to append "*" to the variable.
If I append "*" to the value of the variable

Set @Column1$ ='"something*"'

then it works just fine; however, I'd rather append the value in the CONTAINS function

SELECT @SQL = @SQL + ' AND CONTAINS(Column1, "@Column1$*")'

Any clues on how to do this?

Thanks again for your help :]

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-23 : 10:33:33

What's wrong if you do it in SET statement?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

von
Starting Member

7 Posts

Posted - 2007-02-23 : 11:05:27

Since the value is obtained from user input on an ASP.NET page, I will be passing the value to the stored procedure as a parameter. It is a lot easier for me to maintain and troubleshoot user input if the formatting and/or corrections to that input is in one place and not spread throughout multiple tiers.

So I could append "*" to the parameter value before its passed to the stored procedure or I could do it before I execute sp_executesql. It is just personal preference that I keep it contained in the SQL markup.

thanks again for your time and help :]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources