Please help a noob out in stored procedure!

Hi All,

I'm a newbie in stored procedure programming. I wanted to learn how to perform keyword(s) search with stored procedures. Take the Pubs database for example. How can i create a stored procedure which takes in a string of keywords and return the title_id, title and notes column ? like if i pass in "computer easy" as keywords then the stored procedure should return all the rows with ANY of these keywords in those 3 columns. Can anyone give me some ideas on how to do this? like do i have to use dynamic sql?, any tutorials or sample codes? Thanks in advance!

regards

Download the latest Books On Line for SQL 2000 (or SQL 2005 as appropriate) from Microsoft website and check out for LIKE operator. What you need could be achieved by using LIKE. Try the samples and if you still cant get it working post the code here and we can help you out.
|||Hi ndinakar,
thanks for replying, do you have the links to where i can donwload those books that u mentioned thanks?
regards|||you can search on microsoft website ( or even google ?) . Thats what I would have done too.|||Hi ndinakar,
can you please go to my other posthttp://forums.asp.net/986519/ShowPost.aspx and see if u could answer my questions there as well? because i've posted for quite a while but still have not got a reply yet.

regards|||

hi ndinakar, i'm just trying out a really simple stored procedure but still couldn't get it to work dunno why? can you please help me thanks

USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'test_sp' AND type = 'P')
DROP PROCEDURE test_sp
GO

USE pubs
GO

CREATE PROCEDURE test_sp
AS
create table #temp(string1 varchar(4000), string2 varchar(4000))
insert into #temp (string1, string2) values('string1','string2')

GO
The error message that i got was
Invalid column name 'string1'.

|||

kakusei wrote:

CREATE PROCEDURE test_sp
AS
create table #temp(string1 varchar(4000), string2 varchar(4000))
insert into #temp (string1, string2) values('string1','string2')

GO

Try this

You are creating a temporary table which might not exists when you query it. You table creation is correct. I tested it.
Just add a select command after the insert: select * from #temp
Thanks

|||

If you are not building any kind of index then you can use the like operator as shown below:

select * from <table_name> where title like '%computer easy%' or notes like '%computer easy%'
You can make pass the search string to a sp and then build the dynamic sql inside the sp. finally use the exec command to execute the dynamic sql:
Here is the sp:
create proc sp_TextSearch
@.sTextToSearch varchar(50)
as
declare @.sDynamicSQL varchar(400)
set @.sDynamicSQL = 'select * from <table_name> where title like''%'+@.sTextToSearch +'%'' or notes like ''%'+ @.sTextToSearch +'%'''
--print @.sDynamicSQL
exec(@.sDynamicSQL)

|||It worked for me. Is there anything specific you are trying to do ?|||

Hi ndinakar,

It worked for you ? but how come it doesn't work for me?? Well what i want to do is to create a stored procedure which will take in a keyword string as parameter then break up the string and store the keywords into the #temp table then after that with a while loop to loop through the temp table and dynamically build a sql statement to perform the search (dunno if that is possible yet). So now i'm just testing out how to create the #temp table first, but i couldn't even get that to work :(

i just got this error message

(1 row(s) affected)

Server: Msg 207, Level 16, State 1, Procedure test_sp, Line 5
Invalid column name 'string1'.

|||

Hi musa,
Thanks for your reply and your sample code. But what i want to do ultimately is not just to return the records which only contain the keyword "computer easy" but to return the records which contains the keyword "computer" and "easy" and "computer easy" if i pass in the string "computer easy". So now i'm just thinking of a way to break up the keywords first and then make a loop to loop throught those keywords to build the search query dynamically. I'm think of breaking up the keyword string and storing it into a #temp table first. Dunno if i'm on the right track or not!? so that's why i'm trying to create the #temp table
but couldn't even get that to work

|||

Ok now when i renamed the #temp table to #temp1
but then i put a select * from #temp1 after the insert it didn't show anything
It should show the string1 and string2 right??

|||Sorry all,
forget about my last post about it didn't show anything. i'm stupid forgot to run the procedure!
So now i will have to try and pass in a keyword parameter and break that up and store it into the temp table so that if i pass in "computer easy"
it will store it in the temp table like
keyword id | keyword
--------
1 | computer
2 | easy
any suggestions on how to do that?|||

