Recording Searches - 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

SQL Server Development (2000)

Recording Searches

Author

Topic

rme8494
Yak Posting Veteran

98 Posts

Posted - 2004-10-15 : 17:46:51

Hi Everyone,
I am currently working on a web application where wher users perform hundreds of searches each day. These searchs are on such thing as customer name, or customer phone number.

What I would like to do is some how "remember" what searches (maybe most recent 20) a user has preformed and display them on the web application.

All of my search queries are in stored procedures.

I may be over simplifing it but is there a way that in each of my search SPs I could run an additional SP that would record the USER ID, the SP that was ran, and the parameters that were sent.

Basically I want some way to record what searches were ran by the user so they can quickly run them again from my web application if needed.

Thanks,
Ryan

Ryan Everhart
SBC

SBC. Going Beyond the Call!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-15 : 18:06:12

well u nned another table with columns
UserID, SpName, Parameters
in which you save the search parameters.

then you just build a dynamic sql to execute sproc with parameters and that's it...

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2004-10-16 : 01:32:23

The Sproc that inserts into the logging table could also drop the oldest if there were more than, say, 20 for that user

kristen

rme8494
Yak Posting Veteran

98 Posts

Posted - 2004-10-17 : 18:51:25

quote:
Originally posted by spirit1

well u nned another table with columns
UserID, SpName, Parameters
in which you save the search parameters.

Spirit this is exactly what I want to do. When I am inserting my values into the other table you mentioned above how do I know what stored proc is being called? Is there a variable available during the execution of the SP that tells me what the name SP running is?

Ryan

Ryan Everhart
SBC

SBC. Going Beyond the Call!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-18 : 03:52:17

emmm.... as i see it:
you do a search in your sproc, so you know what sproc is ran. so you can hardcode it's name into it.
insert into newTable
select SYSTEM_USER, 'yourSprocName', parameters

you do this insert in the sproc.

Go with the flow & have fun! Else fight the flow

rme8494
Yak Posting Veteran

98 Posts

Posted - 2004-10-18 : 10:32:00

Spirit,
Thanks for you help so far. After working on this a bit this is the data I've collected so far.

user procname parameter
sp1944 usp_TE_getSearchMAC '8168471454%','AccountNum',2002044
sp1944 usp_TE_getSearchMAC '81684714%','AccountNum',2002044
sp1944 usp_TE_getSearchMAC '81684713%','AccountNum',2002044

Now my question is how do I write another stored procedure that would run the search again using the information above... Say I need to run

exec usp_TE_getSearchMAC '81684713%','AccountNum',2002044

How do I do that dynamically?

I tried "EXEC sp_executesql" but I don't think I had the parameters set right.

Thanks for your help.

Ryan

Ryan Everhart
SBC

SBC. Going Beyond the Call!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-18 : 10:41:20

will this help?

create procedure spNewSproc
as
declare @sql varchar(8000)
set @sql = 'exec usp_TE_getSearchMAC ''81684713%'',''AccountNum'',2002044'
--or
set @sql = 'exec ' + procname + ' ' + parameter from MyTable where user = 'someUserName'
exec @sql
go

Go with the flow & have fun! Else fight the flow

rme8494
Yak Posting Veteran

98 Posts

Posted - 2004-10-18 : 11:04:44

Spirit,
This is what I have...

-- query to get information i need
OMITTED

-- set up sql variable for use in dynamic exectution
SET @sql = 'EXEC ' + @procname + ' ' + @parameter

-- execute dynamic sql
EXEC @sql

This is what my @sql variable looks like
EXEC usp_TE_getSearchMAC '3143852025%','AccountNum',2002819

However I get the following error.

Server: Msg 2812, Level 16, State 62, Line 21
Could not find stored procedure 'EXEC usp_TE_getSearchMAC '3143852025%','AccountNum',2002819,'re8686','usp_TE_getSearchMAC''.

Any thoughts? I've tried taking the 'exec' off of the @sql parameter and I get the same error.

Thanks for you help on this, I'm sorry my question is dragging out this long.

Ryan

Ryan Everhart
SBC

SBC. Going Beyond the Call!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-18 : 11:15:21

you say your @sql looks like:
EXEC usp_TE_getSearchMAC '3143852025%','AccountNum',2002819

so when you do EXEC @sql i believe it translates to:
EXEC 'EXEC usp_TE_getSearchMAC ...'

so have your @sql look like:
@sql = 'usp_TE_getSearchMAC ''3143852025%'',''AccountNum'',2002819'
and the do:
exec @sql

hope it's clear...

Go with the flow & have fun! Else fight the flow

rme8494
Yak Posting Veteran

98 Posts

Posted - 2004-10-18 : 11:15:31

Spirit,
Disregard my last question. I got it to work with the some parentheses.

