How to execute a dynamic SQL with integer parameter for stored procedure?

I'm having problem on trying to execute a query in stored procedure that has parameters as a integer. The parameter with the integer is in the WHERE clause. If I take out the WHERE clause, it would work. If I take out the parameter and replace it with a value, it would work. I have try using the CONVERT function to convert it to an integer, still no luck.

Error: Unterminated String Constant.

What is the problem?

Set @.strSQL='Select *
From
(
SELECT Row_Number() Over(Order By '+ @.SortExpression+') as Row_Count,Rank() Over (Order By '+ @.SortExpression+') as TableInfo_ColumnSort,dbo.EVENT_LOGS.EVENTLOG_ID, dbo.USERS.USERNAME, dbo.EVENT_LOGS.ITEM_TYPE, dbo.EVENT_LOGS.SCREEN_ID,
dbo.EVENT_LOGS.CHANGE_TYPE, dbo.EVENT_LOGS.IP_ADDRESS, dbo.EVENT_LOGS.CREATE_DATE,dbo.USERS.FIRST_NAME,dbo.USERS.Last_NAME
FROM dbo.EVENT_LOGS INNER JOIN
dbo.USERS ON dbo.EVENT_LOGS.USER_UID = dbo.USERS.USERID
) as TableInfo
Where Row_Count Between '+@.startRowIndex+' and '+ @.maxRowIndex+' ';
Exec(@.strSQL);

Can you please try:

Where Row_Count Between ' +Convert(Varchar,@.startRowIndex)+' and '+Convert(Varchar,@.maxRowIndex)+''

|||

Try

Set @.strSQL='Select *
From
(
SELECT Row_Number() Over(Order By ' + @.SortExpression + ') as Row_Count,Rank() Over (Order By ' + @.SortExpression + ') as TableInfo_ColumnSort,dbo.EVENT_LOGS.EVENTLOG_ID, dbo.USERS.USERNAME, dbo.EVENT_LOGS.ITEM_TYPE, dbo.EVENT_LOGS.SCREEN_ID,
dbo.EVENT_LOGS.CHANGE_TYPE, dbo.EVENT_LOGS.IP_ADDRESS, dbo.EVENT_LOGS.CREATE_DATE,dbo.USERS.FIRST_NAME,dbo.USERS.Last_NAME
FROM dbo.EVENT_LOGS INNER JOIN
dbo.USERS ON dbo.EVENT_LOGS.USER_UID = dbo.USERS.USERID
) as TableInfo
Where Row_Count Between ' +CONVERT(VARCHAR(10),@.startRowIndex) -- the integer needs to be converted to varchar for the + to be a concatenate!
+ ' and ' +CONVERT(VARCHAR(10),@.maxRowIndex) + ' ';
PRINT @.strSQL -- Comment this out once the TSQL is generated correctly
Exec(@.strSQL);

|||

It works! I was converting it to an integer with the convert function. I didn't realized I had to convert it as a string..

How to execute a dynamic sql command in user define func

Hi,
I have 3 string
select @.string = 'SELECT'
select @.string2 ='colname'
select @.string3 ='tablename'
select @.finalstring = @.string + @.string2 + @.string3
inside the user define function, we can not put in
exec command, how we run this dynamic sql command in the
user define function?
thank you.
regards,
florencelee
If you want the output of this dynamic SQL in a table,
you can use "insert into #Temp exec(@.finalstring)" and then process the temp
table. The #Temp table should be defined before.
"florencelee" wrote:

> Hi,
> I have 3 string
> select @.string = 'SELECT'
> select @.string2 ='colname'
> select @.string3 ='tablename'
> select @.finalstring = @.string + @.string2 + @.string3
> inside the user define function, we can not put in
> exec command, how we run this dynamic sql command in the
> user define function?
> thank you.
> regards,
> florencelee
>
|||Hi,
But we can not run the exec inside a user define
function, am i right?
For eg, in the store procedure, we can put exec
(@.finalstring) but how we write in the user define
function?

