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 - Is this even possible?

I am executing multiple stored procedures in a stored procedure, pulling back a table of results, which is activated by a crystal report. One of the stored procedures activates a DTS Package. My problem is that the activation of the dts package returns output results, which in turn throws off my crystal report. Is there a way to still run this stored procedure as is but without returning the DTS output results??

I am using Sql 2000 and Crystal 8.5.

I would really appreciate any suggestions.

ThanksYou can create the temp table and insert the execution of the dts package into the temp table. This will "capture" the results and effectively mask them from being seen as output. You will also probably want to use SET NOCOUNT ON in your procedure.

Please help - Is this even possible?

I am executing multiple stored procedures in a stored procedure, pulling back a table of results, which is activated by a crystal report. One of the stored procedures activates a DTS Package. My problem is that the activation of the dts package returns output results, which in turn throws off my crystal report. Is there a way to still run this stored procedure as is but without returning the DTS output results??

I am using Sql 2000 and Crystal 8.5.

I would really appreciate any suggestions.

ThanksYou'd be more likely to get an answer to this question in the MS-SQL forum (http://www.dbforums.com/f7).

-PatPsql

Please help

Hello everybody,
I have stored procedure that calls number of other stored procedures. If I
run stored procedure from query analyzer it never fails.
However it fails from VB. And to be more specific, it does not fail, it
stops at certain point and exits (there is no error msg in sql Profiler)
I have stored procedure SP1 and it calls SP2, SP3, SP4, SP5, SP6, SP7
It goes to SP6 up to the curtain point and without error leaves and it does
not go at all to SP7 and leaves sp SP1 immediately.
It leaves SP6 every time in the same place, however if I add waitfor
(delay) somewhere on the top, it would leave stored procedure earlier
VB6.0 use ODBC connection to SQL server(MDAC2.5), I do not have adodb
command or connection timeout, both values =0
The same stored procedure never fails (with the same data) when running from
query analyzer
I can assure you that it is not permission problem, it is not query problem,
because with added delay, it would leave stored procedure earlier
I mean without delay it fails after 8th query, with delay
it can leave stored procedure after 3rd query (for instance).
.. It reference mdac2.5, also I tried to
reference 2.7 with service pack 1 without any luck
This stored procedure has few delete statements. If I would replace delete
with truncate -> stored procedure would not fail(100% successful). But I
can't do it, because the client who is running sp is not admin
Also the stored procedure sp6, that fails is using tempdb. If I increase
tempdb log file size, the failure rate decreases to around 50%.
I have solved the problem temporary by splitting sp1 into 2 stored procedues
Now sp1 calls sp2,sp3,sp4,sp5
sp1_newOne calls sp6, sp7. Just to note that SP1 and sp1_NewOne are called
from vb.
Please help
Any suggestions would be appreciated
thanks,
Diana M
Hi Diana,
I do not think that someone here can give a specific solution to that problem.
i think you should open profiler and performance monitor and try to find the
problem.
may be you have a dead lock there? or an open transaction and this is
explains the wait for delay helps it. i would have focuced on the filters of
text data at your trace adding event of sp:start and at the perfmon add
counters of cpu and try to see if there is a jump there when you call your sp
from vb. also take a look at sysprocesses able and try and see if you have an
spid in waiting for a while.
hope it helps,
tomer
"Diana M" wrote:

