Stored Procedure Problem

Report · May 31, 2007

I'm breaking into Stored Procedures.

So far I've gotten a page to display my data...Good so far

Today, I've been trying to pull data onto a page after submitting a form.

This is the SP.
-----------------------
CREATE PROCEDURE searchresults(@searchfor varchar (6000))
AS
SELECT P.ID AS ProductID, P.Name, P.ProdID, P.Price, P.ClearSale,
P.SalePrice, P.ShipCost, G.gid, G.pid AS Gpid, G.ord, D.PID AS DPID,
D.Paragraph
FROM atblProducts P INNER JOIN Groups G ON P.ID = G.pid INNER JOIN PCat
PC ON G.gid = PC.gid LEFT JOIN Description D ON G.gid = D.PID
WHERE D.Paragraph LIKE @searchfor
ORDER BY G.gid, G.ord, P.ID, P.Name
GO
-----------------
When I am inside the Command window in Dreamweaver, and hit the test
button, no results appear. No errors but no results.

Report · Jun 01, 2007

What do you get when you execute the Stored Proc through Query Analyser?

"Lee" <lee_nospam_@artjunky.com> wrote in message
news:f3ngbq$gsr$1@forums.macromedia.com...
> I'm breaking into Stored Procedures.
>
> So far I've gotten a page to display my data...Good so far
>
> Today, I've been trying to pull data onto a page after submitting a form.
>
> This is the SP.
> -----------------------
> CREATE PROCEDURE searchresults(@searchfor varchar (6000))
> AS
> SELECT P.ID AS ProductID, P.Name, P.ProdID, P.Price, P.ClearSale,
> P.SalePrice, P.ShipCost, G.gid, G.pid AS Gpid, G.ord, D.PID AS DPID,
> D.Paragraph
> FROM atblProducts P INNER JOIN Groups G ON P.ID = G.pid INNER JOIN PCat PC
> ON G.gid = PC.gid LEFT JOIN Description D ON G.gid = D.PID
> WHERE D.Paragraph LIKE @searchfor
> ORDER BY G.gid, G.ord, P.ID, P.Name
> GO
> -----------------
> When I am inside the Command window in Dreamweaver, and hit the test
> button, no results appear. No errors but no results.

Report · Jun 01, 2007

Which database are you using? If you are SQL Server then @searchfor needs a
% eg. LIKE '%@searchfor' or LIKE '@searchfor%' to work. You always need the
wildcard character when using LIKE.

"Pat Shaw" <pat@nomail.com> wrote in message
news:f3ohge$nnn$1@forums.macromedia.com...
> What do you get when you execute the Stored Proc through Query Analyser?
>
>
> "Lee" <lee_nospam_@artjunky.com> wrote in message
> news:f3ngbq$gsr$1@forums.macromedia.com...
>> I'm breaking into Stored Procedures.
>>
>> So far I've gotten a page to display my data...Good so far
>>
>> Today, I've been trying to pull data onto a page after submitting a form.
>>
>> This is the SP.
>> -----------------------
>> CREATE PROCEDURE searchresults(@searchfor varchar (6000))
>> AS
>> SELECT P.ID AS ProductID, P.Name, P.ProdID, P.Price, P.ClearSale,
>> P.SalePrice, P.ShipCost, G.gid, G.pid AS Gpid, G.ord, D.PID AS DPID,
>> D.Paragraph
>> FROM atblProducts P INNER JOIN Groups G ON P.ID = G.pid INNER JOIN PCat
>> PC ON G.gid = PC.gid LEFT JOIN Description D ON G.gid = D.PID
>> WHERE D.Paragraph LIKE @searchfor
>> ORDER BY G.gid, G.ord, P.ID, P.Name
>> GO
>> -----------------
>> When I am inside the Command window in Dreamweaver, and hit the test
>> button, no results appear. No errors but no results.
>
>

Report · Jun 01, 2007

"Lee" <lee_nospam_@artjunky.com> wrote in message
news:f3ngbq$gsr$1@forums.macromedia.com...
> When I am inside the Command window in Dreamweaver, and hit the test
> button, no results appear. No errors but no results.

What's your default value?
If it's "1" or something, then you probably won't see anything. 😉

Report · Jun 01, 2007

Lionstone wrote:
> What's your default value?
> If it's "1" or something, then you probably won't see anything. 😉

In addition to the "%" issue, I think the problem is with all the other
inner joins.

I tried doing the following:
WHERE D.paragraph LIKE '%' + @searchfor + '%'

With a simple query, it worked!

Report · Jun 01, 2007

Lionstone,

By the way, have you written that book about Stored Procedures yet? ;)

I remember you recommending material for me to study before but I've
misplaced that. For stored procedures, what book would you recommend?

I think I've said this before here but my problem is understanding how
to apply the stored procedures to the practical needs of developing a
site in Dreamweaver.

Basically, there are probably 20-30 tasks that most
Designers/Developers, in the process of pulling data using Stored
Procedures, need to perform . I've looked for a book but it seems that
it's either always from the point of view of Dreamweaver where they go
through really basic stuff or it's from the point of view of Stored
Procedures specifically. For me, I feel somewhat in a cloud when trying
to work through this stuff. I have never been formally trained in
programming, so it doesn't come naturally to me. Consequently, the
process needs to be clear.

Thanks all for your help.

Report · Jun 01, 2007

The top and bottom is if you have the facility available then use Stored
Procedures wherever you can. SP's are compiled queries so in nearly all
cases they will run much quicker. You have added functionality available to
you such as returning error codes and affected rowcounts etc. Your code will
also be safer and far more controllable. Do some study on Transactions and
the benefits of using them.

Take a look at sites such as SQLTeam.com or SQLServerCentral and you will
find a wealth of information on SP's.

Pat.

"Lee" <lee_nospam_@artjunky.com> wrote in message
news:f3pd8r$s1i$1@forums.macromedia.com...
> Lionstone,
>
> By the way, have you written that book about Stored Procedures yet? ;)
>
> I remember you recommending material for me to study before but I've
> misplaced that. For stored procedures, what book would you recommend?
>
> I think I've said this before here but my problem is understanding how to
> apply the stored procedures to the practical needs of developing a site in
> Dreamweaver.
>
> Basically, there are probably 20-30 tasks that most Designers/Developers,
> in the process of pulling data using Stored Procedures, need to perform .
> I've looked for a book but it seems that it's either always from the point
> of view of Dreamweaver where they go through really basic stuff or it's
> from the point of view of Stored Procedures specifically. For me, I feel
> somewhat in a cloud when trying to work through this stuff. I have never
> been formally trained in programming, so it doesn't come naturally to me.
> Consequently, the process needs to be clear.
>
> Thanks all for your help.

Report · Jun 01, 2007

Thanks Pat

I am curious, you mentioned Transactions and I am wondering if
Transactions is a more specific term for Stored Procedures or is it in
addition to SPs?

Report · Jun 01, 2007

How do you display results in Query Analyzer using my example?
-------------
CREATE PROCEDURE searchresults(@searchfor varchar)
AS
SELECT P.ID AS ProductID, P.Name, P.ProdID, PC.gid AS PCgid, G.gid,
G.pid AS Gpid, G.ord, D.PID AS DPID, D.Paragraph
FROM atblProducts P INNER JOIN Groups G ON P.ID = G.pid INNER JOIN
PCat PC ON G.gid = PC.gid LEFT JOIN Description D ON G.gid = D.PID
WHERE D.Paragraph LIKE '%' + @searchfor + '%'
ORDER BY G.gid, G.ord, P.ID, P.Name
GO
---------------

Report · Jun 01, 2007

Transactions can be implemented into your Stored Procs to ensure consistency
for INSERTs and UPDATEs. Basically, by wrapping your code in a Transaction
ensures that all or none of the changes are made when the code executes. The
basic syntax is along the lines of:

BEGIN TRANSACTION Tr_Update
UPDATE MyTable
SET MyColumn = 2
WHERE MyColumn = 1
IF @@ERROR <> 0 --checks for an error in the update (0 = no error)
BEGIN
ROLLBACK TRANSACTION Tr_Update --rolls back any changes
RETURN --forces unconditional exit
END
ELSE
BEGIN
COMMIT TRANSACTION Tr_Update
RETURN
END

GO

This is just the basics. I would have a good look at SQL Books Online to
begin with and do some Googling for more info. Transactions are very usefull
when implemented correctly.

Pat.

"Lee" <lee_nospam_@artjunky.com> wrote in message
news:f3pjtg$6ii$1@forums.macromedia.com...
> Thanks Pat
>
> I am curious, you mentioned Transactions and I am wondering if
> Transactions is a more specific term for Stored Procedures or is it in
> addition to SPs?

Report · Jun 01, 2007

"Lee" <lee_nospam_@artjunky.com> wrote in message
news:f3pjtg$6ii$1@forums.macromedia.com...
> Thanks Pat
>
> I am curious, you mentioned Transactions and I am wondering if
> Transactions is a more specific term for Stored Procedures or is it in
> addition to SPs?

No; a transaction is a different concept. Each SQL statement in your
procedure is an implicit transaction unless you state otherwise. Explicit
transactions are only necessary when you're performing INSERT and/or UPDATEs
that must either all complete or none complete.

BEGIN TRANSACTION
UPDATE table SET column = value WHERE id = @key
IF @@ERROR <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END

INSERT INTO log(colA, colB) VALUES(@key, @user)
IF @@ERROR <> 0 BEGIN ROLLBACK TRANSACTION RETURN 2 END
COMMIT TRANSACTION

Either both of the above statements will happen, or neither will.
Transactions should be short and sweet because they use some pretty
restrictive locking and can prevent other queries from running until they
are either committed or rolled back.

You probably do not have to worry about using explicit transactions right
now.

Report · Jun 02, 2007

You could take your SELECT:

SELECT P.ID AS ProductID, P.Name, P.ProdID, PC.gid AS PCgid, G.gid,
G.pid AS Gpid, G.ord, D.PID AS DPID, D.Paragraph
FROM atblProducts P INNER JOIN Groups G ON P.ID = G.pid INNER JOIN
PCat PC ON G.gid = PC.gid LEFT JOIN Description D ON G.gid = D.PID
WHERE D.Paragraph LIKE '%' + @searchfor + '%'
ORDER BY G.gid, G.ord, P.ID, P.Name

and replace your parameter with an actual value.

"Lee" <lee_nospam_@artjunky.com> wrote in message
news:f3pl5d$84d$1@forums.macromedia.com...
> How do you display results in Query Analyzer using my example?
> -------------
> CREATE PROCEDURE searchresults(@searchfor varchar)
> AS
> SELECT P.ID AS ProductID, P.Name, P.ProdID, PC.gid AS PCgid, G.gid, G.pid
> AS Gpid, G.ord, D.PID AS DPID, D.Paragraph
> FROM atblProducts P INNER JOIN Groups G ON P.ID = G.pid INNER JOIN PCat
> PC ON G.gid = PC.gid LEFT JOIN Description D ON G.gid = D.PID
> WHERE D.Paragraph LIKE '%' + @searchfor + '%'
> ORDER BY G.gid, G.ord, P.ID, P.Name
> GO
> ---------------