Here's a function that will take a delimited string and split it and return the values in a table.
--------
CREATE FUNCTION [dbo].[Split] ( @.vcDelimitedString nVarChar(4000),
@.vcDelimiter nVarChar(100) )
/**************************************************************************
DESCRIPTION: Accepts a delimited string and splits it at the specified
delimiter points. Returns the individual items as a table data
type with the ElementID field as the array index and the Element
field as the data
PARAMETERS:
@.vcDelimitedString - The string to be split
@.vcDelimiter - String containing the delimiter where
delimited string should be split
RETURNS:
Table data type containing array of strings that were split with
the delimiters removed from the source string
USAGE:
SELECT ElementID, Element FROM Split('11111,22222,3333', ',') ORDER BY ElementID
AUTHOR: Karen Gayda
DATE: 05/31/2001
MODIFICATION HISTORY:
WHO DATE DESCRIPTION
-- ---- ----------------
***************************************************************************/
RETURNS @.tblArray TABLE
(
ElementID smallint IDENTITY(1,1) not null primary key, --Array index
Element nVarChar(1200) null --Array element contents
)
AS
BEGIN
DECLARE
@.siIndex smallint,
@.siStart smallint,
@.siDelSize smallint
SET @.siDelSize = LEN(@.vcDelimiter)
--loop through source string and add elements to destination table array
WHILE LEN(@.vcDelimitedString) > 0
BEGIN
SET @.siIndex = CHARINDEX(@.vcDelimiter, @.vcDelimitedString)
IF @.siIndex = 0
BEGIN
INSERT INTO @.tblArray (Element) VALUES(@.vcDelimitedString)
BREAK
END
ELSE
BEGIN
INSERT INTO @.tblArray (Element) VALUES(SUBSTRING(@.vcDelimitedString, 1,@.siIndex - 1))
SET @.siStart = @.siIndex + @.siDelSize
SET @.vcDelimitedString = SUBSTRING(@.vcDelimitedString, @.siStart , LEN(@.vcDelimitedString) - @.siStart + 1)
END
END

RETURN
END
-----------------
After you create the function you can use it as :
Declare @.list varchar(200)

set @.list = 'computer,easy'
Select * from YourTable
where keywordcolumn in (Select Element from dbo.Split(@.list, ','))

|||

Hi ndinakar,
can you please see what's wrong with my code below thanks, i can't get it to work. And thanks for your code, but i wanted to do something like the full-text search Contains clause. So i think the code would not work for me

set @.list = 'computer,easy'
Select * from YourTable
where keywordcolumn in (Select Element from dbo.Split(@.list, ','))

--------------------
MY CODE
-----------------------
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'keyword_search' AND type = 'P')
DROP PROCEDURE keyword_search
GO
USE pubs
GO

CREATE PROCEDURE keyword_search
@.keywords varchar(200)

AS

CREATE TABLE #keyword_table
(keyword_index tinyint IDENTITY(1,1) NOT NULL PRIMARY KEY, keyword varchar(200), key_param varchar(5))

DECLARE @.sql nvarchar(1000)
DECLARE @.keyword_count tinyint
DECLARE @.paramlist nvarchar(1000)

declare @.1 varchar(200)
declare @.2 varchar(200)
declare @.3 varchar(200)
declare @.4 varchar(200)
declare @.5 varchar(200)
declare @.6 varchar(200)
declare @.7 varchar(200)
declare @.8 varchar(200)
declare @.9 varchar(200)
declare @.10 varchar(200)

BEGIN
DECLARE @.delimiter char(1)
DECLARE @.temp_keywords varchar(200)
DECLARE @.keyword_length smallint
DECLARE @.id varchar(5)

SET @.delimiter = ','
SET @.temp_keywords = @.keywords
SET @.keyword_count = 0

WHILE CHARINDEX(@.delimiter, @.temp_keywords) > 0
BEGIN
SELECT @.keyword_length = CHARINDEX(@.delimiter, @.temp_keywords)
INSERT INTO #keyword_table (keyword) values (SUBSTRING(@.temp_keywords,0,@.keyword_length))
SELECT @.temp_keywords = SUBSTRING(@.temp_keywords, @.keyword_length + 1, LEN(@.temp_keywords))
select @.keyword_count = @.keyword_count + 1
END

INSERT INTO #keyword_table (keyword) values (SUBSTRING(@.temp_keywords,0,@.keyword_length))
select @.keyword_count = @.keyword_count + 1

select @.sql = 'select title, notes from pubs..titles '