> Hello everybody,
> I have stored procedure that calls number of other stored procedures. If I
> run stored procedure from query analyzer it never fails.
> However it fails from VB. And to be more specific, it does not fail, it
> stops at certain point and exits (there is no error msg in sql Profiler)
> I have stored procedure SP1 and it calls SP2, SP3, SP4, SP5, SP6, SP7
> It goes to SP6 up to the curtain point and without error leaves and it does
> not go at all to SP7 and leaves sp SP1 immediately.
> It leaves SP6 every time in the same place, however if I add waitfor
> (delay) somewhere on the top, it would leave stored procedure earlier
> VB6.0 use ODBC connection to SQL server(MDAC2.5), I do not have adodb
> command or connection timeout, both values =0
> The same stored procedure never fails (with the same data) when running from
> query analyzer
> I can assure you that it is not permission problem, it is not query problem,
> because with added delay, it would leave stored procedure earlier
> I mean without delay it fails after 8th query, with delay
> it can leave stored procedure after 3rd query (for instance).
> . It reference mdac2.5, also I tried to
> reference 2.7 with service pack 1 without any luck
>
> This stored procedure has few delete statements. If I would replace delete
> with truncate -> stored procedure would not fail(100% successful). But I
> can't do it, because the client who is running sp is not admin
> Also the stored procedure sp6, that fails is using tempdb. If I increase
> tempdb log file size, the failure rate decreases to around 50%.
> I have solved the problem temporary by splitting sp1 into 2 stored procedues
> Now sp1 calls sp2,sp3,sp4,sp5
> sp1_newOne calls sp6, sp7. Just to note that SP1 and sp1_NewOne are called
> from vb.
> Please help
> Any suggestions would be appreciated
> thanks,
> Diana M
>

Please help

Hello everybody,
I have stored procedure that calls number of other stored procedures. If I
run stored procedure from query analyzer it never fails.
However it fails from VB. And to be more specific, it does not fail, it
stops at certain point and exits (there is no error msg in sql Profiler)
I have stored procedure SP1 and it calls SP2, SP3, SP4, SP5, SP6, SP7
It goes to SP6 up to the curtain point and without error leaves and it does
not go at all to SP7 and leaves sp SP1 immediately.
It leaves SP6 every time in the same place, however if I add waitfor
(delay) somewhere on the top, it would leave stored procedure earlier
VB6.0 use ODBC connection to SQL server(MDAC2.5), I do not have adodb
command or connection timeout, both values =0
The same stored procedure never fails (with the same data) when running from
query analyzer
I can assure you that it is not permission problem, it is not query problem,
because with added delay, it would leave stored procedure earlier
I mean without delay it fails after 8th query, with delay
it can leave stored procedure after 3rd query (for instance).
. It reference mdac2.5, also I tried to
reference 2.7 with service pack 1 without any luck
This stored procedure has few delete statements. If I would replace delete
with truncate -> stored procedure would not fail(100% successful). But I
can't do it, because the client who is running sp is not admin
Also the stored procedure sp6, that fails is using tempdb. If I increase
tempdb log file size, the failure rate decreases to around 50%.
I have solved the problem temporary by splitting sp1 into 2 stored procedues
Now sp1 calls sp2,sp3,sp4,sp5
sp1_newOne calls sp6, sp7. Just to note that SP1 and sp1_NewOne are called
from vb.
Please help
Any suggestions would be appreciated
thanks,
Diana MHi Diana,
I do not think that someone here can give a specific solution to that proble
m.
i think you should open profiler and performance monitor and try to find the
problem.
may be you have a dead lock there? or an open transaction and this is
explains the wait for delay helps it. i would have focuced on the filters of
text data at your trace adding event of sp:start and at the perfmon add
counters of cpu and try to see if there is a jump there when you call your s
p
from vb. also take a look at sysprocesses able and try and see if you have a
n
spid in waiting for a while.
hope it helps,
tomer
"Diana M" wrote:

