Hi,
I'm a PHP Programmer but I need help making a storedProc.
basically, if I have orders and want to output the intials of the order taker I need help on that storedProc.
So I got the IDClientID
select idRessCreatedBy from TBL_quotation where idClient = IDclient
now i got the IDresscreatedby and I need to do a
substring(firstname,1,1) + substring(lastname,1,1) from
tbl_ress where IDresscreatedby = IDresscreatedby
Can anyone help me with this storedProc?
I would very much apreciate it
Creek:
I am guessing at a good bit of this, but maybe you have in mind something like this?
|||set nocount on
--
-- I am assuming that the "quotation" table is the orders table; if
-- not how is the order related to the quotation?
--
-- The request is for the "initials of the order"; therefore, it seems
-- that the "order" must be part of the information returned (Unless
-- the "order" is an input parameter!)
--
-- This is really guessing at too much.
--
declare @.quotation table
( IDquote integer,
IDClient integer,
idRessCreatedBy integer
)
insert into @.quotation values (1, 1, 1)
insert into @.quotation values (2, 1, 1)
insert into @.quotation values (3, 2, 2)declare @.ress table
( idRessCreatedBy integer,
firstName varchar (20),
lastName varchar (20)
)
insert into @.ress values (1, 'Bob', 'Einstein')
insert into @.ress values (2, 'Dave', 'Osborne')select a.idQuote as IDOrder,
left (b.firstName, 1) + left (b.lastName, 1) as orderInitials
from @.quotation a
inner join @.ress b
on a.idRessCreatedBy = b.idRessCreatedBy-- Sample Output:
-- IDOrder orderInitials
-- -- -
-- 1 BE
-- 2 BE
-- 3 DO--
-- If the query is to be based say on a given client ID you can add
-- this in
--
declare @.inputClientID integer
set @.inputClientID = 2select a.idQuote as IDOrder,
left (b.firstName, 1) + left (b.lastName, 1) as orderInitials
from @.quotation a
inner join @.ress b
on a.idRessCreatedBy = b.idRessCreatedBy
and a.IDClient = @.inputClientID
-- Sample Output:-- IDOrder orderInitials
-- -- -
-- 3 DO
Hi,
your storedProc is a little confusing for me, as i'm just a PHP programmer.
there's no order as such, it's all quotations but the DB uses the words interchangeably. I know I didn't make the DB, lol.
what's the difference between the two you gave and what's nocount on and where does that go?
I have a clientID array that will be sent from my PHP web app to a storedProc
|||CREATE PROCEDURE Initials_sp
-- Add the parameters for the stored procedure here
@.IDclient int ,
@.Initials NCHAR(2) OUTPUT
AS
SET @.Initials=(SELECT (substring(firstname,1,1) + substring(lastname,1,1)) as initials from tbl_ress where IDresscreatedby = (select idRessCreatedBy from TBL_quotation where idClient = @.IDclient))
Return @.Initials
|||what's@.Initials NCHAR(2) OUTPUT?
Thanks alot man
I very much apreciate your help
|||The difference is that one of the two assumes that you are giving a "client ID" as an argument to the procedure. The other assumes that you want a list of many orders with the initials of the order taker. The "set nocount on" is so that I do not get "count" informational messages returned when I run the script. It is DEFINITELY not a necessary part of the script -- strictly optional!
I used "Client ID" as the input to this; it is also possible that you might want the "Order ID" as an argument to this procedure instead of the "Client ID" Limno's version of a version of the stored procedure is a good example.
The potential weakness of his version of the stored procedure is that it uses an "OUTPUT PARAMETER". This has both advantages and disadvantages. In the sample data I put together I had two orders for customer ID #1. If it is possible that a customer has more than one pending orders the output parameter cannot reflect this because it is only single valued. But there are times in which a programmer prefers to receive an output parameter rather than a query result. What I returned were query results.
|||Dave
The code "@.INITIALS NCHAR(@.) OUTPUT
is the output parameter.
|||what do I put where it says add parameters here?|||NCHAR(2) to hold your returned initial for the output parameter. I don't know whether this link will help you to use it in code.
http://aspnet.4guysfromrolla.com/demos/printPage.aspx?path=/articles/062905-1.aspx
|||Got it!
and what do i put for add parameters here?
I'm just a php programmer
|||Creek:
You probably don't need to add anything. If (1) the @.IDClient parm is what you want to use for filtering and (2) you expect your output to be single valued and (3) receiving the output as an output parameter is acceptable, the procedure ought to be fine as is. In this case, just create the procedure and test it. It is reading data and should not cause any harm as written.
|||
Dave
Knob:
I was visualizing this stored procedure this morning while preparing for work and I realized that the line
Return @.Initials
was going to cause an execution error. So I compiled the stored procedure and ran it and received this error:
Server: Msg 245, Level 16, State 1, Procedure Initials_sp, Line 13
Syntax error converting the nvarchar value 'DO' to a column of data type int.
Therefore, you need to change this line
Return @.Initials
into this:
Return
And this particular execution error will be eliminated. In addition, I am still not confident that the results of the query will be singular. When I ran the stored procedure like this in query analyzer:
declare @.return_initials nchar(2)exec initials_sp 1, @.initials = @.return_initials output
select @.return_initials as [@.return_initials]
I received this output:
Server: Msg 512, Level 16, State 1, Procedure Initials_sp, Line 11
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The 'Initials_sp' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.
@.return_initials
-
NULL(1 row(s) affected)
HOWEVER! This error will not be received if the assumptions of my test data were incorrect. To verify whether or not we need to correct the procedure to fix this potential problem, try running this query:
if
( select count(*)
from ( select idClient,
count(*) as clientOrderCount
from tbl_quotation
group by idClient
having count(*) > 1
) x
) > 0
select 'Multiple Results ' as Results
else
select 'All Singular Results' as Results
If you receive output that looks like this:
Results
--
All Singular Results
then no additional modifications to the procedure are necessary; however, if you receive results that look like this:
Results
--
Multiple Results
then an additional modification is necessary. If you get "Multiple Results" you will need to change the procedure to something like this:
alter PROCEDURE dbo.Initials_sp
-- Add the parameters for the stored procedure here
@.IDclient int
AS
select a.idQuote,
left (b.firstName, 1) + left (b.lastName, 1) as orderInitials
from tbl_quotation a
inner join tbl_ress b
on a.idRessCreatedBy = b.idRessCreatedBy
and a.IDClient = @.IDClientReturn
I ran the query and received multiple results in query analyzer.
I apreciate your help btw
if I run the alter storedproc, what do I put where it says add parameters here?
I'm just a php programmer.
Thasnk
|||Sorry. That line was from template. I changed the end of this sp for the Return part.
You can call this stored procedure from your code(php page) with the value for your @.IDclient parameter.
--start of this sp
CREATE PROCEDURE Initials_sp
@.IDclient int ,
@.Initials NCHAR(2) OUTPUT
AS
SET @.Initials=(SELECT (substring(firstname,1,1) + substring(lastname,1,1)) as initials from tbl_ress where IDresscreatedby = (select idRessCreatedBy from TBL_quotation where idClient = @.IDclient))
Return
--end of this sp
|||
Ok, so I tried the SP and I get this error:
Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'rsGetClientIDbyRessInitials' expects parameter '@.Initials', which was not supplied., SQL state 37000 in SQLExecDirect in c:\src\db\DB_Interface.php on line 102
rsGetClientIDbyRessInitials 8
Could not execute Statement! MySQL-Error: [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'rsGetClientIDbyRessInitials' expects parameter '@.Initials', which was not supplied.
But i'm not trying to send the parameter initials, i'm only trying to send clientID and then return initials.
This is my function:
function getInitials($quotId){
$conn = new Connection();
$initialsArr = $conn->select("rsGetClientIDbyRessInitials ".$quotId);
if($result[0]['IDQuotation'] > 0){
$quotId = $this->getOriginalId($result[0]['IDQuotation']);
}
return $initialsArr;
}//end function
Can you help me out, i've been pulling my hair over this
