charles
Posts: 59
|
| Posted: 10/28/2006, 10:10 AM |
|
I have never used stored procedure as grid datasource since i have being using codecharge studio.
A situation arose where i needed to use a union query as my datasource hence my using stored procedures as my datasource.
my confusion is on how to filter the result with a parameter;s_period_id which will be sent to the page via the URL.
precisely, how can i configure the form properties to accomplish this.
Regards,
Charles
|
 |
 |
Edd
Posts: 547
|
| Posted: 10/28/2006, 6:05 PM |
|
The filtering needs to be done at the Stored Procedure level so the parameter needs to be added to the stored procedure. You then change the SQL statements inside the stored procedure to test for the @s_period_id
My rules about stored procedures and CCS:
1. When you do a "get parameters: in the design, it will often change any varchars to chars - be careful to review this,
2. Passing dates can get screwed up some times - if in doubt always pass dates in the international form of YYYY-MM-DD
3. Sorting columns DOES NOT work for stored procedures unless you code it specifically.
4. In MSSQL Use the "Set NoCount" method to mask extraneous SQL calls when using temp tables.
5. And ALWAYS have a return values as your last statement.
Follow the above and CCS and Stored Procedures always work like clockwork.
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
charles
Posts: 59
|
| Posted: 10/29/2006, 3:52 AM |
|
Quote :The filtering needs to be done at the Stored Procedure level so the parameter needs to be added to the stored procedure. You then change the SQL statements inside the stored procedure to test for the @s_period_id
Edd,Please kindly clarify this statement.
This is my stored procedure code,how do i the filtering at the stored procedure level?
CREATE PROCEDURE [dbo].[cashbook_unionview]
@period_id int
AS
SELECT dbo.transactions.account_no, dbo.accounts.account_name, dbo.transactions.transaction_id, dbo.transactions.transactiondate,
dbo.transactions.transactiondescription, dbo.transactions.withdrawalamount, dbo.transactions.depositamount, dbo.transactions.period_id,
dbo.transactions.transtatus_id
FROM dbo.transactions INNER JOIN
dbo.accounts ON dbo.transactions.account_no = dbo.accounts.account_no
WHERE (dbo.transactions.account_no LIKE '25225%') AND (dbo.transactions.transtatus_id = 1)and depositamount=0
UNION ALL
SELECT dbo.transactions.account_no, dbo.accounts.account_name, dbo.transactions.transaction_id, dbo.transactions.transactiondate,
dbo.transactions.transactiondescription, dbo.transactions.withdrawalamount, dbo.transactions.depositamount, dbo.transactions.period_id,
dbo.transactions.transtatus_id
FROM dbo.transactions INNER JOIN
dbo.accounts ON dbo.transactions.account_no = dbo.accounts.account_no
WHERE (dbo.transactions.account_no LIKE '25225%') AND (dbo.transactions.transtatus_id = 1)and withdrawalamount=0
order by dbo.transactions.transactiondate
GO
So how do iadjust the above?
Regards,
charles
|
|
|
|
Edd
Posts: 547
|
| Posted: 10/29/2006, 4:47 AM |
|
Try:
CREATE PROCEDURE [dbo].[cashbook_unionview]
@period_id int
AS
SELECT dbo.transactions.account_no, dbo.accounts.account_name, dbo.transactions.transaction_id, dbo.transactions.transactiondate,
dbo.transactions.transactiondescription, dbo.transactions.withdrawalamount, dbo.transactions.depositamount, dbo.transactions.period_id,
dbo.transactions.transtatus_id
FROM dbo.transactions INNER JOIN
dbo.accounts ON dbo.transactions.account_no = dbo.accounts.account_no
WHERE (dbo.transactions.account_no LIKE '25225%') AND (dbo.transactions.transtatus_id = 1)and depositamount=0
AND dbo.transactions.period_id = @period_id
UNION ALL
SELECT dbo.transactions.account_no, dbo.accounts.account_name, dbo.transactions.transaction_id, dbo.transactions.transactiondate,
dbo.transactions.transactiondescription, dbo.transactions.withdrawalamount, dbo.transactions.depositamount, dbo.transactions.period_id,
dbo.transactions.transtatus_id
FROM dbo.transactions INNER JOIN
dbo.accounts ON dbo.transactions.account_no = dbo.accounts.account_no
WHERE (dbo.transactions.account_no LIKE '25225%') AND (dbo.transactions.transtatus_id = 1)and withdrawalamount=0
AND dbo.transactions.period_id = @period_id
order by dbo.transactions.transactiondate
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
charles
Posts: 59
|
| Posted: 10/29/2006, 9:56 AM |
|
Thanks edd.I have adjusted the stored procedure accordingly.
Now how do i adjust the form's where parameter.
presently i have
STORED PROCEDURE PARAMETER:@returnvalue
PARAMETER:@RETURN_VALUE int URL direction return value
STORED PROCEDURE PARAMETER:@period_id
PARAMETER:period_id int URL direction input
How do i configure the form to filter the grid resultset by the s_period_id i am sending over
the URL
regards,
charles
|
|
|
|
Edd
Posts: 547
|
| Posted: 10/29/2006, 1:18 PM |
|
STORED PROCEDURE PARAMETER:@period_id
PARAMETER:period_id int URL direction input
Change the passing PARAMETER name to "s_period_id" - the same way you would do a normal search form.
Note: set a default value (suggest zero) so the stored procedure does not recieve a NULL if "s_period_id" is not passed.
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
charles
Posts: 59
|
| Posted: 10/29/2006, 8:24 PM |
|
Many thanks to you edd.
The Form now filters perfectly by the s_period_id sent over the URL.
Now i can use stored procedure as the datasource for my grid forms which to me is a watershed in my journey with this wonderful tool,codecharge studio.
Edd, you are Good man!
Thanks for always sharing your knowledge in this forum.
Regards,
Charles
|
|
|
|
norbi771
|
| Posted: 11/21/2006, 6:44 PM |
|
Dear Edd,
I read the above conversation hoping that it could help me, but it didn't
Maybe you could help me somehow
CREATE PROCEDURE [dbo].[TR_ManagersTree]
@EmployeeID int, @Intercontinental int
AS
declare @Mgr as int
declare @Emp as int
declare @ID_MD as int
SET @ID_MD = 227
create table #tree (id int identity(1,1), i_emp int, i_mgr int)
insert #tree(i_emp, i_mgr) select @ID_MD, @ID_MD
select * from V_EMP
GO
The above doesn't work.
As soon as I remove line that inserts some data into temporary table, it works fine
insert #tree(i_emp, i_mgr) select @ID_MD, @ID_MD
Of course both statements work in query analyzer.
I need to return the "managers tree" and I cannot replace this with any view nor skip tmp table
I will appreciate any help
Best regards
Norbert
|
|
|
|
Edd
Posts: 547
|
| Posted: 11/22/2006, 4:03 AM |
|
Norbert,
Stored procedures always return "messages" , example execute the stored procedure in Query builder and look at the messages screen, these are sent back to the calling language and you get dazed and confused.
To get around this you need to employ some techniques to hide the messages and also you need to return a value to say all is well.
If I take your above stored procedure I would rewite it as such:
CREATE PROCEDURE [dbo].[TR_ManagersTree]
@EmployeeID int, @Intercontinental int
AS
BEGIN
declare @Mgr as int
declare @Emp as int
declare @ID_MD as int
-- Don't send record counts back to the Record set
Set NoCount ON
SET @ID_MD = 227
create table #tree (id int identity(1,1), i_emp int, i_mgr int)
insert #tree(i_emp, i_mgr) select @ID_MD, @ID_MD
-- we now want to return the number of records
Set NoCount Off
select *
from V_EMP
END
-- You should ALWAYS return a success
Return 0
I have to say that is one weird stored procedure.
Hope it helps
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
|