> Hello everybody,
> I have stored procedure that calls number of other stored procedures. If
I
> run stored procedure from query analyzer it never fails.
> However it fails from VB. And to be more specific, it does not fail, it
> stops at certain point and exits (there is no error msg in sql Profiler)
> I have stored procedure SP1 and it calls SP2, SP3, SP4, SP5, SP6, SP7
> It goes to SP6 up to the curtain point and without error leaves and it doe
s
> not go at all to SP7 and leaves sp SP1 immediately.
> It leaves SP6 every time in the same place, however if I add waitfor
> (delay) somewhere on the top, it would leave stored procedure earlier
> VB6.0 use ODBC connection to SQL server(MDAC2.5), I do not have adodb
> command or connection timeout, both values =0
> The same stored procedure never fails (with the same data) when running fr
om
> query analyzer
> I can assure you that it is not permission problem, it is not query proble
m,
> because with added delay, it would leave stored procedure earlier
> I mean without delay it fails after 8th query, with delay
> it can leave stored procedure after 3rd query (for instance).
> . It reference mdac2.5, also I tried to
> reference 2.7 with service pack 1 without any luck
>
> This stored procedure has few delete statements. If I would replace delete
> with truncate -> stored procedure would not fail(100% successful). But I
> can't do it, because the client who is running sp is not admin
> Also the stored procedure sp6, that fails is using tempdb. If I increase
> tempdb log file size, the failure rate decreases to around 50%.
> I have solved the problem temporary by splitting sp1 into 2 stored proce
dues
> Now sp1 calls sp2,sp3,sp4,sp5
> sp1_newOne calls sp6, sp7. Just to note that SP1 and sp1_NewOne are calle
d
> from vb.
> Please help
> Any suggestions would be appreciated
> thanks,
> Diana M
>

Please help

Hello everybody,
I have stored procedure that calls number of other stored procedures. If I
run stored procedure from query analyzer it never fails.
However it fails from VB. And to be more specific, it does not fail, it
stops at certain point and exits (there is no error msg in sql Profiler)
I have stored procedure SP1 and it calls SP2, SP3, SP4, SP5, SP6, SP7
It goes to SP6 up to the curtain point and without error leaves and it does
not go at all to SP7 and leaves sp SP1 immediately.
It leaves SP6 every time in the same place, however if I add waitfor
(delay) somewhere on the top, it would leave stored procedure earlier
VB6.0 use ODBC connection to SQL server(MDAC2.5), I do not have adodb
command or connection timeout, both values =0
The same stored procedure never fails (with the same data) when running from
query analyzer
I can assure you that it is not permission problem, it is not query problem,
because with added delay, it would leave stored procedure earlier
I mean without delay it fails after 8th query, with delay
it can leave stored procedure after 3rd query (for instance).
. It reference mdac2.5, also I tried to
reference 2.7 with service pack 1 without any luck
This stored procedure has few delete statements. If I would replace delete
with truncate -> stored procedure would not fail(100% successful). But I
can't do it, because the client who is running sp is not admin
Also the stored procedure sp6, that fails is using tempdb. If I increase
tempdb log file size, the failure rate decreases to around 50%.
I have solved the problem temporary by splitting sp1 into 2 stored procedues
Now sp1 calls sp2,sp3,sp4,sp5
sp1_newOne calls sp6, sp7. Just to note that SP1 and sp1_NewOne are called
from vb.
Please help
Any suggestions would be appreciated
thanks,
Diana MHi Diana,
I do not think that someone here can give a specific solution to that problem.
i think you should open profiler and performance monitor and try to find the
problem.
may be you have a dead lock there? or an open transaction and this is
explains the wait for delay helps it. i would have focuced on the filters of
text data at your trace adding event of sp:start and at the perfmon add
counters of cpu and try to see if there is a jump there when you call your sp
from vb. also take a look at sysprocesses able and try and see if you have an
spid in waiting for a while.
hope it helps,
tomer
"Diana M" wrote:
> Hello everybody,
> I have stored procedure that calls number of other stored procedures. If I
> run stored procedure from query analyzer it never fails.
> However it fails from VB. And to be more specific, it does not fail, it
> stops at certain point and exits (there is no error msg in sql Profiler)
> I have stored procedure SP1 and it calls SP2, SP3, SP4, SP5, SP6, SP7
> It goes to SP6 up to the curtain point and without error leaves and it does
> not go at all to SP7 and leaves sp SP1 immediately.
> It leaves SP6 every time in the same place, however if I add waitfor
> (delay) somewhere on the top, it would leave stored procedure earlier
> VB6.0 use ODBC connection to SQL server(MDAC2.5), I do not have adodb
> command or connection timeout, both values =0
> The same stored procedure never fails (with the same data) when running from
> query analyzer
> I can assure you that it is not permission problem, it is not query problem,
> because with added delay, it would leave stored procedure earlier
> I mean without delay it fails after 8th query, with delay
> it can leave stored procedure after 3rd query (for instance).
> . It reference mdac2.5, also I tried to
> reference 2.7 with service pack 1 without any luck
>
> This stored procedure has few delete statements. If I would replace delete
> with truncate -> stored procedure would not fail(100% successful). But I
> can't do it, because the client who is running sp is not admin
> Also the stored procedure sp6, that fails is using tempdb. If I increase
> tempdb log file size, the failure rate decreases to around 50%.
> I have solved the problem temporary by splitting sp1 into 2 stored procedues
> Now sp1 calls sp2,sp3,sp4,sp5
> sp1_newOne calls sp6, sp7. Just to note that SP1 and sp1_NewOne are called
> from vb.
> Please help
> Any suggestions would be appreciated
> thanks,
> Diana M
>sql

