cursor select and variables

I have problems to place my variable into the select statement.

DECLARE @.DB_NAME varchar(64)
DECLARE MR_ReqPro_DB_cursor CURSOR FOR
select name from dbo.sysdatabases where name like '%MR_req%'
OPEN MR_ReqPro_DB_cursor
FETCH NEXT FROM MR_ReqPro_DB_cursor
INTO @.DB_NAME

WHILE @.@.FETCH_STATUS = 0
BEGIN
print @.DB_NAME; --works fine

Select NAME, FILEDIRECTORY FROM @.DB_NAME.MR_ReqPro.RQDOCUMENTS WHERE (FILEDIRECTORY LIKE '%\\%');

FETCH NEXT FROM MR_ReqPro_DB_cursor INTO @.DB_NAME
END
CLOSE MR_ReqPro_DB_cursor
DEALLOCATE MR_ReqPro_DB_cursor

GO

How could i use a variable like @.DB_Name in my select ?

the object against which you run a query cannot be a variable. You need use dynamic SQL by first constructing your SQL query as a string and then using the EXEC command or the sp_execute_sql system stored procedure. e.g.

declare @.sql VARCHAR(4000)

set @.sql = 'Select NAME, FILEDIRECTORY FROM ' + @.DB_NAME + '.MR_ReqPro.RQDOCUMENTS WHERE (FILEDIRECTORY LIKE ''%\\%'');'

EXEC (@.sql)

Cursor Help

Hi - Trying to work w/cursors but this is the error I receive:
'Cursorfetch: The number of variables declared in the INTO list must match
that of selected columns.' What am I a doing wrong. Below is my code.
Here's the code:
DECLARE @.Address_Line1 VARCHAR (100)
EXECUTE spGet_Statement_Rate '8', '2005'
--spGet_Statement_Rate looks like this:
--DECLARE RATE_CURSOR CURSOR GLOBAL FOR
--SELECT Address_Line1, Rate_ID FROM COMPANY
OPEN RATE_CURSOR
FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
WHILE (@.@.FETCH_STATUS = 0) BEGIN
print @.Address_Line1--FROM RATE_CURSOR
FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
END
--
CLOSE RATE_CURSOR
DEALLOCATE RATE_CURSORif u could post the ddl and sample data we could say whether u could do w/o
a cursor itself..
im not a fan of cursor based coding..
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:A75B44DC-DC6C-425D-B2BE-8B4F333F174C@.microsoft.com...
> Hi - Trying to work w/cursors but this is the error I receive:
> 'Cursorfetch: The number of variables declared in the INTO list must match
> that of selected columns.' What am I a doing wrong. Below is my code.
> Here's the code:
> DECLARE @.Address_Line1 VARCHAR (100)
> EXECUTE spGet_Statement_Rate '8', '2005'
> --spGet_Statement_Rate looks like this:
> --DECLARE RATE_CURSOR CURSOR GLOBAL FOR
> --SELECT Address_Line1, Rate_ID FROM COMPANY
> OPEN RATE_CURSOR
> FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
> WHILE (@.@.FETCH_STATUS = 0) BEGIN
> print @.Address_Line1--FROM RATE_CURSOR
> FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
> END
> --
> CLOSE RATE_CURSOR
> DEALLOCATE RATE_CURSOR
>|||> 'Cursorfetch: The number of variables declared in the INTO list must match
> that of selected columns.' What am I a doing wrong.
The message says it all. Your cursor select statement:
SELECT Address_Line1, Rate_ID FROM COMPANY
has 2 columns specified. However, your FETCH statements:
FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
have only 1 column specified.
You can correct the issue either by omitting Rate_ID from the select list or
adding a variable for Rate_ID to your FETCH statements.
You might also revisit your code to see if you can accomplish the task
without a cursor. Set-based processing is often much more efficient than
cursor processing.
Hope this helps.
Dan Guzman
SQL Server MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:A75B44DC-DC6C-425D-B2BE-8B4F333F174C@.microsoft.com...
> Hi - Trying to work w/cursors but this is the error I receive:
> 'Cursorfetch: The number of variables declared in the INTO list must match
> that of selected columns.' What am I a doing wrong. Below is my code.
> Here's the code:
> DECLARE @.Address_Line1 VARCHAR (100)
> EXECUTE spGet_Statement_Rate '8', '2005'
> --spGet_Statement_Rate looks like this:
> --DECLARE RATE_CURSOR CURSOR GLOBAL FOR
> --SELECT Address_Line1, Rate_ID FROM COMPANY
> OPEN RATE_CURSOR
> FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
> WHILE (@.@.FETCH_STATUS = 0) BEGIN
> print @.Address_Line1--FROM RATE_CURSOR
> FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
> END
> --
> CLOSE RATE_CURSOR
> DEALLOCATE RATE_CURSOR
>|||What do you mean by 'set based processing?' In a nutshell, I need to create
a result set of data. Then for each record returned, I need to execute a
bunch of stored procedures. I understand that cursors aren't the most
efficient, but this will run off hours and only once a month.
"Dan Guzman" wrote:

> The message says it all. Your cursor select statement:
> SELECT Address_Line1, Rate_ID FROM COMPANY
> has 2 columns specified. However, your FETCH statements:
> FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
> have only 1 column specified.
> You can correct the issue either by omitting Rate_ID from the select list
or
> adding a variable for Rate_ID to your FETCH statements.
> You might also revisit your code to see if you can accomplish the task
> without a cursor. Set-based processing is often much more efficient than
> cursor processing.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:A75B44DC-DC6C-425D-B2BE-8B4F333F174C@.microsoft.com...
>
>|||> What do you mean by 'set based processing?'
Process the whole SET of rows at a time rather than one row at a time.
SQL Server is designed and optimized for set-based operations rather
than for cursor operations.
Aside from performance there are plenty of good reasons not to use
cursors. Set-based code is generally much more concise and easier to
develop, test, debug and maintain.
David Portas
SQL Server MVP
--|||> What do you mean by 'set based processing?' In a nutshell, I need to
> create
> a result set of data. Then for each record returned, I need to execute a
> bunch of stored procedures. I understand that cursors aren't the most
> efficient, but this will run off hours and only once a month.
You can't use set-based processing to execute procs for each row returned.
The point is that you can often use a set-based process instead of a looping
construct. Consider basic example:
DECLARE MyCursor CURSOR GLOBAL FOR
SELECT MyData FROM SomeTable
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @.MyData
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM MyCursor INTO @.MyData
INSERT INTO MyTable(MyData)
VALUES(@.MyData)
END
CLOSE MyCursor
DEALLOCATE MyCursor
The above can also be accomplished with the statement below and perform much
better:
INSERT INTO MyTable (MyData)
SELECT MyData FROM SomeTable
It's best to use inline SQL statements when possible. If you need to keep
code in different procs, you can pass data using temp table instead of a
cursor.
Hope this helps.
Dan Guzman
SQL Server MVP|||Okay, here's the cursor i was going to use (this would be in a stored
procedure):
DECLARE RATE_CURSOR CURSOR GLOBAL FOR
SELECT
S.Statement_ID,
S.Rate_ID,
C.Company_Name,
C.Address_Line1,
C.Address_Line2,
C.City,
C.State,
C.Zip,
C.Phone,
C.creditAllow,
R.Rate_Type,
R.Amount_Per_Transaction,
ISNULL (Amount_Per_Transaction_RCK, 0) AS Amount_Per_Transaction_RCK,
ISNULL (Amount_Per_Transaction_ARC, 0) AS Amount_Per_Transaction_ARC,
R.Percentage_Per_Transaction,
ISNULL (Percentage_Per_Transaction_RCK, 0) AS
Percentage_Per_Transaction_RCK,
ISNULL (Percentage_Per_Transaction_ARC, 0) AS
Percentage_Per_Transaction_ARC,
R.Monthly_Fee,
R.Monthly_minimum,
R.Rate_Per_Batch,
R.Discount_VPT,
R.Discount_Monthly_Fee,
R.Discount_RPB,
R.statement_fee,
R.service_fee,
R.ACHgateway_fee,
R.Reject_company_fee,
R.Reject_customer_fee,
R.ACHRefund_Fee,
R.ChargeBack_fee,
R.BadDE_fee,
R.NOC,
R.reserve_percentage,
R.GrossNet,
R.tranFeeCredit,
R.discountFeeCredit,
R.statementFeeCredit,
R.monthlyminCredit,
R.creditOutReturnFee,
RCK,
ARC,
C.RCKRebate,
C.CCDPPD_rebate_amount,
C.ARC_rebate_amount,
C.isRCK_rebate,
C.isARC_rebate,
C.isCCDPPD_rebate,
COMPANY_ID
FROM ST S, RT R, CO C
WHERE S.Rate_ID = R.Rate_ID and
S.Account_ID = C.Company_ID and
S.statement_Month = @.MONTH and
S.statement_Year = @.YEAR AND
C.ACCOUNT_STATUS = 1
Now, for earch record returned above, I will need to execute about 6 stored
procedures, passing in vaious parameters. I'm unfamilar as to how to loop
through a result set (as opposed to using a cursor) to achieve the same, so
if that's a better option, a code example would be appreciated.|||If you have to iterate through a result set for each row returned then
a cursor may well be the best way to do it. What you should generally
try to avoid is iteration in ANY form - other methods of doing so are
just a cursor in disguise. As you have a legacy proc designed to work
only with single rows you will have to decide whether it's worth a
re-write or if you can live with the cursor solution. The better
alternative in most cases is to avoid designing procs that impose any
single row limitations on your SQL code.
David Portas
SQL Server MVP
--|||To add on to David's response, I'd like to emphasize his point that the real
issue is your procs that operate on one record at a time. Ideally, you can
ditch both the cursor and the single-row procs and instead use set-based
processing. For example:
Instead of:
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM RATE_CURSOR INTO <variable list here>
EXEC MyInsertProc <variable list here>
END
Try:
INSERT INTO MyTable(<column list here> )
SELECT ...
I know that rewriting existing code can be painful and might not be worth
the effort in this case if your cursor performance is acceptable. However,
you should at least be familiar with set-based techniques so you can employ
them going forward as appropriate.
Hope this helps.
Dan Guzman
SQL Server MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:D1E5E64E-3AA9-40C6-975D-C7A079AD20DF@.microsoft.com...
> Okay, here's the cursor i was going to use (this would be in a stored
> procedure):
> DECLARE RATE_CURSOR CURSOR GLOBAL FOR
> SELECT
> S.Statement_ID,
> S.Rate_ID,
> C.Company_Name,
> C.Address_Line1,
> C.Address_Line2,
> C.City,
> C.State,
> C.Zip,
> C.Phone,
> C.creditAllow,
> R.Rate_Type,
> R.Amount_Per_Transaction,
> ISNULL (Amount_Per_Transaction_RCK, 0) AS Amount_Per_Transaction_RCK,
> ISNULL (Amount_Per_Transaction_ARC, 0) AS Amount_Per_Transaction_ARC,
> R.Percentage_Per_Transaction,
> ISNULL (Percentage_Per_Transaction_RCK, 0) AS
> Percentage_Per_Transaction_RCK,
> ISNULL (Percentage_Per_Transaction_ARC, 0) AS
> Percentage_Per_Transaction_ARC,
> R.Monthly_Fee,
> R.Monthly_minimum,
> R.Rate_Per_Batch,
> R.Discount_VPT,
> R.Discount_Monthly_Fee,
> R.Discount_RPB,
> R.statement_fee,
> R.service_fee,
> R.ACHgateway_fee,
> R.Reject_company_fee,
> R.Reject_customer_fee,
> R.ACHRefund_Fee,
> R.ChargeBack_fee,
> R.BadDE_fee,
> R.NOC,
> R.reserve_percentage,
> R.GrossNet,
> R.tranFeeCredit,
> R.discountFeeCredit,
> R.statementFeeCredit,
> R.monthlyminCredit,
> R.creditOutReturnFee,
> RCK,
> ARC,
> C.RCKRebate,
> C.CCDPPD_rebate_amount,
> C.ARC_rebate_amount,
> C.isRCK_rebate,
> C.isARC_rebate,
> C.isCCDPPD_rebate,
> COMPANY_ID
> FROM ST S, RT R, CO C
> WHERE S.Rate_ID = R.Rate_ID and
> S.Account_ID = C.Company_ID and
> S.statement_Month = @.MONTH and
> S.statement_Year = @.YEAR AND
> C.ACCOUNT_STATUS = 1
> Now, for earch record returned above, I will need to execute about 6
> stored
> procedures, passing in vaious parameters. I'm unfamilar as to how to loop
> through a result set (as opposed to using a cursor) to achieve the same,
> so
> if that's a better option, a code example would be appreciated.