EXEC (@sql)

Thanks for all your help!

Ryan

Ryan Everhart
SBC

SBC. Going Beyond the Call!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-18 : 11:16:14

mybe you should post the whole code that you do. it sure would be easier to figure it out...

Go with the flow & have fun! Else fight the flow

rme8494
Yak Posting Veteran

98 Posts

Posted - 2004-10-18 : 11:22:29

Spirit,
Here is the code that works! I really appreciate your help on this. Once I get all the details worked out I believe this will really benefit my users greatly!

Ryan

-- declare local variables to be used in this proc
DECLARE @procname varchar(75),
@parameter varchar(250),
@sql varchar(5000)

-- get the procname and the search paramemeters for use in the dynamic statement below
SELECT @procname = ProcName,
@parameter = Parameter
FROM TESearch
WHERE Search_ID = 429
ORDER BY PostDate desc

-- set up @sql variable for use in dynamic sql below
SET @sql = 'EXEC ' + @procname + ' ' + @parameter

-- execute dynamic sql
EXEC (@sql)

Ryan Everhart
SBC

SBC. Going Beyond the Call!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-18 : 11:28:30

hey great for you.
jsut one question:
what does SCB stand for in your signature?

Go with the flow & have fun! Else fight the flow

rme8494
Yak Posting Veteran

98 Posts

Posted - 2004-10-18 : 11:30:22

SBC is the company I work for.... www.sbc.com.

Ryan Everhart
SBC

SBC. Going Beyond the Call!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-18 : 11:32:36

nice site...

Go with the flow & have fun! Else fight the flow

rme8494
Yak Posting Veteran

98 Posts

Posted - 2004-10-18 : 12:34:29

Spirit,
One more question. Below is teh same code that I posted above with a one addition

',@rcdcnt OUTPUT,@resultset OUTPUT'

I have added the above code to the EXEC line to get 2 fields out of the stored proc in addition too the query results.

When I run the dynamic SQL that you and I created the query results come out just fine, however I do not get any values for the 2 variables, @rcdcnt and @resultset. I know those values are being passed. I just can't seem to grab them correctly. I'll post my code again to get any of your thoughts.

CREATE PROCEDURE [usp_TE_getSavedSearch]

(
@rcdcnt INT OUTPUT,
@resultset TINYINT OUTPUT
)

AS

-- declare local variables to be used in this proc
DECLARE @procname VARCHAR(75),
@parameter VARCHAR(250),
@sql VARCHAR(5000)

-- get the procname and the search paramemeters for use in the dynamic statement below
SELECT @procname = ProcName,
@parameter = Parameter
FROM TESearch
WHERE Search_ID = 429
ORDER BY PostDate desc

-- set up @sql variable for use in dynamic sql below
SET @sql = @sql + 'EXEC ' + @procname + ' ' + @parameter + ',@rcdcnt OUTPUT,@resultset OUTPUT'

-- execute dynamic sql
EXEC (@sql)

Ryan Everhart
SBC

SBC. Going Beyond the Call!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-18 : 13:12:37

they aren't set because they're in another batch.

are you sure your sproc runs ok?
because you have
@sql VARCHAR(5000)
which equates to null
then you do
SET @sql = @sql + 'EXEC ...' which again equates to null because null + string is null

using variables in from different batches can be done with use on global temp tables: ##tempTable

Go with the flow & have fun! Else fight the flow

rme8494
Yak Posting Veteran

98 Posts

Posted - 2004-10-18 : 14:21:56

Yea the proc does run okay because I am seeing the results in my application for the query itself, just not the two variables mentioned above. Also if I skip the dynamic SP we created all together and run

EXEC usp_TE_getSearchMAC '3143852025%','AccountNum',2002819,'re8686','usp_TE_getSearchMAC',@rcdcnt OUTPUT,@resultset OUTPUT

I get the query output as well as the two variables. Can you tell me a little more about the global temp tables?

Ryan

Ryan Everhart
SBC

SBC. Going Beyond the Call!

rme8494
Yak Posting Veteran

98 Posts

Posted - 2004-10-18 : 14:25:24

Spirit, I apologize, no need for you to explain global temp tables themselves.. I just found some information on them in books online.

Ryan Everhart
SBC

SBC. Going Beyond the Call!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-18 : 14:30:12

it's basically a temporary table that can be called from many batches...

Go with the flow & have fun! Else fight the flow

jim_rii
Starting Member

4 Posts

Posted - 2004-11-08 : 15:36:23

I just came across this post and was wondering if anyone has any suggestions on taking these recorded searches and creating a complex search. What I mean by this is giving the user the ability to take these recorded searches and being able to put them together (i.e. displaying a resultset for SearchID 98 and SearchID 99). Any sugestions on how to accomplish this??

jim

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources