query assistance: procedure

Showing posts with label procedure. Show all posts

Wednesday, March 28, 2012

Query Help

Hoping the community can help me with this problem. I have a stored
procedure that returns a few alias fields.
Here is the select clause to give you an idea what I am talking about (FROM
and WHERE omitted to shorten the length):
SELECT DriverTable.DriverLastName, DriverTable.DriverFirstName,
DriverTable.DriverMiddleInitial,[Company Table].CompanyName, [Locations
Table].LocationName,
CASE WHEN DriverLicenseRenewDate <= GETDATE()
THEN 'Drivers Licanse has Expired'
WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
DATEADD(dd, 60, GETDATE())
THEN 'Drivers Licanse expires within the next 60 days' END AS DLRenewDate,
CASE WHEN [Driver'sLicensePhoto] = 0 THEN 'Send Copy of Driver''s License'
END AS DLPhoto,
CASE WHEN DriverEmploymentDateHired >= '8/17/2001' AND
[DriverPre-employmentDrugTestDate]IS NULL THEN 'Send Copy of Current Driver
Pre-employment Drug Test' END AS [DPre-Employ], CASE WHEN
[DriverEmploymentDateHired] >= '8/17/2001' AND
[DriverPre-employment4025(j)DrugQuestion] = 0 THEN 'Send Copy of Driver
Pre-employment Form (§40.25(j)' END AS DPre4025DrugTest
This query has 4 alias's to be returned; DLRenewDate,DLPhoto,[DPre-Employ],
and DPre4025DrugTest. My problem is that if all 4 are returned NULL I do
not want the record to be returned with the query. If I could have a WHERE
like this:
WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
NOT NULL AND DPre4025DrugTest IS NOT NULL
then it would not be a problem, but you cannot test alias's in this manner
(as far as I can tell). What are my options for solving this problem? If I
was in Access I could run a second query on the first and test my
conditions. Can I use another Stored procedure in this manner?
For Example a second stored procedure that works like this:
Select * From StoredProcedure1
WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
NOT NULL AND DPre4025DrugTest IS NOT NULLHi
To reduce the amount of typing you have to do, you can use a table alias for
the table. see Books Online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\acd
ata.chm::/ac_8_qd_05_4ofn.htm
Instead of using the column alias in the WHERE clause, use the actual
expression. As these are CASE statements returning non null values when they
are satisfied they will only return null if non of the clauses are returned,
so these can be tested in the WHERE clause
e.g
For:
CASE WHEN DriverLicenseRenewDate <= GETDATE()
THEN 'Drivers Licanse has Expired'
WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
DATEADD(dd, 60, GETDATE())
THEN 'Drivers Licanse expires within the next 60 days'
END AS DLRenewDate
This will only be null if
NOT ( DriverLicenseRenewDate <= GETDATE()
OR ( DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
DATEADD(dd, 60, GETDATE()) ) )
Which is the same as
DriverLicenseRenewDate > GETDATE()
AND DriverLicenseRenewDate >= DATEADD(dd, 60, GETDATE()) )
HTH
John
"REB" <russ@.bjorgaard.com> wrote in message
news:%23HAwGAZ8DHA.488@.TK2MSFTNGP12.phx.gbl...
> Hoping the community can help me with this problem. I have a stored
> procedure that returns a few alias fields.
> Here is the select clause to give you an idea what I am talking about
(FROM
> and WHERE omitted to shorten the length):
> SELECT DriverTable.DriverLastName, DriverTable.DriverFirstName,
> DriverTable.DriverMiddleInitial,[Company Table].CompanyName, [Locations
> Table].LocationName,
> CASE WHEN DriverLicenseRenewDate <= GETDATE()
> THEN 'Drivers Licanse has Expired'
> WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
> DATEADD(dd, 60, GETDATE())
> THEN 'Drivers Licanse expires within the next 60 days' END AS DLRenewDate,
> CASE WHEN [Driver'sLicensePhoto] = 0 THEN 'Send Copy of Driver''s License'
> END AS DLPhoto,
> CASE WHEN DriverEmploymentDateHired >= '8/17/2001' AND
> [DriverPre-employmentDrugTestDate]IS NULL THEN 'Send Copy of Current
Driver
> Pre-employment Drug Test' END AS [DPre-Employ], CASE WHEN
> [DriverEmploymentDateHired] >= '8/17/2001' AND
> [DriverPre-employment4025(j)DrugQuestion] = 0 THEN 'Send Copy of Driver
> Pre-employment Form (§40.25(j)' END AS DPre4025DrugTest
> This query has 4 alias's to be returned;
DLRenewDate,DLPhoto,[DPre-Employ],
> and DPre4025DrugTest. My problem is that if all 4 are returned NULL I do
> not want the record to be returned with the query. If I could have a
WHERE
> like this:
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
> then it would not be a problem, but you cannot test alias's in this manner
> (as far as I can tell). What are my options for solving this problem? If
I
> was in Access I could run a second query on the first and test my
> conditions. Can I use another Stored procedure in this manner?
> For Example a second stored procedure that works like this:
> Select * From StoredProcedure1
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
>|||That makes sense, thank you.
"REB" <russ@.bjorgaard.com> wrote in message
news:%23HAwGAZ8DHA.488@.TK2MSFTNGP12.phx.gbl...
> Hoping the community can help me with this problem. I have a stored
> procedure that returns a few alias fields.
> Here is the select clause to give you an idea what I am talking about
(FROM
> and WHERE omitted to shorten the length):
> SELECT DriverTable.DriverLastName, DriverTable.DriverFirstName,
> DriverTable.DriverMiddleInitial,[Company Table].CompanyName, [Locations
> Table].LocationName,
> CASE WHEN DriverLicenseRenewDate <= GETDATE()
> THEN 'Drivers Licanse has Expired'
> WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
> DATEADD(dd, 60, GETDATE())
> THEN 'Drivers Licanse expires within the next 60 days' END AS DLRenewDate,
> CASE WHEN [Driver'sLicensePhoto] = 0 THEN 'Send Copy of Driver''s License'
> END AS DLPhoto,
> CASE WHEN DriverEmploymentDateHired >= '8/17/2001' AND
> [DriverPre-employmentDrugTestDate]IS NULL THEN 'Send Copy of Current
Driver
> Pre-employment Drug Test' END AS [DPre-Employ], CASE WHEN
> [DriverEmploymentDateHired] >= '8/17/2001' AND
> [DriverPre-employment4025(j)DrugQuestion] = 0 THEN 'Send Copy of Driver
> Pre-employment Form (§40.25(j)' END AS DPre4025DrugTest
> This query has 4 alias's to be returned;
DLRenewDate,DLPhoto,[DPre-Employ],
> and DPre4025DrugTest. My problem is that if all 4 are returned NULL I do
> not want the record to be returned with the query. If I could have a
WHERE
> like this:
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
> then it would not be a problem, but you cannot test alias's in this manner
> (as far as I can tell). What are my options for solving this problem? If
I
> was in Access I could run a second query on the first and test my
> conditions. Can I use another Stored procedure in this manner?
> For Example a second stored procedure that works like this:
> Select * From StoredProcedure1
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
>sql

Monday, March 26, 2012

query function in stored proc... how to do it?

how can i view all the records in my stored procedure?

how is the query function done?

for example i had my datagrid view... and of course a view button that will trigger a view action in able to view the entire records that i input....

i am not familiar with such things..

pls help me to figure this out..

thanks..

im just a begginer when it comes to this..

pls help me..

thanks..

Are you asking about how to do this from your application code or from toosl like SSMS ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Monday, March 12, 2012

Query Duration

Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23Hb$pYPvHHA.4520@.TK2MSFTNGP02.phx.gbl...
> If you're running Management Studio / Profiler locally against SQL Server
> on another server, then you need to account for network latency, I/O, your
> side's ability to render the results, etc.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "cbrichards via droptable.com" <u3288@.uwe> wrote in message
> news:7496c0e253cde@.uwe...
>

Query Duration

When I run a procedure and monitor in Profiler, and add up the milliseconds
from the Duration column, it comes to approximately 28000 (28 seconds). Yet
the true elapased time is about 48 seconds.
I have run SET STATISTICS TIME and the cumulative duration of the individual
statements is still far less than the actual elapsed time.
Is there a way to account for vast discrepancy between the cumulative
duration in Profiler and the actual elapsed time?
NOTE:
I have "Include Actual Execution Plan" turned off in Management Studio just
to make sure it was not the overhead of creating this graphical plan when I
am performing these duration tests.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200707/1If you're running Management Studio / Profiler locally against SQL Server on
another server, then you need to account for network latency, I/O, your
side's ability to render the results, etc.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:7496c0e253cde@.uwe...
> When I run a procedure and monitor in Profiler, and add up the
> milliseconds
> from the Duration column, it comes to approximately 28000 (28 seconds).
> Yet
> the true elapased time is about 48 seconds.
> I have run SET STATISTICS TIME and the cumulative duration of the
> individual
> statements is still far less than the actual elapsed time.
> Is there a way to account for vast discrepancy between the cumulative
> duration in Profiler and the actual elapsed time?
> NOTE:
> I have "Include Actual Execution Plan" turned off in Management Studio
> just
> to make sure it was not the overhead of creating this graphical plan when
> I
> am performing these duration tests.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200707/1
>|||Are you collecting StartTime / EndTime? If so, what's the difference between
the two?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:7496c0e253cde@.uwe...
> When I run a procedure and monitor in Profiler, and add up the
> milliseconds
> from the Duration column, it comes to approximately 28000 (28 seconds).
> Yet
> the true elapased time is about 48 seconds.
> I have run SET STATISTICS TIME and the cumulative duration of the
> individual
> statements is still far less than the actual elapsed time.
> Is there a way to account for vast discrepancy between the cumulative
> duration in Profiler and the actual elapsed time?
> NOTE:
> I have "Include Actual Execution Plan" turned off in Management Studio
> just
> to make sure it was not the overhead of creating this graphical plan when
> I
> am performing these duration tests.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200707/1
>|||Not to mention the fact that Profiler's reported duration is totally out of
sync with reality in many cases :)
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23Hb$pYPvHHA.4520@.TK2MSFTNGP02.phx.gbl...
> If you're running Management Studio / Profiler locally against SQL Server
> on another server, then you need to account for network latency, I/O, your
> side's ability to render the results, etc.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
> news:7496c0e253cde@.uwe...
>> When I run a procedure and monitor in Profiler, and add up the
>> milliseconds
>> from the Duration column, it comes to approximately 28000 (28 seconds).
>> Yet
>> the true elapased time is about 48 seconds.
>> I have run SET STATISTICS TIME and the cumulative duration of the
>> individual
>> statements is still far less than the actual elapsed time.
>> Is there a way to account for vast discrepancy between the cumulative
>> duration in Profiler and the actual elapsed time?
>> NOTE:
>> I have "Include Actual Execution Plan" turned off in Management Studio
>> just
>> to make sure it was not the overhead of creating this graphical plan when
>> I
>> am performing these duration tests.
>> --
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200707/1
>

Query Duration

Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:%23Hb$pYPvHHA.4520@.TK2MSFTNGP02.phx.gbl...
> If you're running Management Studio / Profiler locally against SQL Server
> on another server, then you need to account for network latency, I/O, your
> side's ability to render the results, etc.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "cbrichards via droptable.com" <u3288@.uwe> wrote in message
> news:7496c0e253cde@.uwe...
>

query does oposite of what i want

I am trying to find all the user tables that are not mentioned in a view or
stored procedure. Instead, this lists each table once for each stored
procedure it is not appearing.
select so2.name,so.name
from AdminDB.dbo.sysobjects so inner join AdminDB.dbo.syscomments sc on
(so.id=sc.id)
inner join AdminDB.dbo.sysobjects so2 on
(patindex('%so2.name%',sc.text)=0)
where so.xtype in ('P','V')
and so2.xtype ='U'
order by so2.name
GOT IT!
SELECT a.name
FROM AdminDB.dbo.sysobjects a LEFT JOIN (
SELECT so2.name,so.name AS 'usedin'
FROM AdminDB.dbo.sysobjects so INNER JOIN AdminDB.dbo.syscomments sc
ON (so.id=sc.id)
INNER JOIN AdminDB.dbo.sysobjects so2 ON (patindex('%' + so2.name+
'%',sc.text)>0)
WHERE so.xtype IN ('P','V','FN')
AND so2.xtype ='U'
) x ON a.name = x.name
WHERE x.name IS NULL
AND a.xtype IN ('U')
"DBA72" wrote:

> I am trying to find all the user tables that are not mentioned in a view or
> stored procedure. Instead, this lists each table once for each stored
> procedure it is not appearing.
> select so2.name,so.name
> from AdminDB.dbo.sysobjects so inner join AdminDB.dbo.syscomments sc on
> (so.id=sc.id)
> inner join AdminDB.dbo.sysobjects so2 on
> (patindex('%so2.name%',sc.text)=0)
> where so.xtype in ('P','V')
> and so2.xtype ='U'
> order by so2.name
|||I would use the ANSI schema views, as opposed to directly accessing system
tables. Does this yield the same result as your query?
SELECT t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
LEFT OUTER JOIN
(
SELECT
oname = ROUTINE_NAME,
odef = ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
UNION ALL
SELECT
oname = TABLE_NAME,
odef = VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
) o
ON o.odef LIKE '%'+t.TABLE_NAME+'%'
WHERE o.oname IS NULL
Note, of course, that pattern matching isn't perfect, for example there are
these (and probably many other) limitations:
(a) a table name could be mentioned in a comment (false positive)
(b) a table name could be spread across multiple rows for a proc/view>8000
characters (missing)
(c) the view/proc name could be the same as or contain the table name, but
not actually depend on it
http://www.aspfaq.com/
(Reverse address to reply.)
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:BB5D2A28-C9A7-4606-82A6-3E7960E72065@.microsoft.com...[vbcol=seagreen]
> GOT IT!
> SELECT a.name
> FROM AdminDB.dbo.sysobjects a LEFT JOIN (
> SELECT so2.name,so.name AS 'usedin'
> FROM AdminDB.dbo.sysobjects so INNER JOIN AdminDB.dbo.syscomments sc
> ON (so.id=sc.id)
> INNER JOIN AdminDB.dbo.sysobjects so2 ON (patindex('%' + so2.name+
> '%',sc.text)>0)
> WHERE so.xtype IN ('P','V','FN')
> AND so2.xtype ='U'
> ) x ON a.name = x.name
> WHERE x.name IS NULL
> AND a.xtype IN ('U')
> "DBA72" wrote:
or[vbcol=seagreen]

query does oposite of what i want

I am trying to find all the user tables that are not mentioned in a view or
stored procedure. Instead, this lists each table once for each stored
procedure it is not appearing.
select so2.name,so.name
from AdminDB.dbo.sysobjects so inner join AdminDB.dbo.syscomments sc on
(so.id=sc.id)
inner join AdminDB.dbo.sysobjects so2 on
(patindex('%so2.name%',sc.text)=0)
where so.xtype in ('P','V')
and so2.xtype ='U'
order by so2.nameGOT IT!
SELECT a.name
FROM AdminDB.dbo.sysobjects a LEFT JOIN (
SELECT so2.name,so.name AS 'usedin'
FROM AdminDB.dbo.sysobjects so INNER JOIN AdminDB.dbo.syscomments sc
ON (so.id=sc.id)
INNER JOIN AdminDB.dbo.sysobjects so2 ON (patindex('%' + so2.name+
'%',sc.text)>0)
WHERE so.xtype IN ('P','V','FN')
AND so2.xtype ='U'
) x ON a.name = x.name
WHERE x.name IS NULL
AND a.xtype IN ('U')
"DBA72" wrote:

> I am trying to find all the user tables that are not mentioned in a view o
r
> stored procedure. Instead, this lists each table once for each stored
> procedure it is not appearing.
> select so2.name,so.name
> from AdminDB.dbo.sysobjects so inner join AdminDB.dbo.syscomments sc on
> (so.id=sc.id)
> inner join AdminDB.dbo.sysobjects so2 on
> (patindex('%so2.name%',sc.text)=0)
> where so.xtype in ('P','V')
> and so2.xtype ='U'
> order by so2.name|||I would use the ANSI schema views, as opposed to directly accessing system
tables. Does this yield the same result as your query?
SELECT t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
LEFT OUTER JOIN
(
SELECT
oname = ROUTINE_NAME,
odef = ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
UNION ALL
SELECT
oname = TABLE_NAME,
odef = VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
) o
ON o.odef LIKE '%'+t.TABLE_NAME+'%'
WHERE o.oname IS NULL
Note, of course, that pattern matching isn't perfect, for example there are
these (and probably many other) limitations:
(a) a table name could be mentioned in a comment (false positive)
(b) a table name could be spread across multiple rows for a proc/view>8000
characters (missing)
(c) the view/proc name could be the same as or contain the table name, but
not actually depend on it
http://www.aspfaq.com/
(Reverse address to reply.)
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:BB5D2A28-C9A7-4606-82A6-3E7960E72065@.microsoft.com...[vbcol=seagreen]
> GOT IT!
> SELECT a.name
> FROM AdminDB.dbo.sysobjects a LEFT JOIN (
> SELECT so2.name,so.name AS 'usedin'
> FROM AdminDB.dbo.sysobjects so INNER JOIN AdminDB.dbo.syscomments sc
> ON (so.id=sc.id)
> INNER JOIN AdminDB.dbo.sysobjects so2 ON (patindex('%' + so2.name+
> '%',sc.text)>0)
> WHERE so.xtype IN ('P','V','FN')
> AND so2.xtype ='U'
> ) x ON a.name = x.name
> WHERE x.name IS NULL
> AND a.xtype IN ('U')
> "DBA72" wrote:
>
or[vbcol=seagreen]

Saturday, February 25, 2012

Query batch completed with errors...

Hi,
I am executin a stored procedure which contains a sequence of Stored
procedures. One of these stored procedures is a cursor-stored procedure
(basically a cursor).
At the end of the execution of this Stp(cursor), it gives me "query batch
completed with errors". I tried to capture the error through Printing
@.@.error, but to no luck. The problem is that this stp is part of a sequence
of stored procedures. Because of this error the subsequent stps dont get
executed.
Could someone give some leads how to resolve this?
Thanx,
S.How are you executing the proc? Did you try running it in isolation in Query
Analyzer? That should at least show you the error message in the results
window. Debug the proc to find the statement that throws the error (In
Query Analyzer's Object Browser right-click the proc name and select Debug).
If you're still stuck once you've narrowed it down a bit then post some code
here to reproduce the error.
David Portas
SQL Server MVP
--|||David,
Yes I was executing it in the Analyser..that how i found out it was this stp
which was givin the prob...
Here is the code for the problem stp..
---
CREATE PROCEDURE CALC_MyDealValue_As_DealPartner
AS
DECLARE
@.pCustomerCivilId nvarchar(15),
@.pInvoiceNo nvarchar(50),
@.pLiability char(1),
@.pAppId char(3),
@.pGrossBalance decimal(15,3),
@.pShareHolderCivilID nvarchar(15),
@.pPartnerShare decimal(15,3)
--Declare the copy cursor
DECLARE DealPartner_Cursor CURSOR FOR
SELECT CustomerCivilId,InvoiceNumber,Liability,
AppId,ShareHolderCivilID,
Convert(Decimal(15,3),SharePercentage)/100 as share
FROM dbo.Shareholder
--where Left(CustomerCivilId,2) ='JA'
where (InvoiceNumber<>'') and (Liability <> '') and (AppId <>'') --For
Deal Partner This is not empty in Shareholder Table
--Open the cursor
BEGIN
OPEN DealPartner_Cursor
FETCH NEXT FROM DealPartner_Cursor
INTO
@.pCustomerCivilId,@.pInvoiceNo,@.pLiabilit
y,@.pAppId,@.pShareHolderCivilID,@.pPar
tnerShare
IF @.@.Error <>0
Begin
Print 'Fetch 1' + convert(nvarchar(50),@.@.Error)
End
WHILE @.@.FETCH_STATUS = 0
BEGIN
--Print 'Parnter- ' + convert(nvarchar(50),@.pShareHolderCivilI
D)
--Print 'Share- ' + convert(nvarchar(50),@.pPartnerShare)
SET @.pGrossBalance = (SELECT isnull(GrossBalance,0) FROM vwMyDealAmounts
WHERE ( --dbo.vwMyDealAmounts.CivilID = @.pCustomerCivilId and
dbo.vwMyDealAmounts.InvoiceNumber = @.pInvoiceNo and
dbo.vwMyDealAmounts.Liability = @.pLiability and
dbo.vwMyDealAmounts.AppId = @.pAppId))
IF @.@.Error <>0
Begin
Print 'Fetch GB ' + convert(nvarchar(50),@.@.Error)
End
--Print 'GrossBal - ' + convert(nvarchar(50),@.pGrossBalance)
--Print 'DealCivilId - ' + convert(nvarchar(50),@.pDealCivilID)
Set @.pGrossBalance = isnull(@.pGrossBalance,0)
if (@.pGrossBalance <> 0.000)
Begin
if Left(@.pCustomerCivilId,2) ='JA'
Begin
Update TmpMyDealings
Set ValAsDealPartner= ValAsDealPartner +(@.pGrossBalance*@.pPartnerShare)
where TmpMyDealings.CivilId=@.pCustomerCivilId -- update TO JA
End
IF @.@.Error <>0
Begin
Print 'UP JA ' + convert(nvarchar(50),@.@.Error)
End
Update TmpMyDealings
Set ValAsDealPartner= ValAsDealPartner +(@.pGrossBalance*@.pPartnerShare)
where TmpMyDealings.CivilId=@.pShareHolderCivilID
IF @.@.Error <>0
Begin
Print 'UP NON-JA ' + convert(nvarchar(50),@.@.Error)
End
--Print 'Done for ' + convert(nvarchar(50),@.pCustomerCivilId) + ' - ' +
convert(nvarchar(50),@.pShareHolderCivilI
D)
--Print 'Done for Count ' + convert(nvarchar(50),@.iCount)
End
FETCH NEXT FROM DealPartner_Cursor
INTO
@.pCustomerCivilId,@.pInvoiceNo,@.pLiabilit
y,@.pAppId,@.pShareHolderCivilID,@.pPar
tnerShare
IF @.@.Error <>0
Begin
Print 'Fetch 2 ' + convert(nvarchar(50),@.@.Error)
End
END
CLOSE DealPartner_Cursor
DEALLOCATE DealPartner_Cursor
END
GO
---
"David Portas" wrote:

> How are you executing the proc? Did you try running it in isolation in Que
ry
> Analyzer? That should at least show you the error message in the results
> window. Debug the proc to find the statement that throws the error (In
> Query Analyzer's Object Browser right-click the proc name and select Debug
).
> If you're still stuck once you've narrowed it down a bit then post some co
de
> here to reproduce the error.
> --
> David Portas
> SQL Server MVP
> --
>
>|||You still didn't tell us the error message. Did you run it in Debug?
Honestly I wouldn't bother though. You should always try to avoid cursors
and 90% of this code is redundant. The effect seems to be the same as that
of a single UPDATE statement.
The following is my best guess of what you need to do. It's untested and as
it's done without a spec you'll probably have to make some mods. In
particular you may want to add WHERE EXISTS depending on requirements.
CREATE PROCEDURE calc_mydealvalue_as_dealpartner
AS
UPDATE TmpMyDealings
SET valasdealpartner = valasdealpartner +
(SELECT ISNULL(SUM(D.grossbalance*
CONVERT(DECIMAL(15,3),S.sharepercentage)/100),0)
FROM vwMyDealAmounts AS D
JOIN Shareholder AS S
ON D.invoicenumber = S.invoicenumber
AND D.liability = S.liability
AND D.appid = S.appid
AND D.grossbalance <>0
AND tmpmydealings.civilid = S.customercivilid
AND S.customercivilid LIKE 'JA%')
RETURN
GO
If you need more help please post DDL, a few rows of sample data (INSERT
statements are the best way to post sample data) and show us what result you
require from the sample. See:
http://www.aspfaq.com/etiquette.asp?id=5006
Hope this helps.
David Portas
SQL Server MVP
--

Monday, February 20, 2012

Query as stored procedure?

I've read a few articles about this, and trauled the net but I cant quite get one to fit my query.

I need to make this as a stored procedure,

select records_tbl.place_id, who_tbl.person_id, place_tbl.place_name, records_tbl.record_datetime, who_tbl.who_name, thing_tbl.thing_name
from records_tbl, place_tbl, who_tbl, thing_tbl
where records_tbl.place_id=place_tbl.place_id
and thing_tbl.thing_id='TH0001'
and who_tbl.person_id='WH000005'

But where I can simply specify on execute the thing_id and the person_id. ie Execute query1 ('TH0001', WH000005')?

So this would involve removing thos two id's and changing them to parameter names? defined at the top of the query? in theory I understand but in practise I do not.

Can anyone help?Create PROCEDURE MyQuery1
(
@.thing_id VARCHAR(20)
,@.person_id VARCHAR(20)
)
AS

SELECT records_tbl.place_id,
who_tbl.person_id,
place_tbl.place_name,
records_tbl.record_datetime,
who_tbl.who_name,
thing_tbl.thing_name
FROM records_tbl,
place_tbl,
who_tbl,
thing_tbl
WHERE records_tbl.place_id = place_tbl.place_id
AND thing_tbl.thing_id = @.thing_id
AND who_tbl.person_id = @.person_id

GO

and Now call
exec MyQuery1 @.thing_id='TH0001' , @.person_id= 'WH000005'

that what you want??|||thats excellent! thanks a lot! :) :)

Wednesday, March 28, 2012

Query Help

Monday, March 26, 2012

query function in stored proc... how to do it?

Monday, March 12, 2012

Query Duration

Query Duration

Query Duration

query does oposite of what i want

query does oposite of what i want

Saturday, February 25, 2012

Query batch completed with errors...

Monday, February 20, 2012

Query as stored procedure?

query assistance

Blog Archive

About Me