IF @.keyword_count = 3
BEGIN
select @.sql = @.sql + 'where title like ' + quotename('%' + @.1 + '%','''')
+ ' or notes like ' + quotename('%' + @.1 + '%','''') + ' and '
select @.sql = @.sql + 'title like ' + quotename('%' + @.2 + '%','''')
+ ' or notes like ' + quotename('%' + @.2 + '%','''') + ' and '
select @.sql = @.sql + 'title like ' + quotename('%' + @.3 + '%','''')
+ ' or notes like ' + quotename('%' + @.3 + '%','''')
-- select @.sql = @.sql + 'where title like''%@.1%'' or notes like''%@.1%'' and '
-- select @.sql = @.sql + 'title like''%@.2%'' or notes like''%@.2%'' and '
-- select @.sql = @.sql + 'title like''%@.3%'' or notes like''%@.3%'''
END

select @.paramlist ='@.1 varchar(200),
@.2 varchar(200),
@.3 varchar(200)'

DECLARE keyword_cursor CURSOR
FOR SELECT keyword FROM #keyword_table
OPEN keyword_cursor
FETCH NEXT FROM keyword_cursor into @.1
FETCH NEXT FROM keyword_cursor into @.2
FETCH NEXT FROM keyword_cursor into @.3

END

exec sp_executesql @.sql, @.paramlist, @.1, @.2, @.3

GO

EXEC keyword_search 'computer,easy,user'

please help

Hi! I'm new at asp .net programming, and would appreciate a littlehelp with a problem I have. I am trying to create a social network, andthe aim of this specific VB page is to show all of the friends of theuser who is logged in. I would like to do this without using gridviews,so that I have more freedom in the way the results of the select areshown and formatted. I found some code which is perfect for anotherpage where I simply show all the users in the database. However, forthis page where I show the friends of the logged in user, I am havingdifficulties because I need to use a parameter @.Param1 which passes thevalue of the username of who is logged in. Previously I was doing thiswith gridviews, and the passing of the parameter was easy, since on thepagepreinit I could declare Session("username") = User.Identity.Nameand then simply use username inside the select command of thesqldatasource ( <asp:SessionParameter Name="Param1"SessionField="username" />)

However, now without the gridview I don't know how I can continue using this parameter. Any suggestions?
Thanks a lot!

Here is the VB code:

<%@. Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SqlClient" %
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"
<script language="VB" runat="server">
Protected Sub Page_PreInit(ByVal sender As Object, ByVal e As System.EventArgs)
Page.Theme = Profile.MyTheme
Session("username") = User.Identity.Name

End Sub
Sub Page_Load(Sender As Object, E As EventArgs)

Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyCommand As New SqlDataAdapter


' MyCommand.SelectCommand.Parameters.AddWithValue("@.Param1", "")
' not sure about the above line, something is missing


MyConnection = New SqlConnection("DataSource=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;IntegratedSecurity=True;User Instance=True")
MyCommand = NewSqlDataAdapter("select c.UserName from aspnet_Users a inner joinaspnet_friendship b on (a.userId = b.userId or a.userId = b.buddyId)inner join aspnet_Users c on (b.userId = c.userId or b.buddyId =c.userId) where a.UserName = @.Param1 and c.UserName <>@.Param1", MyConnection)
DS = New DataSet()
MyCommand.Fill(DS, "aspnet_Users")

MyDataList.DataSource = DS.Tables("aspnet_Users").DefaultView
MyDataList.DataBind()
End Sub

</script
<body
<br />
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox
<ASP:DataList id="MyDataList" RepeatColumns="1" runat="server"
<ItemTemplate
<table cellpadding=10 style="font: 10pt verdana">
<tr>
<td width=1 bgcolor="BD8672"/
<td valign="top">

</td
<td valign="top"
<b>Name: </b><%#DataBinder.Eval(Container.DataItem, "UserName")%><br>
<b>Photo: </b><%#DataBinder.Eval(Container.DataItem, "UserName")%><br>
<p
<a href='<%# DataBinder.Eval(Container.DataItem, "UserName", "purchase.aspx?titleid={0}") %>' >
<img border="0" src="http://pics.10026.com/?src=/quickstart/aspplus/images/purchase_book.gif" >
</a
</td>
</tr>
</table
</ItemTemplate
</ASP:DataList

</body
</asp:Content>

This should do what you are looking for

Sub Page_Load(Sender As Object, E As EventArgs)

Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyCommand As SqlDataAdapter


MyConnection = New SqlConnection("DataSource=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;IntegratedSecurity=True;User Instance=True")
MyCommand = NewSqlDataAdapter("select c.UserName from aspnet_Users a inner joinaspnet_friendship b on (a.userId = b.userId or a.userId = b.buddyId)inner join aspnet_Users c on (b.userId = c.userId or b.buddyId =c.userId) where a.UserName = @.Param1 and c.UserName <>@.Param1", MyConnection)

MyCommand.SelectCommand.Parameters.AddWithValue("@.Param1", Session("username"))

DS = New DataSet()
MyCommand.Fill(DS, "aspnet_Users")

MyDataList.DataSource = DS.Tables("aspnet_Users").DefaultView
MyDataList.DataBind()
End Sub

|||

Thanks for the help. Now I get an error in the line:

MyCommand.Fill(DS, "aspnet_Users")

The error is: "The parameterized query '(@.Param1 nvarchar(4000))select c.UserName fromaspnet_Users a in' expects the parameter '@.Param1', which was notsupplied."

I am not sure what I should include inside the MyCommand.Fill in order to show the results that I want, considering what I posted before. I would like to show the friends of the logged in user.
I'm sorry I have to ask all this, but I am relatively new at this and would appreciate any help :)

Thanks.

|||

Hi!

I managed to solve the problem :)

The final code is:

<script language="VB" runat="server">
Protected Sub Page_PreInit(ByVal sender As Object, ByVal e As System.EventArgs)
Page.Theme = Profile.MyTheme
Session("username") = User.Identity.Name
End Sub
Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs)

Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyCommand As SqlDataAdapter


MyConnection = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True")

MyCommand = New SqlDataAdapter("select c.UserName from aspnet_Users a inner join aspnet_friendship b on (a.userId = b.userId or a.userId = b.buddyId) inner join aspnet_Users c on (b.userId = c.userId or b.buddyId = c.userId) where a.UserName = @.Param1 and c.UserName <> @.Param1", MyConnection)

MyCommand.SelectCommand.Parameters.AddWithValue("@.Param1", Session("username"))

DS = New DataSet()
MyCommand.Fill(DS, "aspnet_friendship")

MyDataList.DataSource = DS.Tables("aspnet_friendship").DefaultView
MyDataList.DataBind()
End Sub
</script>

Thanks once again.

Placing multiple records on a single line (variables)

Hi, I am new to Crystal Reports, but I know Basic and other programming. I have Crystal Reports XI and am pulling data from our ERP/MRP system, Epicor Vista (Progress DB).

I've been asked to figure out a Crystal Reports for our company (I get thrown into these projects). I know what the report should look like and I know how I would go about some VB code in a macro in Excel if all the data was in worksheets(i.e. like tables).

Below is the data. Any help would be SO appreciated. So far I'm loving Crystal Reports and I can't wait to get some reports our company can start using but I'm stuck on understanding the timing and connection of formulas with the records.

Table1 "JobMtl"
Field "JobComplete":String
Field "JobNum":String
Table2 "JobOper"
Field "OpComplete":Boolean
Field "OprSeq":Number

{JobMtl.JobComplete}
False
True
{JobMtl.JobNum}
2010
2011
{JobOper.Complete}
False
True
{JobOper.OprSeq}
10
20
30
40

Let's say I dragged all 4 fields into a report. It would look like this.

JobNum JobComplete OprSeq OpComplete
2010 False 10 True
2010 False 20 True
2010 False 30 False
2010 False 40 False
2011 False 10 True
2011 False 20 False
2011 False 30 False

I would it to read like this

JobNum JobComplete PrevOp CurrOp NextOp
2010 False 20 30 40
2011 False 10 20 30

**Note: {JobMtl.JobComplete} will be used so I am only reporting jobs that are "not complete". I guess it means nothing to you guys, but I put it here because I was not sure if this will be involved in a formula.

Thanks,
Anthony

My email is ls1z282002_at_yahoo.com (replace "_at_" => "@.") if you would a *.RPT with the data I've shown.Check your e-mail.|||Come on
We all want to see the solution|||Here's what I got from her.

There is a second report that somone from another forum helped me on. I actually need to combine both of these into one because I like the report from SvB_NY because she mad the operations a single String. So I need to do some combining of the two.

I have another question that I'm posting below this.

Anthony|||After showing this to everyone at work, they of course asked for more detail in the report :)

What we have is for each operation {JobOper.OprSeq} it may be an Outsourced (There is a boolean field {JobOper.OprSeq}) that states whether that Operation is outsourced. If it is outsourced they want me to list the PONum & POLn. I know this is simple and I thought so too! I even got it too work! So here's the snag...if the Job does not have any outsourced operations it gets skipped in my report! My reasoning is our ERP software doesn't actually make a index for PO's if that job does not have any PO's against it. Makes sense to me. So how do I handle this?

In the report I attached there should be 3 jobs
2010
2011 => Not shown because no outsourced operations
2012

Am I going to have to create 2 seperate reports, save all the information in arrays. Then match up the arrays with some code and print out a report?

Thanks,
Anthony|||SvB_NY, Thanks for all the help!

I got my report to work and I published it into our ERP software. I appreciate all the help and attached is the final product, if anyone cares to look.

I did never get the PO's to work right because I found out we have multiple PO's to a given operation and that created multiple records for the operation, so my logic for getting the "Prev,Curr,Next" operation didn't work. This is OK though because I was running out of room for the data and I had to have a big comment field. If it was dire for us I'm sure I could get it to work, or I actually I would have posted the question here :)

Anthony