Currency Variables?

Starting in my control flow, I execute a data flow that populates a recordset via SQL 2005 Stored Proc. One of the columns in source table is a currency type.

Back to the control flow, I have a for each container that includes an execute sql task that updates or inserts records into another table. I get precision or data type issues since I can not assign the package variable to a currency data type. The only way I can get this to work is if I convert the currency column in my data flow to a string and then cast the variable in my update/insert sql task. Any suggestions?

Thanks....Gary

One suggestion would be to map the currency type in the recordset to a variable of type Object in the ForEach container's Variable mappings tab.

Then, when running the Execute SQL task in the ForEach container, use that Object variable in the parameter mapping tab with a DataType of CURRENCY.

No casting is needed.|||That works as described...Thanks!

CTE vs. Table Variable (Paging)

Hello Experts.
I'm trying to find the pros / cons of using CTEs (Common Table Expressions)
vs. Table Variables for 'Paging' through data.
Here is the scanario:
A. Table Variable Example:
Select Query returns 100k posible matching rows, I store the matching key
column along with an identity column in a table varable (i.e. DECLARE @.tmp
TABLE (rowid int identity, userid int) ), then I left join users to userid i
n
@.tmp where @.tmp.rowid between N and N.
B. CTE Example:
WITH CTEUsers AS (select row_number() as rowid, userid ... ) SELECT * from
CTEUsers LEFT JOIN Users WHERE CTEUsers.rowid between N and N
What I noticed so far is:
CTE compares ROW_NUMBER() expression to the values of the BETWEEN expression
and returns only matching rows,
While Table Variables are first saving all userids in memory, then going
back for a table scan for a match for the BETWEEN expression.
I would like to know if anyone here has a definitive answer on why and in
what scanario can CTEs be faster then Table Variables and vice versa.
Thank you in advance for all your help.
- Eyal Zinder.>> I would like to know if anyone here has a definitive answer on why and in
In general, unless you have an environment where every factor that affects
performance is controlled, comparisons are moot. Also, there could be
several other ways one could achieve similar results. There could be even
different approaches for paging rows using a CTE or table variable as well.
So simply asking which construct/structure is "faster" doesn't mean much.
Your posted examples are not very clear either. Please post a sample table
structure, a few sample data as insert statements and legible code snippets
that demostrates your paging attempts.
Anith|||There are far better options than table variables.
http://www.aspfaq.com/2120
I have not played with CTEs in this case.
"Eyal" <ezinder@.yahoo.com> wrote in message
news:0183F6D1-F2B5-411F-954C-65FE3F84AE49@.microsoft.com...
> Hello Experts.
> I'm trying to find the pros / cons of using CTEs (Common Table
> Expressions)
> vs. Table Variables for 'Paging' through data.
> Here is the scanario:
> A. Table Variable Example:
> Select Query returns 100k posible matching rows, I store the matching key
> column along with an identity column in a table varable (i.e. DECLARE @.tmp
> TABLE (rowid int identity, userid int) ), then I left join users to userid
> in
> @.tmp where @.tmp.rowid between N and N.
> B. CTE Example:
> WITH CTEUsers AS (select row_number() as rowid, userid ... ) SELECT *
> from
> CTEUsers LEFT JOIN Users WHERE CTEUsers.rowid between N and N
> What I noticed so far is:
> CTE compares ROW_NUMBER() expression to the values of the BETWEEN
> expression
> and returns only matching rows,
> While Table Variables are first saving all userids in memory, then going
> back for a table scan for a match for the BETWEEN expression.
>
> I would like to know if anyone here has a definitive answer on why and in
> what scanario can CTEs be faster then Table Variables and vice versa.
> Thank you in advance for all your help.
> - Eyal Zinder.
>
>|||".. unless you have an environment where every factor that affects
performance is controlled, comparisons are moot.. "
The database environment I work with currently exceeds 500,000 hits per
second.
I am very much concerned with the smallest difference in performance.
As for my examples, here is a detailed view:
/* TABLE VARIABLE EXAMPLE: */
SET NOCOUNT ON
DECLARE @.tmp TABLE (rowid int identity, userid int)
INSERT INTO @.tmp (userid)
SELECT userid
FROM users (nolock)
WHERE userStatus = @.N1
ORDER BY userLastLoginDate
SELECT u.*
FROM @.tmp t
LEFT JOIN users u (nolock)
ON u.userid = t.userid
WHERE t.rowid BETWEEN @.N2 and @.N3
ORDER BY t.rowid
/* CTE EXAMPLE */
SET NOCOUNT ON
WITH tmp AS
(
SELECT userid, ROW_NUMBER() OVER (ORDER BY u.userLastLoginDate) AS rowid
FROM users (nolock)
WHERE userStatus = @.N1
)
SELECT u.*
FROM tmp t
LEFT JOIN users u (nolock)
ON u.userid = t.userid
WHERE t.rowid BETWEEN @.N2 and @.N3
ORDER BY t.rowid
Again, I am NOT looking for new / better ways to page through data or any
Cursor based paging. I am looking for what pros / cons does CTE offer over
the above solution (variable table example). I am interested to know how CT
E
works and how it differs from the above example.
Thank you again for all your help and prompt reply.
Eyal Zinder.
"Anith Sen" wrote:

> In general, unless you have an environment where every factor that affects
> performance is controlled, comparisons are moot. Also, there could be
> several other ways one could achieve similar results. There could be even
> different approaches for paging rows using a CTE or table variable as well
.
> So simply asking which construct/structure is "faster" doesn't mean much.
> Your posted examples are not very clear either. Please post a sample table
> structure, a few sample data as insert statements and legible code snippet
s
> that demostrates your paging attempts.
> --
> Anith
>
>|||Hi Eyal,
Make sure you keep it server side and only pass back the page the user wants
to the client, that will save significanly on resources especially the
network.
I, personally, don't tend to use CTE for paging because it does the whole
query first and the way I design schema I only need to join for those rows
on my page to get the 'meta' data - basically, I search on surrogate keys
where possible.
Personally I'd be inclided to use the ROWNUMBER() method and pump the output
into a table variable and join that table variable out to the base tables to
get my 'meta' as described above.
The reason is simple, it cuts down on IO.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Eyal" <ezinder@.yahoo.com> wrote in message
news:0183F6D1-F2B5-411F-954C-65FE3F84AE49@.microsoft.com...
> Hello Experts.
> I'm trying to find the pros / cons of using CTEs (Common Table
> Expressions)
> vs. Table Variables for 'Paging' through data.
> Here is the scanario:
> A. Table Variable Example:
> Select Query returns 100k posible matching rows, I store the matching key
> column along with an identity column in a table varable (i.e. DECLARE @.tmp
> TABLE (rowid int identity, userid int) ), then I left join users to userid
> in
> @.tmp where @.tmp.rowid between N and N.
> B. CTE Example:
> WITH CTEUsers AS (select row_number() as rowid, userid ... ) SELECT *
> from
> CTEUsers LEFT JOIN Users WHERE CTEUsers.rowid between N and N
> What I noticed so far is:
> CTE compares ROW_NUMBER() expression to the values of the BETWEEN
> expression
> and returns only matching rows,
> While Table Variables are first saving all userids in memory, then going
> back for a table scan for a match for the BETWEEN expression.
>
> I would like to know if anyone here has a definitive answer on why and in
> what scanario can CTEs be faster then Table Variables and vice versa.
> Thank you in advance for all your help.
> - Eyal Zinder.
>
>|||>> I am looking for what pros / cons does CTE offer over the above solution
There is no empirical evidence that suggests one approach is always better
than the other. In your specific situation, you should evaluate and compare
the query plans and execution times and decide which one performs better.
You can think of CTE as a temporary resultset/virtual table that lasts only
for the duration of the query. The primary benefits of a CTE include
generation of recursive queries, allowance of multiple references in the
same query and overall simplicity ( many complex queries can be simplified
with a well written CTE )
In your example, CTE offers nothing additional to the overall paging
functionality of the code. In other words, you can avoid the CTE altogether
and use a derived table to achieve similar results. In a small sample I
tested, the plans with a derived table and with a CTE were mostly similar
and provided similar performance.
In general, the "paging" methods are the SQL are an extension of a class of
queries called Quota queries in relational literature. Quota queries sort
the rows based on some explicit sequence of values in a column and then
identify the top/bottom subset (quota). You might want to research on that
if you'd like some background on such formulations.
There are several different approaches to this problem and Aaron's website
offers some of the best SQL 2000 methods that are frequently posted in this
newsgroup.
Anith|||Aaron,
Thank you. But the scale of this site does not allow for middle-tier paging
.
"Aaron Bertrand [SQL Server MVP]" wrote:

> There are far better options than table variables.
> http://www.aspfaq.com/2120
> I have not played with CTEs in this case.
>
>
> "Eyal" <ezinder@.yahoo.com> wrote in message
> news:0183F6D1-F2B5-411F-954C-65FE3F84AE49@.microsoft.com...
>
>|||Tony,
Could you provide an example of using Meta Data for such a scanario?
"Tony Rogerson" wrote:

> Hi Eyal,
> Make sure you keep it server side and only pass back the page the user wan
ts
> to the client, that will save significanly on resources especially the
> network.
> I, personally, don't tend to use CTE for paging because it does the whole
> query first and the way I design schema I only need to join for those rows
> on my page to get the 'meta' data - basically, I search on surrogate keys
> where possible.
> Personally I'd be inclided to use the ROWNUMBER() method and pump the outp
ut
> into a table variable and join that table variable out to the base tables
to
> get my 'meta' as described above.
> The reason is simple, it cuts down on IO.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Eyal" <ezinder@.yahoo.com> wrote in message
> news:0183F6D1-F2B5-411F-954C-65FE3F84AE49@.microsoft.com...
>
>|||The code below runs on my site http://sqlserverfaq.com and performs the
listing and searching of Articles.
You will see I use a temporary table with mostly id's in there and then at
the very end join only for those rows I'm throwing back to the client.
Tony.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER proc [dbo].[ukug3_selGetKBArticles]
@.max_pages int output,
@.required_page int = 1,
@.rows_per_page int = 2,
@.FileType varchar(10) = '',
@.idEvents int = 0,
@.SearchKeywords varchar(200) = '',
@.OpType char(1) = 'F',
@.member_group_id int = NULL,
@.is_member_group_restrict char(1) = 'N'
as
begin
set nocount on
create table #results (
idrow int not null identity,
idKBArticle int not null,
Rank int not null,
Characterization varchar(500) not null default( '' )
)
declare @.from_row int
declare @.to_row int
set @.from_row = ( (@.required_page-1) * @.rows_per_page ) + 1
set @.to_row = @.from_row + ( @.rows_per_page - 1 )
declare @.sql nvarchar(4000)
if @.SearchKeywords > ''
begin
DECLARE @.user_search_text varchar(300)
SET @.user_search_text = @.SearchKeywords
SET @.OpType = 'C' -- Done for performance
SET @.SearchKeywords = dbo.fn_search_cleanse( @.SearchKeywords,
'AND' ) -- Gets rid of noise words and adds 'AND'
IF @.SearchKeywords = '' -- Bad search, give it another chance and
use freetext instead.
BEGIN
SET @.OpType = 'F'
SET @.SearchKeywords = @.user_search_text
END
-- If restricting to a member group then add the additional search
clause for that group
IF @.is_member_group_restrict = 'Y'
BEGIN
SELECT @.SearchKeywords = @.SearchKeywords + ' ' + search_clause
FROM member_group
WHERE id = @.member_group_id
END
SET @.sql = 'SELECT TOP 50 *
FROM (
SELECT DISTINCT
kba.idKBArticle,
[Rank],
Characterization
FROM ( SELECT DISTINCT TOP 50 [FileName],
[Rank],
Characterization
FROM OPENQUERY( lsIndexServer,
''SELECT FileName, Rank, Characterization
FROM TORVERSRVH3.SQLServerUG2..SCOPE() WHERE ' + CASE
WHEN @.OpType='C' THEN 'CONTAINS' ELSE 'FREETEXT' END +
'( '' +
@.SearchKeywords + '' )'' )
WHERE LEFT( Characterization, 12 ) <>
''vti_encoding''
) AS qry
INNER JOIN KBArticle kba ON kba.ArticleFileName =
qry.[FileName]'
-- IF @.member_group_id > 0
-- SET @.sql = @.sql + ' WHERE EXISTS ( SELECT * FROM
KBArticle_MemberGroup_Xref x WHERE x.idKBArticle=kba.idKBArticle AND
x.member_group_id=' + CAST( @.member_group_id AS Varchar(10) ) + ' AND
x.is_released=''Y'' )'
SET @.sql = @.sql + '
UNION ALL
SELECT
kba.idKBArticle,
[Rank] = 9999,
''''
FROM KBArticle kba
WHERE kba.ArticleFileName = ''' + @.SearchKeywords +
''' ) AS dt
ORDER BY Rank DESC'
end
else
begin
set @.sql = N'
select idKBArticle, 9999, ''''
from kbarticle k
where 1=1
and is_external_url_link_broken = ''N''
'
-- Search clause
if @.FileType <> ''
set @.sql = @.sql + N' and FileType=@.FileType'
else if @.idEvents = 0
set @.sql = @.sql + N' and FileType<>''wmv''' -- WMV is dealt
with in its own control now so without this we would duplicate content (ok
on the search though!)
if @.idEvents > 0
set @.sql = @.sql + N' and k.idEvents = @.idEvents'
else
set @.sql = @.sql + N' and FileType <> ''ZIP'''
IF @.member_group_id > 0
SET @.sql = @.sql + ' AND EXISTS ( SELECT * FROM
KBArticle_MemberGroup_Xref x WHERE x.idKBArticle=k.idKBArticle AND
x.member_group_id=' + CAST( @.member_group_id AS Varchar(10) ) + ' AND
x.is_released=''Y'' )'
set @.sql = @.sql + N'
order by EntryDate desc'
end
print @.sql
insert #results ( idKBArticle, Rank, Characterization )
exec sp_executesql @.sql,
N'@.FileType varchar(10), @.idEvents int',
@.FileType, @.idEvents
set @.max_pages = ( @.@.rowcount + ( @.rows_per_page - 1 ) ) /
@.rows_per_page
select id,
title,
author_name,
entry_date,
article_summary = dt.article_summary + case when len(
dt.article_summary ) = 100 then '...' else '' end,
FileType,
ArticleFileName,
CompressedSize,
UncompressedSize,
movie_length,
external_url_link
from (
select t.idRow,
id = t.idKBArticle,
title = k.KBArticleTitle,
author_name = case when k.external_url_link = '' or
k.external_url_link = 'HTTP://' then isnull( r.fullname, '' ) else '' end,
entry_date = CONVERT( varchar(20), k.ModifiedDate, 106 ),
article_summary= SUBSTRING( CASE WHEN t.Characterization = ''
THEN k.KBArticleAbstract ELSE t.Characterization END, 1, 100 ),
FileType = ISNULL( FileType, '' ),
ArticleFileName= ISNULL( ArticleFileName, '' ),
CompressedSize,
UncompressedSize,
movie_length,
external_url_link
from #results t
inner join kbarticle k on k.idKBArticle = t.idKBArticle
left outer join registrations r on r.idregistrations =
k.idregistrations
where idrow between @.from_row and @.to_row ) as dt
order by idrow
end
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Eyal" <ezinder@.yahoo.com> wrote in message
news:3960701C-287D-450B-B610-3C36919B9ABF@.microsoft.com...
> Tony,
> Could you provide an example of using Meta Data for such a scanario?
>
> "Tony Rogerson" wrote:
>|||And the ASP.NET (VB.NET) to call the proc...
Dim dbConn As New
SqlClient.SqlConnection(ConfigurationSettings.AppSettings("DBConnection"))
dbConn.Open()
Dim cmdSQL As SqlClient.SqlCommand
Dim daSQL As New SqlDataAdapter
Dim dsSQL As New DataSet
' Get Articles
cmdSQL = New SqlCommand("ukug3_selGetKBArticles", dbConn)
cmdSQL.CommandType = CommandType.StoredProcedure
cmdSQL.Parameters.Add(New SqlParameter("@.required_page",
Me.ResultsPageNumber))
cmdSQL.Parameters.Add(New SqlParameter("@.rows_per_page",
ConfigurationSettings.AppSettings("KBRowsPerPage")))
If Me.Search_FileType <> "" Then cmdSQL.Parameters.Add(New
SqlParameter("@.FileType", Me.Search_FileType))
If Me.Search_EventId > 0 Then cmdSQL.Parameters.Add(New
SqlParameter("@.idEvents", Me.Search_EventId))
If Me.Search_Keywords <> "" Then cmdSQL.Parameters.Add(New
SqlParameter("@.SearchKeywords", Me.Search_Keywords))
If Me.MemberGroupId > 0 Then cmdSQL.Parameters.Add(New
SqlParameter("@.member_group_id", Me.MemberGroupId))
If Me.MemberGroupId > 0 Then cmdSQL.Parameters.Add(New
SqlParameter("@.is_member_group_restrict", IIf(Me.isRestrictToMemberGroup,
"Y", "N")))
Dim sqlParm As SqlParameter
sqlParm = cmdSQL.Parameters.Add(New SqlParameter("@.max_pages",
CInt(0)))
sqlParm.Direction = ParameterDirection.Output
daSQL.SelectCommand = cmdSQL
Dim iPages As Integer
Try
daSQL.Fill(dsSQL)
datlArt.DataSource = dsSQL
datlArt.DataBind()
iPages = cmdSQL.Parameters("@.max_pages").Value
Catch ex As Exception
iPages = 0
End Try
trNoArticles.Visible = (datlArt.Items.Count = 0)
If iPages = 0 Then
tdPage.Visible = False
Else
lbtnPageNext.Visible = (Me.ResultsPageNumber < iPages)
lbtnPagePrev.Visible = Me.ResultsPageNumber > 1
lblPageCur.Text = Me.ResultsPageNumber.ToString
lblPageLast.Text = iPages.ToString
tdPage.Visible = True
End If
dbConn.Close()
dbConn.Dispose()
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Eyal" <ezinder@.yahoo.com> wrote in message
news:3960701C-287D-450B-B610-3C36919B9ABF@.microsoft.com...
> Tony,
> Could you provide an example of using Meta Data for such a scanario?
>
> "Tony Rogerson" wrote:
>