>--Original Message--
>If you want the output of this dynamic SQL in a table,
>you can use "insert into #Temp exec(@.finalstring)" and
then process the temp[vbcol=seagreen]
>table. The #Temp table should be defined before.
>"florencelee" wrote:
the
>.
>
|||See my other reply...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:38a301c49f9f$406bf590$a301280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> But we can not run the exec inside a user define
> function, am i right?
> For eg, in the store procedure, we can put exec
> (@.finalstring) but how we write in the user define
> function?
>
> then process the temp
> the

How to execute a dynamic sql command in user define func

Hi,
I have 3 string
select @.string = 'SELECT'
select @.string2 ='colname'
select @.string3 ='tablename'
select @.finalstring = @.string + @.string2 + @.string3
inside the user define function, we can not put in
exec command, how we run this dynamic sql command in the
user define function?
thank you.
regards,
florenceleeIf you want the output of this dynamic SQL in a table,
you can use "insert into #Temp exec(@.finalstring)" and then process the temp
table. The #Temp table should be defined before.
"florencelee" wrote:
> Hi,
> I have 3 string
> select @.string = 'SELECT'
> select @.string2 ='colname'
> select @.string3 ='tablename'
> select @.finalstring = @.string + @.string2 + @.string3
> inside the user define function, we can not put in
> exec command, how we run this dynamic sql command in the
> user define function?
> thank you.
> regards,
> florencelee
>|||Hi,
But we can not run the exec inside a user define
function, am i right?
For eg, in the store procedure, we can put exec
(@.finalstring) but how we write in the user define
function?
>--Original Message--
>If you want the output of this dynamic SQL in a table,
>you can use "insert into #Temp exec(@.finalstring)" and
then process the temp
>table. The #Temp table should be defined before.
>"florencelee" wrote:
>> Hi,
>> I have 3 string
>> select @.string = 'SELECT'
>> select @.string2 ='colname'
>> select @.string3 ='tablename'
>> select @.finalstring = @.string + @.string2 + @.string3
>> inside the user define function, we can not put in
>> exec command, how we run this dynamic sql command in
the
>> user define function?
>> thank you.
>> regards,
>> florencelee
>.
>|||See my other reply...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:38a301c49f9f$406bf590$a301280a@.phx.gbl...
> Hi,
> But we can not run the exec inside a user define
> function, am i right?
> For eg, in the store procedure, we can put exec
> (@.finalstring) but how we write in the user define
> function?
>
> >--Original Message--
> >If you want the output of this dynamic SQL in a table,
> >you can use "insert into #Temp exec(@.finalstring)" and
> then process the temp
> >table. The #Temp table should be defined before.
> >
> >"florencelee" wrote:
> >
> >> Hi,
> >> I have 3 string
> >>
> >> select @.string = 'SELECT'
> >> select @.string2 ='colname'
> >> select @.string3 ='tablename'
> >>
> >> select @.finalstring = @.string + @.string2 + @.string3
> >>
> >> inside the user define function, we can not put in
> >> exec command, how we run this dynamic sql command in
> the
> >> user define function?
> >>
> >> thank you.
> >> regards,
> >>
> >> florencelee
> >>
> >.
> >sql

how to execute (using sp_executesql or other...) heterogeneous dyn

use SET ANSI_NULLS ON and SET ANSI_WARNINGS ON in dynamic query
"herve maillarda" wrote:

> Hi,
> I need to run execute an heterogeneous dynamic query (I build the query
> using nvarchar variable).
> Code sample :
> CREATE PROCEDURE COPY_DATA (@.ServerName nvarchar(20), @.DbName
> nvarchar(20),@.TableName nvarchar(20), @.DateDeb DateTime, @.DateFin
> DateTime) AS
> -- Build SQL Query --
> Set @.Ch_SQL = +"INSERT INTO " + @.ServerName + "." + @.DbName + ".dbo." +
> @.TableName + " ( Date_Heure, Valeur )"
> Set @.Ch_SQL =@.Ch_SQL + " SELECT Date_Heure,Valeur FROM " + @.DbName +
> ".dbo." + @.TableName + " WHERE (Date_Heure >= CONVERT(DATETIME, '" +
> CAST(@.DateDeb AS nvarchar(20)) + "', 102) AND Date_Heure <=
> CONVERT(DATETIME, '" + CAST(@.DateFin AS nvarchar(20)) +"', 102))"
> -- Execute query
> SET ANSI_NULLS ON
> SET ANSI_WARNINGS ON
> exec sp_executesql @.Ch_SQL
> GO
>
> I've alway an error coming from SQL server..."You must set ANSI_NULLS
> and ANSI_WARNINGS"...
> If I try the query from the Analyzer it works ...
> How can I do ?
> Thanks for your help...
> Herve.
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>Won't work...
I still have the same error message coming from the Query Analyzer
"[Microsoft][ODBC SQL Server Driver][SQL Server] heterogeneous querys
require ANSI_NULLS options and ANSI_WARNINGS to be defined for the
connection."
I've try to add the options in my query (as you told me) :
Set @.Ch_SQL = "SET ANSI_NULLS ON SET ANSI_WARNINGS ON INSERT INTO " +
@.ServerName ...
Do I miss something ?
thanks for your help.
Herve MAILLARD
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Hi
Do you have triggers that have not had the settings correctly defined?
http://groups-beta.google.com/group...br />
915f7f68
John
Herve MAILLARD wrote:
> Won't work...
> I still have the same error message coming from the Query Analyzer
> "[Microsoft][ODBC SQL Server Driver][SQL Server] heterogeneous querys
> require ANSI_NULLS options and ANSI_WARNINGS to be defined for the
> connection."
> I've try to add the options in my query (as you told me) :
> Set @.Ch_SQL = "SET ANSI_NULLS ON SET ANSI_WARNINGS ON INSERT INTO "
+
> @.ServerName ...
> Do I miss something ?
> thanks for your help.
>
> Herve MAILLARD
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!sql

How to effectively create dynamic queries?

Let's say I have a search screen in my application that allows users to
do various AND OR conditions to about 14 pieces of criteria. That is a
complicated query to build dynamically and will be super slow because
it is dynamic.
Is there an efficient way to do this type of quering?
Thanks,
BrettHi
You can search sp_executesql in BOL.
That sp may reuse Execute plan than EXEC.
Also, You can access .xp_execresultset in Master database stored procedures.
"brett"?? ??? ??:

> Let's say I have a search screen in my application that allows users to
> do various AND OR conditions to about 14 pieces of criteria. That is a
> complicated query to build dynamically and will be super slow because
> it is dynamic.
> Is there an efficient way to do this type of quering?
> Thanks,
> Brett
>|||On 24 Apr 2006 16:02:36 -0700, "brett" <account@.cygen.com> wrote:
in <1145919756.043300.318930@.u72g2000cwu.googlegroups.com>

>Let's say I have a search screen in my application that allows users to
>do various AND OR conditions to about 14 pieces of criteria. That is a
>complicated query to build dynamically and will be super slow because
>it is dynamic.
>Is there an efficient way to do this type of quering?
>Thanks,
>Brett
Of course not. But you can always use a sophisticated CASE function
statement.
This posting is provided "AS IS" with no warranties and no guarantees either
express or implied.
Stefan Berglund|||I think what you want is the ability to load tables with criteria and
not have to use dynamic SQL. Let's say you want to search for job
candidates based on their skills.
skill = Java AND (skill = Perl OR skill = PHP)
becomes the disjunctive canonical form:
(Java AND Perl) OR (Java AND PHP)
which we load into this table:
CREATE TABLE Query
(and_grp INTEGER NOT NULL,
skill CHAR(4) NOT NULL,
PRIMARY KEY (and_grp, skill));
INSERT INTO Query VALUES (1, 'Java');
INSERT INTO Query VALUES (1, 'Perl');
INSERT INTO Query VALUES (2, 'Java');
INSERT INTO Query VALUES (2, 'PHP');
Assume we have a table of job candidates:
CREATE TABLE Candidates
(candidate_name CHAR(15) NOT NULL,
skill CHAR(4) NOT NULL,
PRIMARY KEY (candidate_name, skill));
INSERT INTO Candidates VALUES ('John', 'Java'); --winner
INSERT INTO Candidates VALUES ('John', 'Perl');
INSERT INTO Candidates VALUES ('Mary', 'Java'); --winner
INSERT INTO Candidates VALUES ('Mary', 'PHP');
INSERT INTO Candidates VALUES ('Larry', 'Perl'); --winner
INSERT INTO Candidates VALUES ('Larry', 'PHP');
INSERT INTO Candidates VALUES ('Moe', 'Perl'); --winner
INSERT INTO Candidates VALUES ('Moe', 'PHP');
INSERT INTO Candidates VALUES ('Moe', 'Java');
INSERT INTO Candidates VALUES ('Celko', 'Java'); -- loser
INSERT INTO Candidates VALUES ('Celko', 'Algol');
INSERT INTO Candidates VALUES ('Smith', 'APL'); -- loser
INSERT INTO Candidates VALUES ('Smith', 'Algol');
The query is simple now:
SELECT DISTINCT C1.candidate_name
FROM Candidates AS C1, Query AS Q1
WHERE C1.skill = Q1.skill
GROUP BY Q1.and_grp, C1.candidate_name
HAVING COUNT(C1.skill)
= (SELECT COUNT(*)
FROM Query AS Q2
WHERE Q1.and_grp = Q2.and_grp);
You can retain the COUNT() information to rank candidates. For example
Moe meets both qualifications, while other candidates meet only one of
the two. You can Google "canonical disjunctive form" for more details.
This is a form of relational division.|||Celko wrote:

> I think what you want is the ability to load tables with criteria and
> not have to use dynamic SQL. Let's say you want to search for job
> candidates based on their skills.
Just out of interest, what's if you had multiple types of criteria to
search on. For example, let's say you wanted:
skill = Java AND (skill = Perl OR skill = PHP) AND
birth_date between '1 Jan 1960' and '31 Dec 1970' AND
exists at least 2 entries in CandidateJobHistory where (role =
'Programmer' or role = 'BusinessAnalyst')
Would you have multiple Query tables, or make the Query table handle
multiple types of crtieria?
Kris|||http://www.sommarskog.se/dyn-search.html
"brett" <account@.cygen.com> wrote in message
news:1145919756.043300.318930@.u72g2000cwu.googlegroups.com...
> Let's say I have a search screen in my application that allows users to
> do various AND OR conditions to about 14 pieces of criteria. That is a
> complicated query to build dynamically and will be super slow because
> it is dynamic.
> Is there an efficient way to do this type of quering?
> Thanks,
> Brett
>|||I think that one has got to take the biscuit for an over complicated kludge
at keeping the solution within standard sql.
Not only will this not scale you have still to address optional parameters
at which point you will realise 14 tables all with left outer joins just
will not scale.
Dynamic SQL or a ridiculous number of IF ELSE statements are required,
unfortunetly you'll never use IF ELSE because its procedural so that puts
you into the COALESCE camp which will give a very general and very poor
query plan that will not scale.
Your answer only serves to demonstrate your lack of real programming
exposure, get out of the class room, these solutions will not work in the
real world.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1145932636.689321.112960@.j33g2000cwa.googlegroups.com...
>I think what you want is the ability to load tables with criteria and
> not have to use dynamic SQL. Let's say you want to search for job
> candidates based on their skills.
> skill = Java AND (skill = Perl OR skill = PHP)
> becomes the disjunctive canonical form:
> (Java AND Perl) OR (Java AND PHP)
> which we load into this table:
> CREATE TABLE Query
> (and_grp INTEGER NOT NULL,
> skill CHAR(4) NOT NULL,
> PRIMARY KEY (and_grp, skill));
> INSERT INTO Query VALUES (1, 'Java');
> INSERT INTO Query VALUES (1, 'Perl');
> INSERT INTO Query VALUES (2, 'Java');
> INSERT INTO Query VALUES (2, 'PHP');
> Assume we have a table of job candidates:
> CREATE TABLE Candidates
> (candidate_name CHAR(15) NOT NULL,
> skill CHAR(4) NOT NULL,
> PRIMARY KEY (candidate_name, skill));
> INSERT INTO Candidates VALUES ('John', 'Java'); --winner
> INSERT INTO Candidates VALUES ('John', 'Perl');
> INSERT INTO Candidates VALUES ('Mary', 'Java'); --winner
> INSERT INTO Candidates VALUES ('Mary', 'PHP');
> INSERT INTO Candidates VALUES ('Larry', 'Perl'); --winner
> INSERT INTO Candidates VALUES ('Larry', 'PHP');
> INSERT INTO Candidates VALUES ('Moe', 'Perl'); --winner
> INSERT INTO Candidates VALUES ('Moe', 'PHP');
> INSERT INTO Candidates VALUES ('Moe', 'Java');
> INSERT INTO Candidates VALUES ('Celko', 'Java'); -- loser
> INSERT INTO Candidates VALUES ('Celko', 'Algol');
> INSERT INTO Candidates VALUES ('Smith', 'APL'); -- loser
> INSERT INTO Candidates VALUES ('Smith', 'Algol');
> The query is simple now:
> SELECT DISTINCT C1.candidate_name
> FROM Candidates AS C1, Query AS Q1
> WHERE C1.skill = Q1.skill
> GROUP BY Q1.and_grp, C1.candidate_name
> HAVING COUNT(C1.skill)
> = (SELECT COUNT(*)
> FROM Query AS Q2
> WHERE Q1.and_grp = Q2.and_grp);
> You can retain the COUNT() information to rank candidates. For example
> Moe meets both qualifications, while other candidates meet only one of
> the two. You can Google "canonical disjunctive form" for more details.
> This is a form of relational division.
>|||Tony Rogerson wrote:

> Your answer only serves to demonstrate your lack of real programming
> exposure, get out of the class room, these solutions will not work in the
> real world.
Actually we've used a similar approach for one of our products. It was
for a set of marketing plans for which the user selected a set of
customer demographic criteria (birth date, ethnicity, location, etc
etc). The reason we didn't just generate dynamic SQL was because these
marketing plans had to be saved and reusable, therefore we had to store
the actual criteria in a set of tables, similar to the Query table Joe
used except we stored all criteria types in that table.
Then when actually generating the customer list, we used a query
similar to Joe's and it works reasonably well. The main divantage is
that the optimizer can't use the best indexes, but since the act of
generating a customer list isn't time criticial it didn't matter.
I doubt this solution is suitable for the OP's problem (dynamic SQL is
probably better IMO), but in some cases it is useful and not just a
theoretical solution.
Kris|||Hi Kris,
I think you made the point that you get a general plan.
Also, consider 10 concurrent users doing this, suddenly you have shifted
your select centric access model to a transaction one so you'll face
increased write IO in the logs and disk, also there may well be a lot of
blocking problems which will cause significant scalability problems which
I'm suprised you have not noticed yet - perhaps there aren't many concurrent
users on your particular app using this functionality at the same time?
Dynamic SQL is the only way to go, I've done something similar in terms of
spec myself for a knowledge management system we developed, basically I
build the SQL dynamically and store the text, the query can be very easily
resubmitted without any work apart from a select from the table holding the
query, no constants used - all parameterised too and you get the best plan
for the job instead of a general one.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
<kriskirk@.hotmail.com> wrote in message
news:1145947385.605291.160600@.y43g2000cwc.googlegroups.com...
> Tony Rogerson wrote:
>
> Actually we've used a similar approach for one of our products. It was
> for a set of marketing plans for which the user selected a set of
> customer demographic criteria (birth date, ethnicity, location, etc
> etc). The reason we didn't just generate dynamic SQL was because these
> marketing plans had to be saved and reusable, therefore we had to store
> the actual criteria in a set of tables, similar to the Query table Joe
> used except we stored all criteria types in that table.
> Then when actually generating the customer list, we used a query
> similar to Joe's and it works reasonably well. The main divantage is
> that the optimizer can't use the best indexes, but since the act of
> generating a customer list isn't time criticial it didn't matter.
> I doubt this solution is suitable for the OP's problem (dynamic SQL is
> probably better IMO), but in some cases it is useful and not just a
> theoretical solution.
> Kris
>|||Hi Brett,
SQL Server does a lot of auto-parameterisation so query plans will be
cached, obviously you might end up with 'x' different query plans but thats
good because they will be highly efficient based solely on what you are
trying to do instead of kludging the query to get round the lack of dynamics
within the SELECT statement.
The string concatenation won't even be measurable.
Build your query and parameterise it, Aaron links to a really good article
on dynamic SQL by Erland (http://www.sommarskog.se/dyn-search.html)
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"brett" <account@.cygen.com> wrote in message
news:1145919756.043300.318930@.u72g2000cwu.googlegroups.com...
> Let's say I have a search screen in my application that allows users to
> do various AND OR conditions to about 14 pieces of criteria. That is a
> complicated query to build dynamically and will be super slow because
> it is dynamic.
> Is there an efficient way to do this type of quering?
> Thanks,
> Brett
>

How to edit textbox or a cell of table?

Hi everybody!
I've created a report in reporting services and in it i need to have a
dynamic textbox.
Let me describe more.I have two reports ,in first report i have
manegers' names .After that users see first report and manegers'
informations for example their names and saleries , they want to
select manegers on base for example their saleries ,i mean the user
must see informations then decide and select manegers who their
employees will be shown in second report ,in this case i need a
textbox that the user can write Yes or No in it,or checkbox for
multiple select .
After that users select their manegers they can see manegers'
employees in second report.
I need a textbox that has two values (Yes and No) and these values
must be shown in textbox in preview mode to select by users or user
can select Yes or No or checkbox.
Can anybody help me?
ShimaSwhy not just create one report that groups on the managers names and when
the name is clicked the details are shown. This functionality is built in
and easy to do.
another option is to add link on the managers name and when clicked open the
second report by passing the manager's name as a parameter. you won't be
able to do multiple choices though.
<shima.seifolahi@.gmail.com> wrote in message
news:1193114815.518228.133770@.t8g2000prg.googlegroups.com...
> Hi everybody!
> I've created a report in reporting services and in it i need to have a
> dynamic textbox.
> Let me describe more.I have two reports ,in first report i have
> manegers' names .After that users see first report and manegers'
> informations for example their names and saleries , they want to
> select manegers on base for example their saleries ,i mean the user
> must see informations then decide and select manegers who their
> employees will be shown in second report ,in this case i need a
> textbox that the user can write Yes or No in it,or checkbox for
> multiple select .
> After that users select their manegers they can see manegers'
> employees in second report.
> I need a textbox that has two values (Yes and No) and these values
> must be shown in textbox in preview mode to select by users or user
> can select Yes or No or checkbox.
> Can anybody help me?
> ShimaS
>|||I did it ,but i need to select more than one prson in first report,
for one person i don't have any problem
Thanks

How to dynamic specify the colums in Full-Text Search?

My problem is simple: i want to dynamic specified the columns in the
ContainsTable, this is possible? Please see the example.
<Code>
Declare @.Test1 int
Declare @.Test2 int
Declare @.Query varchar(50)
Declare @.Temp varcahr(50)
--Test
Set @.Test1=1
Set @.Test1=0
Set @.Query='something'
--Add the columns to put in containstable
IF (@.Test1=1)
Begin
Set @.Temp='ID'
End
IF (@.Test2=1)
Begin
Set @.Temp= @.Temp + ',Name'
End
SELECT *
FROM
<table>
INNER JOIN
CONTAINSTABLE (<table>, @.Temp, @.Query) AS KEY_TBL
ON <table>.ID = KEY_TBL.[KEY]
</Code>
Thanks
you can only search on one column at a time, or if you are using contains or containstable, on all columns.
To get what you want you would have to do something like this
select * from tablename where contains(col1, 'test') or contains(col2, 'test') or contains(col3, 'test')
|||Renato,
Hilary is correct, you can only specify one column per CONTAINSTABLE
predicate or use the astrick "*" to indicate that you want to specify all
FT-enabled columns in the referenced table. However, this will be supported
in SQL Server 2005 (codename Yukon) with multi-column syntax, for example:
SELECT PrimaryKey, Col1, Col2
FROM FT_Table
WHERE CONTAINS( ( Col1, Col2 ) , ' "john" AND "kane" ')
Until the Yukon public beta becomes available, you will need to the astrick
to specificy all columns or use Hilary's example below. You might also find
the following stored proc useful as well:
use pubs
go
-- DROP PROCEDURE sp_FTSearchPubsInfo
go
CREATE PROCEDURE sp_FTSearchPubsInfo ( @.vcSearchText varchar(7800))
AS
declare @.s as varchar (8000)
set @.s='select pub_id, pr_info from pub_info where
contains(pr_info,'+''''+@.vcSearchText+''''+')'
exec (@.s)
go
EXEC sp_FTSearchPubsInfo '(("book" or "books") and ("publish" or
"publisher"))'
go
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:61A1A4F7-557D-46CD-ADB4-9D36052924C1@.microsoft.com...
> you can only search on one column at a time, or if you are using contains
or containstable, on all columns.
> To get what you want you would have to do something like this
> select * from tablename where contains(col1, 'test') or contains(col2,
'test') or contains(col3, 'test')
|||Thanks for the help.
I will try the Hillary example...
See you later

How to dynamic specify the colums in Full-Text Search?

My problem is simple: i want to dynamic specified the columns in the ContainsTable, this is possible? Please see the example.

Declare @.Test1 int
Declare @.Test2 int
Declare @.Query varchar(50)
Declare @.Temp varcahr(50)
--Test
Set @.Test1=1
Set @.Test1=0
Set @.Query='something'
--Add the column to put in containstable
IF (@.Test1=1)
Begin
Set @.Temp='ID'
End
IF (@.Test2=1)
Begin
Set @.Temp= @.Temp + ',Name'
End
SELECT *
 FROM
 <table>
 INNER JOIN
 CONTAINSTABLE (<table>,@.Temp, @.Query) AS KEY_TBL
 ON <table>.ID = KEY_TBL.[KEY]

ThanksOnly in Sql Server 2005 (Yukon) that is possible... :/

How to do a SELECT on SP Result

Hi there,

Problem:

Having an old webapplication with dynamic build sql queries. Now the database changed completly because of new db model. Queries are getting much more complex now, so i put them into stored procedures.

Now i want to work with the data in an application for sorting an filtering. All the 'WHERE' conditions are build dynamic within the code. For not modifying the whole application i would like to do something like:

SELECT * FROM (EXECUTE sp_someprocedure) WHERE X = 1