Please All Help me ( challenge )

I'm in need to write stored procedures which insert id for employee in a table in a database but i want to make stored procedures to prevent the user from entering id with value 0 i want to catch this from the stored procedures as kindly message and send this message to windows application

Use Raiserror with severy >=16:

create procedure InsertEmployee

@.id int

as

if @.id = 0

begin

RAISERROR ('Employee ID can''t be 0',16,1);

end

else

begin

--INSERT here

print 'ID > 0 '

end

|||But the important section is how to catch this error message from windows application and show it when the user try to enter value for id employee less than 1|||

That depends on the technology that you are using to connect to the database server and also the programming language that you have used to develop the client application. You're unlikely to get the answer to your question in a T-SQL forum.

See if you can find a more relevant forum in this list:

http://forums.microsoft.com/MSDN/default.aspx?siteid=1

Just to steer you in the right direction, and now that you've been advised on how to raise errors in your stored procedures, then this forum might be the next port of call:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=45&SiteID=1

Chris

|||If you want to use SqlCommand explicitly:

SqlCommand cmd = new SqlCommand("<name of your SP>",conn);
cmd.Parameters.Add("@.ID",SqlDBType.Int);
cmd.Parameters["@.ID"].Value = <value from user input>;
try
{
cmd.ExecuteNoQuery();
}
catch(SqlException e)
{
//Catch error here
//For example
MessageBox.Show(e.Errors[0].Message);
}

If you want to use SqlDataAdapter/TableAdapter

SqlDataAdapter da = <some code to obtain>;
try
{
da.Update(ds);//ds - you dataset or datatable

}
catch(SqlException e)

{

//Catch error here

//For example

MessageBox.Show(e.Errors[0].Message);

}|||The whole point is that if your are giving id as parameter why not check the value inside your application before sending it to the SP.
Now other option is to write a trigger inside which u rollback ur transaction if id is 0. This will prevent 0's not only from appl but also from backend

PL/SQL versus stored procedures

I want to know what's the differences between PL/SQL and stored
procedures,
the followings are my analysis, please comment and advise.
1) PL/SQL is Oracle specific, stored procedures are supported in
Oracle, MS-SQL Server, or other databases.
2) PL/SQL has 2 types: procedures and functions
3) PL/SQL procedure = stored procedure '
4) Oracle stored procedure and MS-SQL stored procedure have
different syntax. I think they are slightly different, I could
find the syntax for Oracle stored procedure, but not MS-SQL stored
procedure. In other words, can we put MS-SQL stored procedure
and put in Oracle without any changes?
Please advise. thanks!!On 13 Oct 2005 22:15:44 -0700, apngss@.yahoo.com wrote:

>I want to know what's the differences between PL/SQL and stored
>procedures,
>the followings are my analysis, please comment and advise.
>1) PL/SQL is Oracle specific, stored procedures are supported in
>Oracle, MS-SQL Server, or other databases.
>2) PL/SQL has 2 types: procedures and functions
>3) PL/SQL procedure = stored procedure '
>4) Oracle stored procedure and MS-SQL stored procedure have
>different syntax. I think they are slightly different, I could
>find the syntax for Oracle stored procedure, but not MS-SQL stored
>procedure. In other words, can we put MS-SQL stored procedure
>and put in Oracle without any changes?
>Please advise. thanks!!
1 PL/SQL is Oracle's language to implement stored procedures, T-SQL is
MS language to implement stored procedures. T-SQL is only supported in
MS and maybe Sybase. 'Stored procedure' is a *concept*, not a
*language*
2 Like any proper procedural language
3 PL/SQL can be used for anonymous blocks : ie code which is not
stored, and for procedures/functions/packages which are stored
4 the syntax for T-SQL and Pl/SQL is akin, but dissimilar.
T-SQL procedures won't work in Oracle and vice versa, moreover Oracle
works completely different, and too many people mistakenly think
Oracle is sqlserver sold by a different vendor. It is not, it works
completely different.
Sybrand Bakker, Senior Oracle DBA|||PL/SQL is to Oracle as T-SQL is to SQL Server. Both have their programming
constructs and data structures. You can "implement" stored procedures using
PL/SQL much like you can do the same with T-SQL.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
<apngss@.yahoo.com> wrote in message
news:1129266944.512419.283400@.o13g2000cwo.googlegroups.com...
>I want to know what's the differences between PL/SQL and stored
> procedures,
> the followings are my analysis, please comment and advise.
> 1) PL/SQL is Oracle specific, stored procedures are supported in
> Oracle, MS-SQL Server, or other databases.
> 2) PL/SQL has 2 types: procedures and functions
> 3) PL/SQL procedure = stored procedure '
> 4) Oracle stored procedure and MS-SQL stored procedure have
> different syntax. I think they are slightly different, I could
> find the syntax for Oracle stored procedure, but not MS-SQL stored
> procedure. In other words, can we put MS-SQL stored procedure
> and put in Oracle without any changes?
> Please advise. thanks!!
>|||apngss@.yahoo.com wrote:
> I want to know what's the differences between PL/SQL and stored
> procedures,
> the followings are my analysis, please comment and advise.
> 1) PL/SQL is Oracle specific, stored procedures are supported in
> Oracle, MS-SQL Server, or other databases.
> 2) PL/SQL has 2 types: procedures and functions
> 3) PL/SQL procedure = stored procedure '
> 4) Oracle stored procedure and MS-SQL stored procedure have
> different syntax. I think they are slightly different, I could
> find the syntax for Oracle stored procedure, but not MS-SQL stored
> procedure. In other words, can we put MS-SQL stored procedure
> and put in Oracle without any changes?
PL/SQL = name of a Oracle's embedded programming language (based on
another programming language called ADA). PL/SQL is also very similar
in syntax and structure to Pascal.
PL/SQL programs can be :
- procedures
- functions
- packages (program units containing both procedures and packages,
similar to a Pascal unit)
- anonymous code block
As procedures, functions and packages are stored (in source code and
pre-compiled format) inside the database, these are often collectively
refered to as "stored procedures".
Anonymous blocks are PL/SQL code blocks constructed by the client and
transmitted to Oracle. Oracle parses and compiles these and then
execute them. These are obviously not stored in the database and
therefore not considers as "stored procedures".
Other databases implements their own embedded programming languages.
SQL-Server's is called Transact-SQL (or simply T-SQL).
Why not simple use SQL? SQL is not Turing Complete and despite its
power and flexibility, lacks at doing complex structural processing.
Thus most databases implement an embedded programming language, which
they tightly integrate with their SQL engine - providing seamless SQL
access from within this embedded language. Note that not all databases
have embedded programming language - some only recently started doing
this (like mySQL).
Embedded programming language look similar. So yes, there are
similarities between T-SQL and PL/SQL. But these are very superficial.
PL/SQL is a formal declarative procedural language with
object-orientated features. It is capable of doing what you can do in
other languages, like Java, C/C++, Delphi/Pascal, Visual Basic, etc.
(besides, Oracle and SQL-Sever work conceptually very different and
what is "good practise" in one database, is a performance killer in the
other)
There are limitations in PL/SQL however. It is a server-side language.
It thus lacks I/O devices such as screen, keyboard, mouse, printer and
so on. It is not an interactive language (it cannot interact with the
end-user). As it is embedded in a database it cannot natively access
the operating system kernel API. Etc.
However, these "limitations" are common to embedded languages. The
advantage is that PL/SQL can be run on any Oracle database on any
platform. Thus you can develop a PL/SQL application on Windows in
Oracle and have a customer use your application in their Oracle
database on an IBM mainframe. Similar to Java, it is fully portable
across Oracle platforms. Unlike Java, it is not an issue of
write-once-and-debug-everywhere as the PL/SQL engine is consistant.
Oracle's Replicator is written in PL/SQL. Oracle Applications (a
commercial product suite) consists of over 1 million lines of PL/SQL
source code. I myself, has written numerous server-side systems
(including a custom replicator and web applicatiom tiers) in PL/SQL. It
is a very capable language - and, as a statement of fact, the *best*
programming language to use when dealing with Oracle data.
Billy|||Good Explanation Billy
Madhivanan|||That was a very good explanation. Thank you.
Ivy

PL/SQL to TSQL

I need a tool to convert (at least 50%) my source code of 200+ stored procedures from PL/SQL to TSQL.
Could any one help me?
ThanksHi Carlos,

Check out our website http://www.dbbest.com

PL/SQL effeciency question

Right now I have a few PL/SQL procedures that calculate count(*) and sum(*) values for different reports. So far it's a daily operation, with around 80000 records per day (seems to be increasing by 10000 records every week though)

it's not a complex procedure, just a lot of number crunching for each columns. Right now the basic logic is to use a separate SQL statement for each column(with different conditions, of course), and repeat it until all the reports are filled in.

So far it's taking about 20 secs for going thru every 10000 records. Add the time it takes to load the records in (about 25-30 secs per 80000 records), and it becomes about 3 minutes of operation each day.

I have been thinking about how I can improve the performance of the procedure. So far I am thinking about whether the performance would improve if I can change the logic by declaring a cursor for the records, and just go thru it one time, then put the value into different variables.

Any suggestion is appreciated

MarkOriginally posted by mchih
Right now I have a few PL/SQL procedures that calculate count(*) and sum(*) values for different reports. So far it's a daily operation, with around 80000 records per day (seems to be increasing by 10000 records every week though)

it's not a complex procedure, just a lot of number crunching for each columns. Right now the basic logic is to use a separate SQL statement for each column(with different conditions, of course), and repeat it until all the reports are filled in.

So far it's taking about 20 secs for going thru every 10000 records. Add the time it takes to load the records in (about 25-30 secs per 80000 records), and it becomes about 3 minutes of operation each day.

I have been thinking about how I can improve the performance of the procedure. So far I am thinking about whether the performance would improve if I can change the logic by declaring a cursor for the records, and just go thru it one time, then put the value into different variables.

Any suggestion is appreciated

Mark
Generally it is preferable to avoid procedural logic if you want best performance. Maybe you could combine all (or many of) your counts and sums into a single query using DECODE (or CASE) to filter the records:

SELECT SUM( DECODE( col1, 'x', 1, 0 )) as COUNT_WHERE_COL1_IS X
, COUNT(*) TOTAL_COUNT,
, SUM( DECODE( col3, 123, col4, 0 )) as SUM_COL4_WHERE_COL3_IS_123
...
FROM ...|||it might be difficult to put many of the query together, since each of them has a different where clause.

eg:

table temp(
card_type
card_amount
message_type
message_response
.
.
.
)

a sample query would be
select count(*), sum(card_amount) from temp
where card_type = x
and message_type = y
and message_response = z;

now that i think about it, it might be possible to use a GROUP BY to get all the values with similar WHERE clause, but I don't know how to store the values individually so i can access them later (eg. put it into different table)

TIA

Mark