Is there a way to realize this ?

Thx for helping

Hi,

Just doing queries won′t work, although there is a option to do that (via OPENQUERY) its not the prefered way of calling stored procedures. It would be better to use a stored procedure calling the procedure and inserting the data in a temp table andf later select on this table using your condition.

Something like:

INSERT INTO #TempTable
EXEC sp_Yourprocedure

SELECT * FROM #TempTable
where youcondition = true

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

You may want to consider changing sp_comeprocedure to a table valued function (tvf_somefunction), assuming you always want to call it inside a select statement. Then your code ends up looking like:

select * from tvf_somefunction() where x = 1

|||You get an invalid object error when you try to insert into on a Temp Table. This does not work. Any other suggestions?

How to do a SELECT on SP Result

Hi there,

Problem:

Having an old webapplication with dynamic build sql queries. Now the database changed completly because of new db model. Queries are getting much more complex now, so i put them into stored procedures.

Now i want to work with the data in an application for sorting an filtering. All the 'WHERE' conditions are build dynamic within the code. For not modifying the whole application i would like to do something like:

SELECT * FROM (EXECUTE sp_someprocedure) WHERE X = 1

Is there a way to realize this ?

Thx for helping

Hi,

Just doing queries won′t work, although there is a option to do that (via OPENQUERY) its not the prefered way of calling stored procedures. It would be better to use a stored procedure calling the procedure and inserting the data in a temp table andf later select on this table using your condition.

Something like:

INSERT INTO #TempTable
EXEC sp_Yourprocedure

SELECT * FROM #TempTable
where youcondition = true

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

You may want to consider changing sp_comeprocedure to a table valued function (tvf_somefunction), assuming you always want to call it inside a select statement. Then your code ends up looking like:

select * from tvf_somefunction() where x = 1

|||You get an invalid object error when you try to insert into on a Temp Table. This does not work. Any other suggestions?