HELP!! - TSQL Cursor problem

Hi,

I have a work project due very soon and am stuck with something.

I am using a cursor in a stored procedure to return the required data for output in an ASP/VBScript page. The problem is that (as run in MS Query Analyser) the stored procedure returns the data in individual data sets - 1 for each iteration of the cursor. It is returned in multiple frames in QA, the same as when you run multiple queries at the same time in the QA window.

I have never used cursors before so maybe this is to be expected - but what I want is for all the data to be returned in one data set. At present I can only access part of the data in my webpage - when I run the stored procedure and loop thru the data in the webpage, there is only the data from the first iteration of the cursor.

Below is a representation of a chunk of rows from the table, the stored procedure and a representation of the returned results. Can you please help me to return all the data in a single data set, or else tell me how I can access each of the data sets in my webpage.

Many thanks in advance for your help

Simon

simon.barnettnospam@.elexon.co.uk

Table

ID_col, Category_col, KeyAccountability_col, PerformanceMeasure_col, StaffID_col
1, Delivery, KeyAcc1, PerfMeas1, 3
3, Delivery, KeyAcc2, PerfMeas2, 3
7, Delivery, KeyAcc3, PerfMeas3, 3
8, Department, KeyAcc4, PerfMeas4, 3
11, Department, KeyAcc5, PerfMeas5, 3
12, Department, KeyAcc6, PerfMeas6, 3
13, Communications, KeyAcc7, PerfMeas7, 3
16, Communications, KeyAcc8, PerfMeas8, 3

Stored Procedure

declare @.var0 nchar(56)
declare @.var1 nchar(56)
declare keyaccscursor cursor for
(SELECT distinct category from
[CareerFramework].[dbo].[KeyAccountability] where jobprofileid = @.jobprofileID)
OPEN keyaccscursor
FETCH NEXT FROM keyaccscursor
INTO @.var1
WHILE @.@.FETCH_STATUS = 0
BEGIN
select distinct KeyAccountability as col1, 'keyacc' as rowtype from KeyAccountability where (category = @.var1) and (jobprofileid = @.jobprofileID)
union
select distinct category as col1, 'cat' as rowtype from KeyAccountability where (category = @.var1) and (jobprofileid = @.jobprofileID)
FETCH NEXT FROM keyaccscursor
INTO @.var1
END
CLOSE keyaccscursor
DEALLOCATE keyaccscursor

Results (when run in MSSQL Query Analyser )

-

KeyAccountability PerformanceMeasure (column headings)

Delivery

KeyAcc1 PerfMeas1

KeyAcc2 PerfMeas2

KeyAcc3 PerfMeas3

-

KeyAccountability PerformanceMeasure (column headings)

Department

KeyAcc4 PerfMeas3

KeyAcc5 PerfMeas4

KeyAcc6 PerfMeas5

-

KeyAccountability PerformanceMeasure (column headings)

Communications

KeyAcc7 PerfMeas6

KeyAcc7 PerfMeas7

I understand you have to present the following data

3, Delivery, KeyAcc2, PerfMeas2, 3
7, Delivery, KeyAcc3, PerfMeas3, 3
8, Department, KeyAcc4, PerfMeas4, 3
11, Department, KeyAcc5, PerfMeas5, 3
12, Department, KeyAcc6, PerfMeas6, 3
13, Communications, KeyAcc7, PerfMeas7, 3
16, Communications, KeyAcc8, PerfMeas8, 3

As

Delivery

KeyAcc2, PerfMeas2
KeyAcc3, PerfMeas3

And

Department

KeyAcc4, PerfMeas4, 3
KeyAcc5, PerfMeas5, 3

KeyAcc6, PerfMeas6, 3

And so on.

I should use a DropDownList cotrol with sqlsource ?SELECT distinct category from
[CareerFramework].[dbo].[KeyAccountability] where jobprofileid = @.jobprofileID”

And a GridView control with

Select KeyAccountability, PerformanceMeasure from KeyAccountability where Category=@.Categ

And @.categ is linked with DropDownList value.

Hope this help

|||

To fix your query for your requirement,

Code Snippet

declare @.var0 nchar(56)

declare @.var1 nchar(56)

declare @.jobprofileID as int

Set @.jobprofileID = 1

declare @.result table

(

col1 varchar(1000),

rowtype varchar(10)

)

declare keyaccscursor cursor for

(select distinct [category] from [keyaccountability] where jobprofileid = @.jobprofileid)

open keyaccscursor

fetch next from keyaccscursor

into @.var1

while @.@.fetch_status = 0

begin

Insert Into @.result

select keyaccountability as col1, 'keyacc' as rowtype from keyaccountability where (category = @.var1) and (jobprofileid = @.jobprofileid)

union

select category as col1, 'cat' as rowtype from keyaccountability where (category = @.var1) and (jobprofileid = @.jobprofileid)

fetch next from keyaccscursor

into @.var1

end

close keyaccscursor

deallocate keyaccscursor

Select * from @.result

|||

You can achieve the result without cursor also, (Highly recommended)

Code Snippet

declare @.jobprofileID as int

set @.jobprofileID = 1

select keyaccountability as col1, 'keyacc' as rowtype from keyaccountability where

(jobprofileid = @.jobprofileid)

union

select category as col1, 'cat' as rowtype from keyaccountability where

(jobprofileid = @.jobprofileid)

|||

Thanks very much for this - inserting into a table during the loop gives me the single data set - exactly as required.

There are 2 problems though, which may be related.

1.

The first is that I cannot declare a table in memory.

declare @.result table

(

col1 varchar(1000),

rowtype varchar(10)

)

gives me the following error:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'table'.

(When I change to creating a permanent table using CREATE table, and run it in QA, the correct data populates the table.)

2.

When adding this into my stored procedure and running my webpage, the page breaks with the message that the recordset cannot be looped thru because it is closed.

When I comment out the insert statement but leave in the CREATE statement, the page works as it did before.

If you can help with this I would be very grateful.

Many thanks

Simon

|||

Hi,

I tried this also but although all the required results are returned as a single data set, KeyAccountabilities are not associated with the Categories as required.

All the Categories are listed in the top rows and then the KeyAccountabilities are listed in random order below. Looking at the SQL I can't see a way to group them as required using this method.

Would it be possible?

Many thanks

Simon

declare @.jobprofileID as int

set @.jobprofileID = 1

select keyaccountability as col1, 'keyacc' as rowtype from keyaccountability where

(jobprofileid = @.jobprofileid)

union

select category as col1, 'cat' as rowtype from keyaccountability where

(jobprofileid = @.jobprofileid)

|||

I am really wondering how its happen. What is the version of SQL Server you are using.

To find,

Select @.@.VERSION

Even on your ASP page you can change the RecordSet behaviour as UseClient istead of UseServer. It will fix the issue.

|||

The following query may fix your group/order issue,

Code Snippet

declare @.jobprofileID as int

set @.jobprofileID = 1

Select col1,rowtype from

(

select category,keyaccountability as col1, 'keyacc' as rowtype from keyaccountability where

(jobprofileid = @.jobprofileid)

Union

select category,category as col1, 'cat' as rowtype from keyaccountability where

(jobprofileid = @.jobprofileid)

) as Data

Order By

category, Case When rowtype='cat' Then 1 Else 2 End, Col1

|||Thank you

sql

HELP! Stored Procedure problem

I have the following stored procedure:

CREATE procedure sp_BA_BuildTablesStep2

(
@.BACKENDDATEVALUE varchar(10),
@.QUERYSTATMENT varchar(100)
)

AS

DECLARE @.SQL varchar(8000)
SET @.SQL = " if object_id('BA_BACK') is not null drop table BA_BACK

SELECT BA_NEW.SRC_CODE,
Count(RG_BA_SHIPPED_ORDERS.ORDER_NBR) AS SUBSEQUENT_ORDERS,
Sum(RG_BA_SHIPPED_ORDERS.TOTAL_AMT) AS INVOICE_TOTAL,
Sum(RG_BA_SHIPPED_ORDERS.SHIP_CHARGE) AS SHIPPING_CHARGE,
Sum(RG_BA_SHIPPED_ORDERS.SHIP_COST) AS SHIPPING_COST,
Sum(RG_BA_SHIPPED_ORDERS.MARGIN) AS MARGIN

INTO BA_BACK

FROM BA_NEW INNER JOIN RG_BA_SHIPPED_ORDERS ON BA_NEW.CUST_NBR = RG_BA_SHIPPED_ORDERS.CUST_NBR

WHERE (RG_BA_SHIPPED_ORDERS.DATE > [BA_NEW].[MaxOFDATE]) And (RG_BA_SHIPPED_ORDERS.DATE < = " + @.BACKENDDATEVALUE+ ") " + @.QUERYSTATMENT +"

GROUP BY BA_NEW.SRC_CODE"

EXEC(@.SQL)

GO

When I run it I get 0 rows affected, when I run the query in QA with my values plugged in I get the results I want... can you see anything I did wrong?

@.BACKENDDATEVALUE is a date supplied as '02/18/03' and @.QUERYSTATMENT is like: 'and RG_BA_SHIPPED_ORDERS.CO_TYPE =1'

I'm changeing @.QUERYSTATMENT by adding "OR" statements at the end depending on what the user chooses in the form.

Any ideas?

KenFigured out the problem! I wasn't passing the vars correctly!

Thanks much!

Ken

HELP! Stored Procedure Problem

I need to pull distinct records in my SP, but if there are different values in some of the columns it grabs those records too. I tried a nested query but I get an error that it is returning more than one value and not allowed.

I can grab the 1st record for each cust I need like this in a view:

SELECT TOP 100 PERCENT vcCustId, MIN(siEntry) AS MinRecNo, vcAdsource AS Ad
FROM dbo.PropReportData
GROUP BY vcCustId, vcAdsource
ORDER BY vcCustId

and then Inner Join it in my SP like this:

CREATE PROCEDURE Reports_GetReportData
(
@.cApartmentSite varchar(25),
@.vcAdSource varchar(50)
)
AS
SELECT *
FROM dbo.vPrePull INNER JOIN
 dbo.PropReportData ON dbo.vPrePull.MinRecNo = dbo.PropReportData.siEntry
WHERE
cApartmentSite = @.cApartmentSite and vcAdSource = @.vcAdSource
GO

Now, the problem is this, the parameters are in my stored procedure which is parsed second. So I am not getting the unique data to pull from.

Ultimately what I need to use is below with something inside it that will do what the view above did:

CREATE PROCEDURE Reports_GetReportData2
(
@.cApartmentSite varchar(25),
@.vcAdSource varchar(50)
)
AS
SELECT TOP 100 PERCENT vcCustId, MIN(siEntry) AS MinRecNo, vcAdsource AS Ad,sientry,vcProspectName,
vcPhone,vcEmail,vcDesiredHome,vcMoveInDate,vcStatus,vcVisitDate,vcComments
FROM dbo.PropReportData
WHERE
cApartmentSite = @.cApartmentSite and vcAdSource = @.vcAdSource
GROUP BY vcCustId, vcAdsource,sientry,vcProspectName,vcPhone,vcEmail,vcDesiredHome,vcMoveInDate,vcStatus,vcVisitDate,
vcComments
ORDER BY vcCustId
GO

Got to have this done by C.O.B. Monday or I may not have a job.
Thanks.What you want seems doable, but some more info (structure of the tables, some sample data) would help.

Not that I feel pressured or anything here...|||I'm using it to create reports for my CRM application and I used the Reports Starter Kit as my base. I am displaying Each Ad Source the Prospect called in on and the Prospects Record data in a tabular report. In the footer of each Ad Source it gives a count of the total leads that came in on that Ad Source. The problem is this, when a change is made it creates another record for that prospect with a unique record number; this has to be for history purposes, because I have another report that display all record activity, (that one works). So when I display the Prospect records it has a duplicate, which I can cull out by using vcCustID, MIN(siEntry) this gives me the first record entry for that Prospect, BUT, if one of the fields that I am trying to display was changed (like Visit Date in the example below), another instance of the Prospect is displayed. So for example I have a count of 2 unique leads by Ad Source and they call back and change their Visit Date, it will display a count of 2 leads (which is correct) and display 3 records (wrong), showing that Prospect twice.

Example:
ApartmentGuide.com
Prospect Name.Telephone.Email.DesiredHome.Move-In Date.Status.Visit Date
Bear, Smokey 911-911-9119 smokey@.nofire.com 1 x 1 10/31/2003 Visit Set10/23/2003
Bear, Smokey 911-911-9119 smokey@.nofire.com 1 x 1 10/31/2003 Visit Set10/26/2003
Walker, Johnny 555-645-7895 drunk@.booze.com 1 x 1 10/23/2003 Visit Set 10/23/2003

Total Leads this Ad Source: 2

I am pulling the data from a single table called PropReportData that has just the info I need for reporting. This was necessary because the information necessary to create a report is in 9 different tables and the amount of executes necessary to do the inner joins caused major performance issues and after running an execution plan it just didn't seem feasible to continue in that direction.

The table has vcCustID(Unique), cApartmentSite(used to associate Client to Cust to), siEntry(Unique Record Number), Ad Source, etc.

The last Stored Procedure in my first post is what I need to work, it has the parameters in it I need to display client specific data, which uses cApartmentSite. The value is picked up from the UserLogin and put in session to be used with my parameters and a few other things.

Each Prospect record created by my Marketing Associates for that client has this value inserted in a field in the PropReportData table creating the Client to Prospect relationship. So when the client logs it only pulls their information based on the cApartmentSite value in the tables.

Let me know if you need more info.

Thanks.|||This seems to work:

CREATE PROCEDURE Reports_PainInTheButt
(
@.cApartmentSite varchar(25),
@.vcAdSource varchar(50)
)
AS
SELECT DISTINCT
 TOP 100 PERCENT dbo.PropReportData.vcCustId, MIN(DISTINCT dbo.PropReportData.siEntry) AS siEntry,
 MIN(DISTINCT dbo.PropReportData.vcAdsource) AS vcAdsource,
 MIN(DISTINCT dbo.PropReportData.vcProspectName) AS vcProspectName, MIN(DISTINCT dbo.PropReportData.vcPhone) AS vcPhone,
 MIN(DISTINCT dbo.PropReportData.vcEmail) AS vcEmail, MIN(DISTINCT dbo.PropReportData.vcDesiredHome) AS vcDesiredHome,
 MIN(DISTINCT dbo.PropReportData.vcMoveInDate) AS vcMoveInDate, MIN(DISTINCT dbo.PropReportData.vcStatus) AS vcStatus,
 MIN(DISTINCT dbo.PropReportData.vcVisitDate) AS vcVisitDate, MIN(DISTINCT dbo.PropReportData.vcComments) AS vcComments
FROM dbo.PropReportData
WHERE dbo.PropReportData.cApartmentSite = @.cApartmentSite AND dbo.PropReportData.vcAdsource = @.vcAdSource
GROUP BY dbo.PropReportData.vcCustId
ORDER BY dbo.PropReportData.vcCustId
GO

If you see any potential problems with this let me know, it's all I can come up with.

Thanks.

HELP! Stored Procedure problem

I have the following stored procedure:

CREATE procedure sp_BA_BuildTablesStep2

(
@.BACKENDDATEVALUE varchar(10),
@.QUERYSTATMENT varchar(100)
)

AS

DECLARE @.SQL varchar(8000)
SET @.SQL = " if object_id('BA_BACK') is not null drop table BA_BACK

SELECT BA_NEW.SRC_CODE,
Count(RG_BA_SHIPPED_ORDERS.ORDER_NBR) AS SUBSEQUENT_ORDERS,
Sum(RG_BA_SHIPPED_ORDERS.TOTAL_AMT) AS INVOICE_TOTAL,
Sum(RG_BA_SHIPPED_ORDERS.SHIP_CHARGE) AS SHIPPING_CHARGE,
Sum(RG_BA_SHIPPED_ORDERS.SHIP_COST) AS SHIPPING_COST,
Sum(RG_BA_SHIPPED_ORDERS.MARGIN) AS MARGIN

INTO BA_BACK

FROM BA_NEW INNER JOIN RG_BA_SHIPPED_ORDERS ON BA_NEW.CUST_NBR = RG_BA_SHIPPED_ORDERS.CUST_NBR

WHERE (RG_BA_SHIPPED_ORDERS.DATE > [BA_NEW].[MaxOFDATE]) And (RG_BA_SHIPPED_ORDERS.DATE < = " + @.BACKENDDATEVALUE+ ") " + @.QUERYSTATMENT +"

GROUP BY BA_NEW.SRC_CODE"

EXEC(@.SQL)

GO

When I run it I get 0 rows affected, when I run the query in QA with my values plugged in I get the results I want... can you see anything I did wrong?

@.BACKENDDATEVALUE is a date supplied as '02/18/03' and @.QUERYSTATMENT is like: 'and RG_BA_SHIPPED_ORDERS.CO_TYPE =1'

I'm changeing @.QUERYSTATMENT by adding "OR" statements at the end depending on what the user chooses in the form.

Any ideas?

KenFigured out the problem! I wasn't passing the vars correctly!

Thanks much!

Ken

Help! SQL Server 2000 extended stored procedure hangs in Windows 98

I am trying to run xp_cmdshell from the Query Analyzer using SQL
Server 2000 running on Windows 98.

It seems like it should be simple - I'm typing

xp_cmdshell 'dir *.exe'

in the Query Analyzer in the Master db. I'm logged in as sa.

The timer starts running and never stops. No error message.

Can anyone PLEASE help me with this? Any suggestions would be
appreciated. Are SQL Server 2000 extended stored procedures not
supported in Windows 98? I've tried searching the Knowledge Base but
can't find anything.

Thanks!sylmart7 (sylmart7@.aol.com) writes:
> I am trying to run xp_cmdshell from the Query Analyzer using SQL
> Server 2000 running on Windows 98.
> It seems like it should be simple - I'm typing
> xp_cmdshell 'dir *.exe'
> in the Query Analyzer in the Master db. I'm logged in as sa.
> The timer starts running and never stops. No error message.
> Can anyone PLEASE help me with this? Any suggestions would be
> appreciated. Are SQL Server 2000 extended stored procedures not
> supported in Windows 98? I've tried searching the Knowledge Base but
> can't find anything.

As I have no experience at all of Windows 98, I cannot really help. What
I can say, though, after having read the topic on xp_cmdshell in Books
Online is: yes, xp_cmdshell is supported on Win98. The article mentions
several restrictions with regards to security context and return value
on Win 98, so obviously you should be able to use it.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

HELP! Question on SQL Mail

I am trying to send an email through an SQL Stored
procedure using Lotus Notes system.
I have read the article entitled: "An Introduction to
SQL Mail and SQLAgentMail".
I have a few questions:
1. Can I use SQL Mail even if my mail server is Lotus
Notes?
2. Do I need to install Lotus Notes client where the
SQL Server is?
OR is it: as long as the SQL Server can detect the
Lotus Notes server through the network, I just need to
do the configuration as described in the article?
I would appreciate it if someone can respond as soon as
possible.
Thank you very much.
Regards,
GGCThanks Jens for the links! I am going to check them.
Regards,
GGC

Help! Processing Hard Coded vs. Stored Procedure

Hi All,
I am just in the process of putting a script that updates a reporting table
into a stored procedure and have found that when run as a SP it takes soooo
much longer. Running the script in QA with the "WHERE" clauses hard coded
takes around 1 min, when I attempt to run via a SP with the "WHERE" clauses
replaced by variables (@.Title) that the user inputs it takes around 1 hour.
Below is a sample of both.
Can anyone shed any light on why? What am I doing wrong?
Thanks DC
Stored Procedure - -
CREATE PROCEDURE [dbo]. [sp_Report_Promoted_Issue_Impact_Analysi
s]
@.MarketGroup nvarchar (3),
@.Master_Title_id nvarchar (25),
@.Current_Title_Issue_Rank int
as
TRUNCATE TABLE Report_Promoted_Issue_Impact_Analysis
----
--INSERT INTO Report_Promoted_Issue_Impact_Analysis
( Retail_Type_id,
Retail_Type,
Market_Group_id,
Market_Group_Desc,
Outlet_id,
Branch_id,
EIS_Master_Title_id,
Title_id_2,
Issue_id,
Title_Issue_Rank,
Dist_Qty,
Sales_Qty
)
SELECT O.Retail_Class_id AS Retail_Type_id, MG.Retail_Class_Desc AS
Retail_Type, O.Market_Group_id, MG.Market_Group_Desc, OIH.Outlet_id,
TIB.Branch_id, T.EIS_Master_Title_id, OIH.Title_id_2,
OIH.Issue_id, TIB.Title_Issue_Rank, OIH.Dist_Qty, OIH.Sales_Qty
FROM Titles T RIGHT OUTER JOIN
Outlet_Issue_History OIH ON T.Title_id_2 =
OIH.Title_id_2 LEFT OUTER JOIN
Titles_Issues_Branch TIB ON OIH.Issue_id =
TIB.Issue_id AND OIH.Branch_id = TIB.Branch_id AND OIH.Title_id_2 =
TIB.Title_id_2 LEFT OUTER JOIN
Market_Groups MG RIGHT OUTER JOIN
Outlets O ON MG.Retail_Class_Market_Group_id =
O.Retail_Class_Market_Group_id ON OIH.Outlet_id = O.Outlet_id
WHERE (O.Market_Group_id <> @.MarketGroup) AND (T.EIS_Master_Title_id =
@.Master_Title_id) AND (TIB.Title_Issue_Rank = @.Current_Title_Issue_Rank) AND
(O.Retail_Class_id IN (N'A', N'B', N'K', N'S'))
Hard Coded Script - -
TRUNCATE TABLE Report_Promoted_Issue_Impact_Analysis
----
--
INSERT INTO Report_Promoted_Issue_Impact_Analysis
( Retail_Type_id,
Retail_Type,
Market_Group_id,
Market_Group_Desc,
Outlet_id,
Branch_id,
EIS_Master_Title_id,
Title_id_2,
Issue_id,
Title_Issue_Rank,
Dist_Qty,
Sales_Qty
)
SELECT O.Retail_Class_id AS Retail_Type_id, MG.Retail_Class_Desc AS
Retail_Type, O.Market_Group_id, MG.Market_Group_Desc, OIH.Outlet_id,
TIB.Branch_id, T.EIS_Master_Title_id, OIH.Title_id_2,
OIH.Issue_id, TIB.Title_Issue_Rank, OIH.Dist_Qty, OIH.Sales_Qty
FROM Titles T RIGHT OUTER JOIN
Outlet_Issue_History OIH ON T.Title_id_2 =
OIH.Title_id_2 LEFT OUTER JOIN
Titles_Issues_Branch TIB ON OIH.Issue_id =
TIB.Issue_id AND OIH.Branch_id = TIB.Branch_id AND OIH.Title_id_2 =
TIB.Title_id_2 LEFT OUTER JOIN
Market_Groups MG RIGHT OUTER JOIN
Outlets O ON MG.Retail_Class_Market_Group_id =
O.Retail_Class_Market_Group_id ON OIH.Outlet_id = O.Outlet_id
WHERE (O.Market_Group_id <> 'WW') AND (T.EIS_Master_Title_id = 'OK') AND
(TIB.Title_Issue_Rank = 5) AND (O.Retail_Class_id IN (N'A', N'B', N'K', N'S'
))Sounds to me like the query is using a different execution plan when it know
s
that the 'variables' have a particular value. Check the execution plans, hav
e
a look to see what indexes are being used, and see if this gives you some
clues into the situation.
Also make sure your statistics are updated, as this could cause the
optimiser to think that a particular plan might be best when it's really not
.
Rob
"David C" wrote:

> Hi All,
> I am just in the process of putting a script that updates a reporting tabl
e
> into a stored procedure and have found that when run as a SP it takes sooo
o
> much longer. Running the script in QA with the "WHERE" clauses hard coded
> takes around 1 min, when I attempt to run via a SP with the "WHERE" clause
s
> replaced by variables (@.Title) that the user inputs it takes around 1 hour
.
> Below is a sample of both.
> Can anyone shed any light on why? What am I doing wrong?
> Thanks DC
> Stored Procedure - -
> CREATE PROCEDURE [dbo]. [sp_Report_Promoted_Issue_Impact_Analysi
s]
> @.MarketGroup nvarchar (3),
> @.Master_Title_id nvarchar (25),
> @.Current_Title_Issue_Rank int
> as
> TRUNCATE TABLE Report_Promoted_Issue_Impact_Analysis
> ----
--INSERT INTO Report_Promoted_Issue_Impact_Analysis
> ( Retail_Type_id,
> Retail_Type,
> Market_Group_id,
> Market_Group_Desc,
> Outlet_id,
> Branch_id,
> EIS_Master_Title_id,
> Title_id_2,
> Issue_id,
> Title_Issue_Rank,
> Dist_Qty,
> Sales_Qty
> )
> SELECT O.Retail_Class_id AS Retail_Type_id, MG.Retail_Class_Desc AS
> Retail_Type, O.Market_Group_id, MG.Market_Group_Desc, OIH.Outlet_id,
> TIB.Branch_id, T.EIS_Master_Title_id, OIH.Title_id_2
,
> OIH.Issue_id, TIB.Title_Issue_Rank, OIH.Dist_Qty, OIH.Sales_Qty
> FROM Titles T RIGHT OUTER JOIN
> Outlet_Issue_History OIH ON T.Title_id_2 =
> OIH.Title_id_2 LEFT OUTER JOIN
> Titles_Issues_Branch TIB ON OIH.Issue_id =
> TIB.Issue_id AND OIH.Branch_id = TIB.Branch_id AND OIH.Title_id_2 =
> TIB.Title_id_2 LEFT OUTER JOIN
> Market_Groups MG RIGHT OUTER JOIN
> Outlets O ON MG.Retail_Class_Market_Group_id =
> O.Retail_Class_Market_Group_id ON OIH.Outlet_id = O.Outlet_id
> WHERE (O.Market_Group_id <> @.MarketGroup) AND (T.EIS_Master_Title_id =
> @.Master_Title_id) AND (TIB.Title_Issue_Rank = @.Current_Title_Issue_Rank) A
ND
> (O.Retail_Class_id IN (N'A', N'B', N'K', N'S'))
>
> Hard Coded Script - -
> TRUNCATE TABLE Report_Promoted_Issue_Impact_Analysis
> ----
--
> INSERT INTO Report_Promoted_Issue_Impact_Analysis
> ( Retail_Type_id,
> Retail_Type,
> Market_Group_id,
> Market_Group_Desc,
> Outlet_id,
> Branch_id,
> EIS_Master_Title_id,
> Title_id_2,
> Issue_id,
> Title_Issue_Rank,
> Dist_Qty,
> Sales_Qty
> )
> SELECT O.Retail_Class_id AS Retail_Type_id, MG.Retail_Class_Desc AS
> Retail_Type, O.Market_Group_id, MG.Market_Group_Desc, OIH.Outlet_id,
> TIB.Branch_id, T.EIS_Master_Title_id, OIH.Title_id_2
,
> OIH.Issue_id, TIB.Title_Issue_Rank, OIH.Dist_Qty, OIH.Sales_Qty
> FROM Titles T RIGHT OUTER JOIN
> Outlet_Issue_History OIH ON T.Title_id_2 =
> OIH.Title_id_2 LEFT OUTER JOIN
> Titles_Issues_Branch TIB ON OIH.Issue_id =
> TIB.Issue_id AND OIH.Branch_id = TIB.Branch_id AND OIH.Title_id_2 =
> TIB.Title_id_2 LEFT OUTER JOIN
> Market_Groups MG RIGHT OUTER JOIN
> Outlets O ON MG.Retail_Class_Market_Group_id =
> O.Retail_Class_Market_Group_id ON OIH.Outlet_id = O.Outlet_id
> WHERE (O.Market_Group_id <> 'WW') AND (T.EIS_Master_Title_id = 'OK') A
ND
> (TIB.Title_Issue_Rank = 5) AND (O.Retail_Class_id IN (N'A', N'B', N'K', N'
S'))
>|||use "with recompile" option in the stored proc.. Its just a guess.
Can you run the query and SP together and see the execution plan.
I think for the SP the execution plan is built with the
Report_Promoted_Issue_Impact_Analysis table filled. So it prepares for an
execution plan with the table full, but it gets truncated...
Hope this helps.
--
"David C" wrote:

> Hi All,
> I am just in the process of putting a script that updates a reporting tabl
e
> into a stored procedure and have found that when run as a SP it takes sooo
o
> much longer. Running the script in QA with the "WHERE" clauses hard coded
> takes around 1 min, when I attempt to run via a SP with the "WHERE" clause
s
> replaced by variables (@.Title) that the user inputs it takes around 1 hour
.
> Below is a sample of both.
> Can anyone shed any light on why? What am I doing wrong?
> Thanks DC
> Stored Procedure - -
> CREATE PROCEDURE [dbo]. [sp_Report_Promoted_Issue_Impact_Analysi
s]
> @.MarketGroup nvarchar (3),
> @.Master_Title_id nvarchar (25),
> @.Current_Title_Issue_Rank int
> as
> TRUNCATE TABLE Report_Promoted_Issue_Impact_Analysis
> ----
--INSERT INTO Report_Promoted_Issue_Impact_Analysis
> ( Retail_Type_id,
> Retail_Type,
> Market_Group_id,
> Market_Group_Desc,
> Outlet_id,
> Branch_id,
> EIS_Master_Title_id,
> Title_id_2,
> Issue_id,
> Title_Issue_Rank,
> Dist_Qty,
> Sales_Qty
> )
> SELECT O.Retail_Class_id AS Retail_Type_id, MG.Retail_Class_Desc AS
> Retail_Type, O.Market_Group_id, MG.Market_Group_Desc, OIH.Outlet_id,
> TIB.Branch_id, T.EIS_Master_Title_id, OIH.Title_id_2
,
> OIH.Issue_id, TIB.Title_Issue_Rank, OIH.Dist_Qty, OIH.Sales_Qty
> FROM Titles T RIGHT OUTER JOIN
> Outlet_Issue_History OIH ON T.Title_id_2 =
> OIH.Title_id_2 LEFT OUTER JOIN
> Titles_Issues_Branch TIB ON OIH.Issue_id =
> TIB.Issue_id AND OIH.Branch_id = TIB.Branch_id AND OIH.Title_id_2 =
> TIB.Title_id_2 LEFT OUTER JOIN
> Market_Groups MG RIGHT OUTER JOIN
> Outlets O ON MG.Retail_Class_Market_Group_id =
> O.Retail_Class_Market_Group_id ON OIH.Outlet_id = O.Outlet_id
> WHERE (O.Market_Group_id <> @.MarketGroup) AND (T.EIS_Master_Title_id =
> @.Master_Title_id) AND (TIB.Title_Issue_Rank = @.Current_Title_Issue_Rank) A
ND
> (O.Retail_Class_id IN (N'A', N'B', N'K', N'S'))
>
> Hard Coded Script - -
> TRUNCATE TABLE Report_Promoted_Issue_Impact_Analysis
> ----
--
> INSERT INTO Report_Promoted_Issue_Impact_Analysis
> ( Retail_Type_id,
> Retail_Type,
> Market_Group_id,
> Market_Group_Desc,
> Outlet_id,
> Branch_id,
> EIS_Master_Title_id,
> Title_id_2,
> Issue_id,
> Title_Issue_Rank,
> Dist_Qty,
> Sales_Qty
> )
> SELECT O.Retail_Class_id AS Retail_Type_id, MG.Retail_Class_Desc AS
> Retail_Type, O.Market_Group_id, MG.Market_Group_Desc, OIH.Outlet_id,
> TIB.Branch_id, T.EIS_Master_Title_id, OIH.Title_id_2
,
> OIH.Issue_id, TIB.Title_Issue_Rank, OIH.Dist_Qty, OIH.Sales_Qty
> FROM Titles T RIGHT OUTER JOIN
> Outlet_Issue_History OIH ON T.Title_id_2 =
> OIH.Title_id_2 LEFT OUTER JOIN
> Titles_Issues_Branch TIB ON OIH.Issue_id =
> TIB.Issue_id AND OIH.Branch_id = TIB.Branch_id AND OIH.Title_id_2 =
> TIB.Title_id_2 LEFT OUTER JOIN
> Market_Groups MG RIGHT OUTER JOIN
> Outlets O ON MG.Retail_Class_Market_Group_id =
> O.Retail_Class_Market_Group_id ON OIH.Outlet_id = O.Outlet_id
> WHERE (O.Market_Group_id <> 'WW') AND (T.EIS_Master_Title_id = 'OK') A
ND
> (TIB.Title_Issue_Rank = 5) AND (O.Retail_Class_id IN (N'A', N'B', N'K', N'
S'))
>|||Hi again, I have fixed this problem by creating and inserting into a temp
table first, the processing time is now down to 1-2 min.
The execution plans were quite different between the hard coded and the
original stored procedure.
Thanks again
DC
"David C" wrote:

> Hi All,|||This will be good if there aren't too many concurrent users.
Temporary tabl should be avoided as much as possible because it uses lot of
I/O.
I would suggest you to find the real reason and fix the proc.
if not possible try to use a table variable instead of a temporary table.
-Omnibuzz

HELP! Err whn changing Stored Proc used

We had to make a change to a stored procedure and wanted to test out the
change. So made a test version of the sp (all done correctly and verified by
dba). I changed the name of the stored proc used in my report to be the test
version. I could run the sp fine from the data tab and it picked up the
change and displayed the correct results. When I wanted to run it from the
Preview tab ... different story. I tried EVERYTHING and couldnt get it to
not throw an error (SQL0444N Routine "*S_UPTIME" (specification name
"SQL050707202207020") is implemented with code in library or path"
...RES_UPTIME", function "DANTEST.SP_RPT_RES_UPTIME" which cannot be
accessed. Reason code: "4". SQLSTATE=42724. Permissions on the sp are
correct, grant was run, everything is correct from DBA standpoint. IN FACT
... if I create a NEW report and use that stored proc then I dont get an
error. SO ... where to look for the problem. I need to use the new stored
procedure in an existing report and I CANNOT REWRITE that just to use a new
stored procedure. Something is being remembered in the project I need to
make the change in and I dont know where to look. Thanks for any help.I had an issue similar to this and it seems to be that even though you can
run the dataset on the data tab after changing the stored procedure using
the ! it doesn't update the field list. If you look in the config for the
dataset after running it you still see the field list from the old SP, I
tried using the refresh fields button, rerunning the new SP several times
but the field list wouldn't update.
As a work around I just nuked the dataset and recreated a new one with the
same name and the new SP and then the new field list was available.
Hope this helps.
Peace,
Dan
"MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
news:63BCFB5C-8196-4622-8D3F-5A336CE07A96@.microsoft.com...
> We had to make a change to a stored procedure and wanted to test out the
> change. So made a test version of the sp (all done correctly and verified
> by
> dba). I changed the name of the stored proc used in my report to be the
> test
> version. I could run the sp fine from the data tab and it picked up the
> change and displayed the correct results. When I wanted to run it from
> the
> Preview tab ... different story. I tried EVERYTHING and couldnt get it to
> not throw an error (SQL0444N Routine "*S_UPTIME" (specification name
> "SQL050707202207020") is implemented with code in library or path"
> ...RES_UPTIME", function "DANTEST.SP_RPT_RES_UPTIME" which cannot be
> accessed. Reason code: "4". SQLSTATE=42724. Permissions on the sp are
> correct, grant was run, everything is correct from DBA standpoint. IN
> FACT
> ... if I create a NEW report and use that stored proc then I dont get an
> error. SO ... where to look for the problem. I need to use the new
> stored
> procedure in an existing report and I CANNOT REWRITE that just to use a
> new
> stored procedure. Something is being remembered in the project I need to
> make the change in and I dont know where to look. Thanks for any help.|||Dan,
I have had that issue before and dealt with it as you mention here. This
issue is different. It doesnt have to do with the fields list as the fields
as there - that isnt the problem. I dont understand the different behavior
between running the stored proc using the ! and running the report against it
using the preview. The report didnt change from before ...just the stored
proc ... shouldnt be such a problem!
"Dan Christjohn" wrote:
> I had an issue similar to this and it seems to be that even though you can
> run the dataset on the data tab after changing the stored procedure using
> the ! it doesn't update the field list. If you look in the config for the
> dataset after running it you still see the field list from the old SP, I
> tried using the refresh fields button, rerunning the new SP several times
> but the field list wouldn't update.
> As a work around I just nuked the dataset and recreated a new one with the
> same name and the new SP and then the new field list was available.
> Hope this helps.
> Peace,
> Dan
> "MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
> news:63BCFB5C-8196-4622-8D3F-5A336CE07A96@.microsoft.com...
> > We had to make a change to a stored procedure and wanted to test out the
> > change. So made a test version of the sp (all done correctly and verified
> > by
> > dba). I changed the name of the stored proc used in my report to be the
> > test
> > version. I could run the sp fine from the data tab and it picked up the
> > change and displayed the correct results. When I wanted to run it from
> > the
> > Preview tab ... different story. I tried EVERYTHING and couldnt get it to
> > not throw an error (SQL0444N Routine "*S_UPTIME" (specification name
> > "SQL050707202207020") is implemented with code in library or path"
> > ...RES_UPTIME", function "DANTEST.SP_RPT_RES_UPTIME" which cannot be
> > accessed. Reason code: "4". SQLSTATE=42724. Permissions on the sp are
> > correct, grant was run, everything is correct from DBA standpoint. IN
> > FACT
> > ... if I create a NEW report and use that stored proc then I dont get an
> > error. SO ... where to look for the problem. I need to use the new
> > stored
> > procedure in an existing report and I CANNOT REWRITE that just to use a
> > new
> > stored procedure. Something is being remembered in the project I need to
> > make the change in and I dont know where to look. Thanks for any help.
>
>

Help! Debugging Stored Procedure from query analyser - "Step through disabled"

Can anybody explain how to do debug a stored procedure from SQL Query Analyser.

When i tried opening Query Analyser and pressing F8 i am able to see Object Browser on left side, i selected the d/b and expanded it then i selected a stored procdure by right click of mouse. I selected "Debug".

It shows me alert msg "SQL Debugging may not work properly if you log on as 'Local System Account' while SQl server is configured to run as a service. You can open Event Viewer to see details." DO U WISH TO CONTINUE- I selected "YES"

I am able to see 3 split windows on right side and GO, Toggle, Untoggle are enabled BUT Step Into, Step Over, Step Out...Stop debugging are disabled at menu bar.

The 1st right split window shows the proc code, 2nd split window shows Local-Global-Callstack none of them shows any values(blank), 3rd split window shows records(result) and
@.RETURN_VALUE = 0 message

I had Toggled at each and every line of the procedure in 1st split window still it doesnt respond anything.

What might be the problem, how to solve it do i need to give any permissions.

i tried logging from wind Authentication and also from Sql Authentication (sa/sa), still same problem occurs. By the way i am using SQL Server 2000.

Pls help me out

Thanks in advance
Murali Kumar

I have exactly the same problem too. Please someone tell me what the problem is, tq.

HELP! -> Stored procedure and ASP.NET

I've created a SP in MS SQL Server 2005 Express. In ASP.NET I want to use this SP, but somehow my data is not added...what am I doing wrong?
Also: how can I efficiently debug a SP?

Here's my SP:

setANSI_NULLSON

setQUOTED_IDENTIFIERON

go

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTERPROCEDURE [dbo].[sp_InsertNewUser]

-- Add the parameters for the stored procedure here

(@.UserNamenchar(20),

@.pwdnchar(15),

@.Emailnchar(35),

@.IsMalebit,

@.BirthDatedatetime,

@.Countrysmallint,

@.PerfectSexnchar(1),

@.HasHTMLbit,

@.HasOffersbit,

@.HasNewsLetterbit

)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SETNOCOUNTON;

INSERTINTO dbo.tblMember(UserName,pwd,Email,IsMale,BirthDate,Country,PerfectSex,HasHTML,HasOffers,HasNewsLetter)

VALUES(@.UserName,@.pwd,@.Email,@.IsMale,@.BirthDate,@.Country,@.PerfectSex,@.HasHTML,@.HasOffers,@.HasNewsLetter)

END

Here's my code:
Dim BirthDate As DateTime = CDate(ddlMonth.SelectedValue + "-" & ddlDay.SelectedValue & "-" & ddlYear.SelectedValue)
Dim ActivationCode As String = Session.SessionID
Randomize()
Dim sPwd As String = CStr(Int((1000000 * Rnd()) + 100000))
Dim MyCommand As New SqlCommand("sp_InsertNewUser", DBConnection)
MyCommand.CommandType = Data.CommandType.StoredProcedure
MyCommand.Parameters.Add(New SqlParameter("@.UserName", tbUserName.Text))
MyCommand.Parameters.Add(New SqlParameter("@.pwd", sPwd))
MyCommand.Parameters.Add(New SqlParameter("@.Email", tbEmail.Text))
MyCommand.Parameters.Add(New SqlParameter("@.IsMale", ddlSex.SelectedValue))
MyCommand.Parameters.Add(New SqlParameter("@.BirthDate", BirthDate))
MyCommand.Parameters.Add(New SqlParameter("@.Country", ddlCountry.SelectedValue))
MyCommand.Parameters.Add(New SqlParameter("@.PerfectSex", ddlPerfectSex.SelectedValue))
MyCommand.Parameters.Add(New SqlParameter("@.HasHTML", ddlEmailSupport.SelectedValue))
MyCommand.Parameters.Add(New SqlParameter("@.HasOffers", cbProdServices.Checked))
MyCommand.Parameters.Add(New SqlParameter("@.HasNewsLetter", True))

You've missed the pertinent part of your code. After you add the parameters, are you actually executing your MyCommand? And, where is this code running and are you sure its running?|||

Im sure its running, as I stepped through the code.
I execute the command with ExecuteNonQuery.
the db connection opens and the statement appears to be executed.
I've put everything in a try catch statement but no errors occur! I've checked the field definitions and they are good as well...

but I take it you dont see any syntax errors?
And do you know how I can debug a SP?

Thanks!

|||

Peter Smith wrote:

Im sure its running, as I stepped through the code.
I execute the command with ExecuteNonQuery.
the db connection opens and the statement appears to be executed.

I guess we'll have to trust you on this one.

Peter Smith wrote:

but I take it you dont see any syntax errors?

I don't see any obvious syntax errors. Have you tried running your stored procedure from Query Analyzer?

Also, I guess you are using ASP.NET 1.1? The syntax you are using for parameters has been deprecated for ASP.NET 2.0 (you would now use the AddWithValue method). Personally I would use the constructor that specifies the data type, e.g.:
MyCommand.Parameters.Add(New SqlParameter("@.UserName", SqlDbType.NChar,20)).Value = tbUserName.Text
or, more simply:
MyCommand.Parameters.Add("@.UserName", SqlDbType.NChar,20).Value = tbUserName.Text

Peter Smith wrote:

And do you know how I can debug a SP?

Which IDE are you using? Visual Studio 2003? I know this includes a stored procedure debugging capability from within Server Explorer but I have not really used it myself (seeHOW TO: Debug Stored Procedures in Visual Studio .NET). I know there is a Transact-SQL debugger available from within Query Analyzer (seeStarting the Debugger in BOL) and I've only played around with that a bit. Unfortunately I debug stored procedures using old-fashioned manual methods: running the stored procedure in Query Analyzer, examining the results, tweaking the code if there are errors/unexpected results. I don't use a formal debugging tool.|||

I dont know how to use that quote thing ;)

"Also, I guess you are using ASP.NET 1.1? The syntax you are using for parameters has been deprecated for ASP.NET 2.0 (you would now use the AddWithValue method). Personally I would use the constructor that specifies the data type, e.g.:
MyCommand.Parameters.Add(New SqlParameter("@.UserName", SqlDbType.NChar,20)).Value = tbUserName.Text
or, more simply:
MyCommand.Parameters.Add("@.UserName", SqlDbType.NChar,20).Value = tbUserName.Text"

actually I use ASP.NET 2.0, I just didnt know I could also do it otherwise.
I will now use that simple constructor you provided, although I dont really see the purpose of specifying the datatype...is that for better debugging, or faster code or...I dont know...

I use Visual Web Developer 2005 Express and SQL Server 2005 Express...can I then still use the tools (query analyzer and server explorer) you mentioned?

Thanks!

HELP! - Cast from DBNull when there is Data

Hi,

I have built a few pages and a stored procedure and a class on the back of a SQL2000 dbase. and I get the following error:

Cast from type 'DBNull' to type 'String' is not valid.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.InvalidCastException: Cast from type 'DBNull' to type 'String' is not valid.

Source Error:

Line 111: Dim myWorkJobs As WorkJobsDATA = New WorkJobsDATALine 112:Line 113: myWorkJobs.CustomerID = CStr(parameterCustomerID.Value)Line 114: myWorkJobs.WorkID = CStr(parameterWorkID.Value)Line 115: myWorkJobs.DateOfQuote = CStr(parameterDateOfQuote.Value).Trim()

Source File:C:\Inetpub\wwwroot\Commerce\Components\WorkJobs.vb Line:113

My Database has 1 line of data (for testing) and all fields are populated. I am Querying a column called IndividualID which has a value of 3425243 at the moment. This is hardcoded in the aspx.vb at the moment.

ASPX VB:

Public Class WorkRequest
Inherits System.Web.UI.Page

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
End Sub

Private Sub btnEnter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEnter.Click

'for now, send this value (stored in dbase under individualID) to the querystring

Dim IndividualID As String = "3425243"

Response.Redirect("WorkRequestMain.aspx?IndividualID=" & IndividualID)
End Sub
End Class

COMPONENTS\WorkJobs.vb (This is the class file)

Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

Namespace ASPNET.StarterKit.Commerce

Public Class WorkJobsDATA

Public CustomerID As String
Public WorkID As String
Public DateOfQuote As String
Public QuoteAmount As String
Public Title As Decimal
Public FirstName As String
Public Surname As String
Public FirstLine As String
Public District As String
Public Town As String
Public Postcode As String
Public Telephone As String
Public Requirements As String
Public WorkRequired As String
Public EmailAddress As String

End Class

Public Class WorkJobs

Public Function GetWorkDetails(ByVal IndividualID As String) As WorkJobsDATA

Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim myCommand As SqlCommand = New SqlCommand("SP_PendingQuotes", myConnection)

' Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure

' Add Parameters to SPROC
Dim parameterIndividualID As SqlParameter = New SqlParameter("@.IndividualID", SqlDbType.NVarChar, 50)
parameterIndividualID.Value = IndividualID
myCommand.Parameters.Add(parameterIndividualID)

Dim parameterCustomerID As SqlParameter = New SqlParameter("@.CustomerID", SqlDbType.BigInt, 8)
parameterCustomerID.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterCustomerID)

Dim parameterWorkID As SqlParameter = New SqlParameter("@.WorkID", SqlDbType.NVarChar, 50)
parameterWorkID.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterWorkID)

Dim parameterDateOfQuote As SqlParameter = New SqlParameter("@.DateOfQuote", SqlDbType.DateTime, 8)
parameterDateOfQuote.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterDateOfQuote)

Dim parameterQuoteAmount As SqlParameter = New SqlParameter("@.QuoteAmount", SqlDbType.Money, 8)
parameterQuoteAmount.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterQuoteAmount)

Dim parameterTitle As SqlParameter = New SqlParameter("@.Title", SqlDbType.NVarChar, 50)
parameterTitle.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterTitle)

Dim parameterFirstName As SqlParameter = New SqlParameter("@.FirstName", SqlDbType.NVarChar, 50)
parameterFirstName.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterFirstName)

Dim parameterSurname As SqlParameter = New SqlParameter("@.Surname", SqlDbType.NVarChar, 50)
parameterSurname.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterSurname)

Dim parameterFirstLine As SqlParameter = New SqlParameter("@.FirstLine ", SqlDbType.NVarChar, 50)
parameterFirstLine.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterFirstLine)

Dim parameterDistrict As SqlParameter = New SqlParameter("@.District", SqlDbType.NVarChar, 50)
parameterDistrict.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterDistrict)

Dim parameterTown As SqlParameter = New SqlParameter("@.Town", SqlDbType.NVarChar, 50)
parameterTown.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterTown)

Dim parameterPostcode As SqlParameter = New SqlParameter("@.Postcode", SqlDbType.NVarChar, 50)
parameterPostcode.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterPostcode)

Dim parameterTelephone As SqlParameter = New SqlParameter("@.Telephone", SqlDbType.NVarChar, 50)
parameterTelephone.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterTelephone)

Dim parameterRequirements As SqlParameter = New SqlParameter("@.Requirements", SqlDbType.NVarChar, 3500)
parameterRequirements.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterRequirements)

Dim parameterWorkRequired As SqlParameter = New SqlParameter("@.WorkRequired", SqlDbType.NVarChar, 3500)
parameterWorkRequired.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterWorkRequired)

Dim parameterEmailAddress As SqlParameter = New SqlParameter("@.EmailAddress", SqlDbType.NVarChar, 100)
parameterEmailAddress.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterEmailAddress)

myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

parameterEmailAddress.Value.GetType()

Dim myWorkJobs As WorkJobsDATA = New WorkJobsDATA

myWorkJobs.CustomerID = CStr(parameterCustomerID.Value)
myWorkJobs.WorkID = CStr(parameterWorkID.Value)
myWorkJobs.DateOfQuote = CStr(parameterDateOfQuote.Value).Trim()
myWorkJobs.Title = CStr(parameterTitle.Value).Trim()
myWorkJobs.FirstName = CStr(parameterFirstName.Value).Trim()
myWorkJobs.Surname = CStr(parameterSurname.Value).Trim()
myWorkJobs.FirstLine = CStr(parameterFirstLine.Value).Trim()
myWorkJobs.District = CStr(parameterDistrict.Value).Trim()
myWorkJobs.Town = CStr(parameterTown.Value).Trim()
myWorkJobs.Postcode = CStr(parameterPostcode.Value).Trim()
myWorkJobs.Telephone = CStr(parameterTelephone.Value).Trim()
myWorkJobs.Requirements = CStr(parameterRequirements.Value).Trim()
myWorkJobs.WorkRequired = CStr(parameterWorkRequired.Value).Trim()
myWorkJobs.EmailAddress = CStr(parameterEmailAddress.Value).Trim()

Return myWorkJobs

End Function

End Class
End Namespace

And finally my stored procedure:

CREATE Procedure SP_PendingQuotes
(
@.IndividualID nvarchar,
@.CustomerID bigint OUTPUT,
@.WorkID nvarchar(50) OUTPUT,
@.DateOfQuote datetime OUTPUT,
@.QuoteAmount money OUTPUT,
@.Title nvarchar(50) OUTPUT,
@.FirstName nvarchar(50) OUTPUT,
@.Surname nvarchar(50) OUTPUT,
@.FirstLine nvarchar(50) OUTPUT,
@.District nvarchar(50) OUTPUT,
@.Town nvarchar(50) OUTPUT,
@.Postcode nvarchar(50) OUTPUT,
@.Telephone nvarchar(50) OUTPUT,
@.Requirements nvarchar(3500) OUTPUT,
@.WorkRequired nvarchar(3500) OUTPUT,
@.EmailAddress nvarchar(100) OUTPUT
)
AS

SELECT
@.IndividualID = IndividualID,
@.CustomerID = CustomerID,
@.WorkID = WorkID,
@.DateOfQuote = DateOfQuote,
@.QuoteAmount = QuoteAmount,
@.Title = Title,
@.FirstName = FirstName,
@.Surname = Surname,
@.FirstLine = FirstLine,
@.District = District,
@.Town = Town,
@.Postcode = Postcode,
@.Telephone = Telephone,
@.Requirements = Requirements,
@.WorkRequired = WorkRequired,
@.EmailAddress = EmailAddress

FROM
PendingQuotes

WHERE
IndividualID = @.IndividualID
GO

Any ideas anyone?

I appreciate this is a big amount of data, but if anyone wants to chat to me i'm available on MSN Messenger underwolvokid@.msn.com

The problem is in the stored procedure, you've defined @.IndividualID as nvarchar, specify the length, as it is defaulting to 1. Your query then returns no values because there is no record where IndividualID='3'|||

Call me muppett! It all becomes a blur after you go over and over everything a million times trying to find out whats wrong.

Thanks very much. It works now!

Help!

I have a procedure and i dont know why it doesnt work:
SET SERVEROUTPUT ON
CREATE OR REPLACE
PROCEDURE update_price(p_cutoff IN CHAR) AS
change CONSTANT Real := 0.9;
CURSOR c_upgrade_p IS
SELECT price FROM items
WHERE start_date = DATE(p_cutoff)
FOR UPDATE OF price;
BEGIN
FOR c_upgrade_rec IN c_upgrade_p LOOP
UPDATE items
SET price = price * 0.9;
WHERE CURRENT OF c_upgrade_p;
END LOOP;
COMMIT;
END;
/
any help wouldbe greatHello,

pls, post the error.

Best regards
Manfred Peter
Alligator Company Software GmbH
http://www.alligatorsql.com|||the error is simple that it doesnt compile- when i say show err it says the where statement does belong...

Help writing a stored procedure...

I'm developing a library and want to display the alphabets across the
screen. When a user clicks on one of the alphabets I want all titles
beginning with that letter to appear on the screen.

How would I write this stored procedure?

My table is called Titles

Fields:
Title ID
Titles

Thanks!

The select statement migth need some improvements, I think that would result in a full table-scan. Have a look at you execution plan and optimize as required.

CREATE PROCEDURE st_Ret_MyStuff(@.characterClickedas varChar(1))ASBEGINSET NOCOUNT ON;SELECT yourColumnsFROM titlesWHEREleft(titleCol, 1)=@.characterClicked;ENDGO

Cheers!

/Eskil

|||

Thanks that works!

Help woth stored procedure!

Hi,
Below is my stored procedure, for some reason it is not bringing back the
max contract start date although I have sopecified this. Any ideas would be
greatly appreciated. Will also include some data to show what I am trying
to do.
SELECT TOP 100 PERCENT dbo.tbl_referral_name.rn_id,
dbo.tbl_referral_name.rn_forename, dbo.tbl_referral_name.rn_surname,
dbo.tbl_referral_add.ra_add1,
dbo.tbl_referral_add.ra_add2,
dbo.tbl_referral_info.ri_closed, dbo.tbl_support.s_options,
dbo.tbl_officer.off_full_name,
MAX(dbo.tbl_support.s_contract_startdate) AS
Start_date, dbo.tbl_support_provider.sp_company
FROM dbo.tbl_referral_name INNER JOIN
dbo.tbl_referral_add ON dbo.tbl_referral_name.rn_id =
dbo.tbl_referral_add.ra_rn_id INNER JOIN
dbo.tbl_referral_info ON dbo.tbl_referral_add.ra_id =
dbo.tbl_referral_info.ri_ra_id INNER JOIN
dbo.tbl_support ON dbo.tbl_referral_info.ri_id =
dbo.tbl_support.s_ri_id INNER JOIN
dbo.tbl_officer ON dbo.tbl_referral_info.ri_off_id =
dbo.tbl_officer.off_id INNER JOIN
dbo.tbl_support_provider ON dbo.tbl_support.s_sp_id =
dbo.tbl_support_provider.sp_id
GROUP BY dbo.tbl_referral_info.ri_closed, dbo.tbl_support.s_options,
dbo.tbl_officer.off_full_name, dbo.tbl_support_provider.sp_company,
dbo.tbl_referral_add.ra_add2,
dbo.tbl_referral_add.ra_add1, dbo.tbl_referral_name.rn_surname,
dbo.tbl_referral_name.rn_forename,
dbo.tbl_referral_name.rn_id
ORDER BY dbo.tbl_referral_name.rn_surname
Damon Smith, 41 Seven Oaks, Caerau, 0, , Alan Jones, 10/03/2003,
Homeless Team
Damon Smith, 41 Seven Oaks, Caerau, 0, CA, Alan Jones, 06/10/2003, Tai
Troth
There are also other records included in the list but I am trying to get the
maximum contract start date for any where there are more than one instance
like the example above.
Appreciate the help
Thanks
DamonWhat does "not bringing back the max contract start date" mean? The
wrong date? No date? An error message? It's hard to help you unless you
post some code that will actually reproduce the problem and explain
what result you expect. See the following article which explains the
best way to post your problem for the group:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Hi,
Sorry about that. Basically when I include MAX on the contract_startdate
field it is having no effect on my results. What it should be doing is
bringing back the most recent contract_startdates if there is more than once
instance of a person and address but it is bringing back everything. i.e.
Damon Smith, 41 Seven Oaks, Caerau, 0, , Alan Jones, 10/03/2003,
Homeless Team
Damon Smith, 41 Seven Oaks, Caerau, 0, CA, Alan Jones, 06/10/2003, Tai
Troth - I want it to just bring this one back (most recent date) in all
cases where there is more than one instance of a person and address like
above.
Any ideas why this may be?
Damon
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108634350.485310.14970@.l41g2000cwc.googlegroups.com...
> What does "not bringing back the max contract start date" mean? The
> wrong date? No date? An error message? It's hard to help you unless you
> post some code that will actually reproduce the problem and explain
> what result you expect. See the following article which explains the
> best way to post your problem for the group:
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>|||You need something like the following. PLease bear in mind I typed this
and have not tested it. You need to do a subselect in the where clause
of the main query to determine the ID and maximum start date then limit
the records in the main criteria based on the subselect.
You do not need a Group By clause in the main query. I have also
forgotten whether MS SQL Server allows multiple fields for subselects
as I have shown. If it does not then concatenate the two fields into
one. eg dbo.tbl_referral_name.rn_id +
dbo.tbl_support.s_contract_startdate
SELECT dbo.tbl_referral_name.rn_id,
dbo.tbl_referral_name.rn_forename,
dbo.tbl_referral_name.rn_surname,
dbo.tbl_referral_add.ra_add1,
dbo.tbl_referral_add.ra_add2,
dbo.tbl_referral_info.ri_closed,
dbo.tbl_support.s_options,
dbo.tbl_officer.off_full_name,
dbo.tbl_support.s_contract_startdate AS Start_date,
dbo.tbl_support_provider.sp_company
FROM dbo.tbl_referral_name INNER JOIN
dbo.tbl_referral_add ON dbo.tbl_referral_name.rn_id =
dbo.tbl_referral_add.ra_rn_id INNER JOIN
dbo.tbl_referral_info ON dbo.tbl_referral_add.ra_id =
dbo.tbl_referral_info.ri_ra_id INNER JOIN
dbo.tbl_support ON dbo.tbl_referral_info.ri_id =
dbo.tbl_support.s_ri_id INNER JOIN
dbo.tbl_officer ON dbo.tbl_referral_info.ri_off_id =
dbo.tbl_officer.off_id INNER JOIN
dbo.tbl_support_provider ON dbo.tbl_support.s_sp_id =
dbo.tbl_support_provider.sp_id
WHERE dbo.tbl_referral_name.rn_id, dbo.tbl_support.s_contract_startdate
in ( select dbo.tbl_referral_name.rn_id,
max(dbo.tbl_support.s_contract_startdate)
from dbo.tbl_referral_name INNER JOIN
dbo.tbl_referral_add ON dbo.tbl_referral_name.rn_id =
dbo.tbl_referral_add.ra_rn_id INNER JOIN
dbo.tbl_referral_info ON dbo.tbl_referral_add.ra_id =
dbo.tbl_referral_info.ri_ra_id INNER JOIN
dbo.tbl_support ON dbo.tbl_referral_info.ri_id =
dbo.tbl_support.s_ri_id
group by dbo.tbl_referral_name.rn_id)
ORDER BY dbo.tbl_referral_name.rn_surname
Celtic Kiwi

Help woth cursor!

Hi,
I have a stored procedure which cycles through a select statement and loads
the results into a cursor. It then sends an email off for each result. I
was wondering if it was possible to group all the results into one email?
My stored procedure is below for reference:-
OPEN surveillance_cursor
FETCH NEXT FROM surveillance_cursor
INTO @.REG_NO, @.URN, @.OFFICER, @.REVIEW_DATE, @.OFFICER_EMAIL
-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0
IF @.OFFICER_EMAIL IS NOT NULL
BEGIN
select @.recipient = LTRIM(RTRIM(@.OFFICER_EMAIL))
select @.sbj = 'List of Renewal Dates'
select @.msg = 'Reg No:- ' + @.REG_NO + ', ' + 'URN:- ' + @.URN + ', ' +
'Officer:- ' + @.OFFICER + ', ' + 'Review Date:- ' + @.REVIEW_DATE
exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
@.message=@.msg
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM surveillance_cursor
INTO @.REG_NO, @.URN, @.OFFICER, @.REVIEW_DATE, @.OFFICER_EMAIL
END
CLOSE surveillance_cursor
DEALLOCATE surveillance_cursor
GO
Any help on this would be greatly appreciated
Thanks
Damon> I have a stored procedure which cycles through a select statement and
> loads the results into a cursor. It then sends an email off for each
> result. I was wondering if it was possible to group all the results into
> one email?
You'll have to be more specific. Do you mean one e-mail for each unique
@.officer_email, or one e-mail total?|||Create another variable e.g @.allMSG , keep adding the data for every
cursor and then do the "exec sp_sendmail after the cursor has finished.
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"Damon" <nonsense@.nononsense.com> wrote in message
news:2F4Ef.71406$zt1.64049@.newsfe5-gui.ntli.net...
> Hi,
> I have a stored procedure which cycles through a select statement and
loads
> the results into a cursor. It then sends an email off for each result. I
> was wondering if it was possible to group all the results into one email?
> My stored procedure is below for reference:-
> OPEN surveillance_cursor
> FETCH NEXT FROM surveillance_cursor
> INTO @.REG_NO, @.URN, @.OFFICER, @.REVIEW_DATE, @.OFFICER_EMAIL
> -- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
> WHILE @.@.FETCH_STATUS = 0
> IF @.OFFICER_EMAIL IS NOT NULL
> BEGIN
> select @.recipient = LTRIM(RTRIM(@.OFFICER_EMAIL))
> select @.sbj = 'List of Renewal Dates'
> select @.msg = 'Reg No:- ' + @.REG_NO + ', ' + 'URN:- ' + @.URN + ', ' +
> 'Officer:- ' + @.OFFICER + ', ' + 'Review Date:- ' + @.REVIEW_DATE
> exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
> @.message=@.msg
> -- This is executed as long as the previous fetch succeeds.
> FETCH NEXT FROM surveillance_cursor
> INTO @.REG_NO, @.URN, @.OFFICER, @.REVIEW_DATE, @.OFFICER_EMAIL
> END
> CLOSE surveillance_cursor
> DEALLOCATE surveillance_cursor
> GO
>
> Any help on this would be greatly appreciated
> Thanks
>
> Damon
>

Help with writing a Stored Procedure

Hi there,
I would be really grateful with some assistance with this, i am 100% new to
SQL & Stored Procedures. I'm building an ASP web application, i need to be
able to update two fields in two related tables & could really do with some
assistance. The tables are tbCompany and tbemployee. tbemployee is related t
o
tbCompany through a field in tbemployee named CompanyId (int). The fields
that i need to update are CompanySuspendedAccount in tbCompany
EmpCompAccoutSuspended in tbemployee. Needless to say there could be multipl
e
records in tbemployee that are related to the one record in tbCompany.
Ideally i will need to write two stored procedures, the first that updates
all records to yes and the second to no.
I would be really grateful for some assitance with this - Many thanks
GarethAssuming some thing that you didn=B4t posted a SP could bve something
like this for you:
CREATE PROCEDURE UPDSuspendCode
(
@.CompanyId INT,
@.Suspend BIT
)
AS
BEGIN
DECLARE @.Error INT
BEGIN TRANSACTION
UPDATE tbCompany
SET CompanySuspendedAccount =3D @.Suspend
Where CompanyId =3D @.CompanyId
SET @.ERROR =3D @.@.Error
UPDATE tbemployee
SET EmpCompAccoutSuspended =3D @.Suspend
Where CompanyId =3D @.CompanyId
SET @.ERROR =3D @.Error + @.@.Error
IF @.Error > 0
BEGIN
RAISERROR('And Error has occured during updating the suspend
status',16,1)
ROLLBACK
END
ELSE
COMMIT
END
HTH, Jens Suessmeyer.|||Thanks Jene, it looks good to me, presumably here i am setting the value to
Suspend? So i could use @.yes BIT for the suspending of the accounts and @.no
BIT to unsuspend. Both the fields CompanySuspendedAccount &
EmpCompAccoutSuspended are nvarchar so presumably it would need to be as
above unless i change the database? Thanks for your help
Gareth
"Jens" wrote:

> Assuming some thing that you didn′t posted a SP could bve something
> like this for you:
> CREATE PROCEDURE UPDSuspendCode
> (
> @.CompanyId INT,
> @.Suspend BIT
> )
> AS
> BEGIN
> DECLARE @.Error INT
> BEGIN TRANSACTION
>
> UPDATE tbCompany
> SET CompanySuspendedAccount = @.Suspend
> Where CompanyId = @.CompanyId
> SET @.ERROR = @.@.Error
>
> UPDATE tbemployee
> SET EmpCompAccoutSuspended = @.Suspend
> Where CompanyId = @.CompanyId
> SET @.ERROR = @.Error + @.@.Error
> IF @.Error > 0
> BEGIN
> RAISERROR('And Error has occured during updating the suspend
> status',16,1)
> ROLLBACK
> END
> ELSE
> COMMIT
> END
>
> HTH, Jens Suessmeyer.
>|||"@.yes BIT " Thats what my @.Suspend is for, you don=B4t need a @.Suspende
AND @.Unsuspense because the twi BITs can never evaluate to true (can be
either switch on AND off, right ).
You don=B4t have to change the database because BIT a Integer data type
with the constraint of being 1, 0, or NULL, so implicit conversion will
be used.
DECLARE @.Suspense BIT
DECLARE @.SomeColumn NVARCHAR(200)
SET @.Suspense =3D 1
SET @.SomeColumn =3D @.Suspense
Print @.SomeColumn
HTH, jens Suessmeyer.

Help with wildcard numeric search

I have constructed this stored procedure using union to let me find with
lots of flexibility. However one of the Parameters is a Numeric and I only
want to test if it's gtreater than zero. When I do an IF statement I get a
syntax error. I'm new to stored procs, this sort of thing would fly anywhere
else. Can someone help. The parameter I am trying to match on if greater
than zero is @.SuiteAncillaryID. If zero I want the complete result set so I
will need an else.
CREATE PROCEDURE [dbo].[pr_tblPersonAddress_Find_Limited]
@.Complex_Name varchar(60) = '%',
@.Building_Name varchar(60) = '%',
@.Location_Descriptor varchar(60) = '%',
@.House_Number_1 varchar(6) = '%',
@.Street_name varchar(45) = '%',
@.Locality_Name varchar(46) = '%',
@.Surname varchar(50) = '%',
@.FirstName varchar(50) = '%',
@.Position varchar(50) = '%',
@.TradingName varchar(255) = '%',
@.CompanyName varchar(255) = '%',
@.ACN char(20) = '%',
@.ABN char(20) = '%',
@.SiteName varchar(255) = '%',
@.SiteAncillaryID int =0,
@.ErrorCode int OUTPUT
AS
SET NOCOUNT ON
SELECT POIC From
(Select POIC
From tblpoic POICP
JOIN tblPerson PERSON
on POICP.PersonID=PERSON.PersonID
Where
PERSON.[Surname] like '%' + @.Surname + '%' AND
PERSON.[FirstName] like '%' + @.FirstName + '%' AND
PERSON.[Position] like '%' + @.Position + '%'
Union
Select POIC
From tblpoic POICO
JOIN tblOrganisation ORGANISATION
on POICO.OrgID=ORGANISATION.OrgID
Where
ORGANISATION.[TradingName] like '%' + @.TradingName + '%' AND
ORGANISATION.[CompanyName] like '%' + @.CompanyName + '%' AND
ORGANISATION.[ACN] like '%' + @.ACN + '%' AND
ORGANISATION.[ABN] like '%' + @.ABN + '%'
UNION
IF @.SiteAncillaryID>0
Begin
Select POIC
From tblpoic POICS
JOIN tblSite SITE
on POICS.SiteID=SITE.SiteID
WHERE
SITE.[SiteName]>@.SiteName
and SITE.[SiteName]=@.siteAncillaryID
Union
END
Select POIC
From tblpoic POICA
JOIN tbladdress ADDRESS
ON POICA.AddressID = ADDRESS.AddressID
where
ADDRESS.[Complex_Name] like '%' + @.Complex_Name + '%' AND
ADDRESS.[Building_Name] like '%' + @.Building_Name + '%' AND
ADDRESS.[Location_Descriptor] like '%' + @.Location_Descriptor + '%' AND
ADDRESS.[House_Number_1] like '%' + @.House_Number_1 + '%' AND
ADDRESS.[Street_name] like '%' + @.Street_name + '%' AND
ADDRESS.[Locality_Name] like '%' + @.Locality_Name + '%' ) as TEMP
Order by POIC
-- Get the Error Code for the statement just executed.
SELECT @.ErrorCode=@.@.ERROR
-- Get the IDENTITY value for the row just inserted.
GOJust include it in the where clause - if it's <= 0 then you will get an empt
y
resultset for that part of the union.
UNION
Select POIC
From tblpoic POICS
JOIN tblSite SITE
on POICS.SiteID=SITE.SiteID
WHERE
SITE.[SiteName]>@.SiteName
and SITE.[SiteName]=@.siteAncillaryID
and @.SiteAncillaryID>0|||If statements are Transact-SQL, not SQL. A Stored Proc is written in
Transact-SQL language, which is an MS SQL Server priprietary Programming
language for control flow and Variable declaration, whch understands standar
d
SQL..
Standard SQL is the embedded statements that "talk" to the query processor,
the Selects, Updates, Inserts, and Deletes... The T=SQL constructions, (If,
While, Begin End, Declare @.Variable, etc.) can be used only Outside of SQL
Statements not inside of one.
btw, the SQL equivilent (closest equivilent) to IF is Case statement.
Check it out in Books On Line.
"Shoeman" wrote:

> I have constructed this stored procedure using union to let me find with
> lots of flexibility. However one of the Parameters is a Numeric and I only
> want to test if it's gtreater than zero. When I do an IF statement I get a
> syntax error. I'm new to stored procs, this sort of thing would fly anywhe
re
> else. Can someone help. The parameter I am trying to match on if greater
> than zero is @.SuiteAncillaryID. If zero I want the complete result set so
I
> will need an else.
>
> CREATE PROCEDURE [dbo].[pr_tblPersonAddress_Find_Limited]
> @.Complex_Name varchar(60) = '%',
> @.Building_Name varchar(60) = '%',
> @.Location_Descriptor varchar(60) = '%',
> @.House_Number_1 varchar(6) = '%',
> @.Street_name varchar(45) = '%',
> @.Locality_Name varchar(46) = '%',
> @.Surname varchar(50) = '%',
> @.FirstName varchar(50) = '%',
> @.Position varchar(50) = '%',
> @.TradingName varchar(255) = '%',
> @.CompanyName varchar(255) = '%',
> @.ACN char(20) = '%',
> @.ABN char(20) = '%',
> @.SiteName varchar(255) = '%',
> @.SiteAncillaryID int =0,
> @.ErrorCode int OUTPUT
>
> AS
> SET NOCOUNT ON
> SELECT POIC From
> (Select POIC
> From tblpoic POICP
> JOIN tblPerson PERSON
> on POICP.PersonID=PERSON.PersonID
> Where
> PERSON.[Surname] like '%' + @.Surname + '%' AND
> PERSON.[FirstName] like '%' + @.FirstName + '%' AND
> PERSON.[Position] like '%' + @.Position + '%'
> Union
> Select POIC
> From tblpoic POICO
> JOIN tblOrganisation ORGANISATION
> on POICO.OrgID=ORGANISATION.OrgID
> Where
> ORGANISATION.[TradingName] like '%' + @.TradingName + '%' AND
> ORGANISATION.[CompanyName] like '%' + @.CompanyName + '%' AND
> ORGANISATION.[ACN] like '%' + @.ACN + '%' AND
> ORGANISATION.[ABN] like '%' + @.ABN + '%'
> UNION
> IF @.SiteAncillaryID>0
> Begin
> Select POIC
> From tblpoic POICS
> JOIN tblSite SITE
> on POICS.SiteID=SITE.SiteID
> WHERE
> SITE.[SiteName]>@.SiteName
>
> and SITE.[SiteName]=@.siteAncillaryID
> Union
> END
>
>
> Select POIC
> From tblpoic POICA
> JOIN tbladdress ADDRESS
> ON POICA.AddressID = ADDRESS.AddressID
> where
> ADDRESS.[Complex_Name] like '%' + @.Complex_Name + '%' AND
> ADDRESS.[Building_Name] like '%' + @.Building_Name + '%' AND
> ADDRESS.[Location_Descriptor] like '%' + @.Location_Descriptor + '%' AN
D
> ADDRESS.[House_Number_1] like '%' + @.House_Number_1 + '%' AND
> ADDRESS.[Street_name] like '%' + @.Street_name + '%' AND
> ADDRESS.[Locality_Name] like '%' + @.Locality_Name + '%' ) as TEMP
> Order by POIC
>
> -- Get the Error Code for the statement just executed.
> SELECT @.ErrorCode=@.@.ERROR
> -- Get the IDENTITY value for the row just inserted.
> GO
>
>|||> Standard SQL is the embedded statements that "talk" to the query processor,
> the Selects, Updates, Inserts, and Deletes... The T=SQL constructions, (If
,
> While, Begin End, Declare @.Variable, etc.) can be used only Outside of SQ
L
> Statements not inside of one.
So you think that t-sql is use to provide control of flow for sql?
Brings up a few interesting questions - like what is sql?
Actually t-sql is the version of sql implemented on sql server which
includes differences and extensions to any ansi standard.
All control of flow, variable declaration, select statements are t-sql.
p.s. A case statement is usually what people want when they try to use an if
in a select statement but I don't think it is in this case.|||Nigel,
As I'm sure you understand, I'm Just trying to explain why "If" cannot be
used inside a "SQL statement". There is a distinction between those
Statements which Select, Insert, update or delete data, and the control flow
statements which they are embedded in...
"If" is a T-SQL Control-FLow construct, and is not useable within a "SQL"
Statement (a Select/Inert/Update/Delete). Although I cannot find
specifically where this distinction is made in the defintitions, or what the
exact words are to describe it, it is exactly pertinent to the issue the
individual was having...
"Nigel Rivett" wrote:

> So you think that t-sql is use to provide control of flow for sql?
> Brings up a few interesting questions - like what is sql?
> Actually t-sql is the version of sql implemented on sql server which
> includes differences and extensions to any ansi standard.
> All control of flow, variable declaration, select statements are t-sql.
> p.s. A case statement is usually what people want when they try to use an
if
> in a select statement but I don't think it is in this case.|||I'll agree that if is a control of flow statement.
It is also a t-sql statement just like select.
The problem is your assertion that select is an sql statement rather than a
t-sql statement. The distinction is made in defining control of flow
statements.
If you are looking for something that says if is t-sql and select sql then
you won't find it because it's not correct.
You can say that select is part of the ansi standard sql definition and is
also implemented in t-sql.
"CBretana" wrote:
> Nigel,
> As I'm sure you understand, I'm Just trying to explain why "If" cannot
be
> used inside a "SQL statement". There is a distinction between those
> Statements which Select, Insert, update or delete data, and the control fl
ow
> statements which they are embedded in...
> "If" is a T-SQL Control-FLow construct, and is not useable within a "SQL"
> Statement (a Select/Inert/Update/Delete). Although I cannot find
> specifically where this distinction is made in the defintitions, or what t
he
> exact words are to describe it, it is exactly pertinent to the issue the
> individual was having...
>
> "Nigel Rivett" wrote:
>|||Yes, I agree... My semantic mistake was using the Acronym "SQL" to refer to
just those statements which modify or retrieve Data... I'm not sure there is
a phrase or acronym which makes this distinction, but "SQL Statements"
seemed a viable choice, given the issue the user was experiencing...
But you are correct, Thanks.
"Nigel Rivett" wrote:
> I'll agree that if is a control of flow statement.
> It is also a t-sql statement just like select.
> The problem is your assertion that select is an sql statement rather than
a
> t-sql statement. The distinction is made in defining control of flow
> statements.
> If you are looking for something that says if is t-sql and select sql then
> you won't find it because it's not correct.
> You can say that select is part of the ansi standard sql definition and is
> also implemented in t-sql.
> "CBretana" wrote:
>|||Since you're arguing semantics, it would be more accurate to say that the IF
statement is not part of the DML language elements in mickeysoft's T-SQL
implementation and thus cannot be used in a DML statement like Select,
Insert, Update or Delete.
Thomas
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:3385D49B-2CBB-4EC3-B8F1-3CB31E95E39E@.microsoft.com...
> Yes, I agree... My semantic mistake was using the Acronym "SQL" to refer
> to
> just those statements which modify or retrieve Data... I'm not sure there
> is
> a phrase or acronym which makes this distinction, but "SQL Statements"
> seemed a viable choice, given the issue the user was experiencing...
> But you are correct, Thanks.
>
> "Nigel Rivett" wrote:
>

Help with WHERE Clause in Stored Procedure

Hi,

I have an sp with the following WHERE clause

@.myqarep varchar(50)

SELECT tblCase.qarep FROM dbo.tblCase

WHERE dbo.tblCase.qarep = CASE @.myqarep WHEN '<All>' THEN
dbo.tblCase.qarep ELSE @.myqarep

@.myqarep is returned from a combo box (ms access)...the user either
picks a qarep from the combo box or they leave the default which is
'<All>'

they problem i'm having is that if the record's value for
dbo.tblCase.qarep is null...the record does not show up in the
results...but i need it to

any help is appreciated.

thanks
Paul... WHERE qarep = @.myqarep
OR @.myqarep = '<All>'

--
David Portas
SQL Server MVP
--|||thanks for the quick response...i'll give it a try!

David Portas wrote:
> ... WHERE qarep = @.myqarep
> OR @.myqarep = '<All>'
> --
> David Portas
> SQL Server MVP
> --

Help with WHERE Clause

hi guys help please..I have a stored procedure below that basically retrieve data from tables and under my WHERE clause I want to execute conditions depending on the value of "@.FilterBy" variable. If @.FilterBy is equal to "Pending" then execute a conditions under "IF @.FilterBy = 'Pending'" and if it equals to 'Delivered' then execute conditions under IF @.FilterBy = 'Delivered'. But unfortunately I can't figure out how to do that my stored procedure below just wont work becuase it has an error "Incorrect syntax near the keyword 'IF'"...Any help guys on how to solve this problem? Thanks in advance!

USE [CFREEDB]
GO
/****** Object: StoredProcedure [dbo].[usp_DELIVERY_GET] Script Date: 09/01/2007 12:03:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_DELIVERY_GET]
@.FilterBy varchar(20),
@.CustomerID int,
@.FromDate datetime,
@.ToDate datetime
AS
BEGIN

SELECT DISTINCT Delivery.CustomerID, Customer.Customer_LastName, Customer.Customer_MiddleName, Customer.Customer_FirstName,
Customer.Customer_Company, Customer.Customer_Address, Customer.Customer_ContactNo, Customer.Customer_Discount, Customer_Balance

FROM CFREE_Delivery Delivery
INNER JOIN CFREE_Customer Customer
ON Delivery.CustomerID = Customer.CustomerID
WHERE
IF @.FilterBy = 'Pending'
BEGIN
Delivery.IsDeleted <> 1 AND
Delivery.IsDelivered IS NULL AND
Delivery.IsRemitted IS NULL AND
Delivery_Date BETWEEN @.FromDate AND @.ToDate
END
IF @.FilterBy = 'Delivered'
BEGIN
Delivery.IsDeleted <> 1 AND
Delivery.IsDelivered IS NOT NULL AND
Delivery.IsRemitted IS NOT NULL AND
Delivery_Date BETWEEN @.FromDate AND @.ToDate
END

ORDER BY Customer.Customer_LastName, Customer.Customer_FirstName, Customer.Customer_MiddleName

ENDWHERE Delivery.IsDeleted <> 1
AND Delivery_Date BETWEEN @.FromDate AND @.ToDate
AND (
( @.FilterBy = 'Pending'
AND Delivery.IsDelivered IS NULL
AND Delivery.IsRemitted IS NULL
)
OR ( @.FilterBy = 'Delivered'
AND Delivery.IsDelivered IS NOT NULL
AND Delivery.IsRemitted IS NOT NULL
)
)

Help with using OUTPUT parameter

Hi all,
I have a procedure (sp1) that needs to execute another procedure (sp2); need
to set a variable (@.pass) in sp1 with a value generated within sp2. SP1 is
called, executed from within ASP. As it is currently I get a value of 0 [zero]
in the table.
--
Code for SP1:
--
CREATE PROCEDURE dbo.usp_AddAffiliateApplication
(
@.web varchar(200), @.url varchar(200), @.cat int, @.first varchar(100), @.last
varchar(100),
@.email varchar(100), @.area char(3), @.phone char(7), @.structure int, @.pay
varchar(200),
@.add1 varchar(200), @.add2 varchar(200), @.city varchar(100), @.state int,
@.zip char(5),
@.country int, @.pass varchar(20), @.ip varchar(15), @.send char(1) OUTPUT
)
AS
SET NOCOUNT ON
SET @.send = 'N'
IF @.url NOT IN ( SELECT a.applicantURL
FROM dbo.affiliateApplications a )
BEGIN
SET @.send = 'Y'
--
EXEC @.pass = dbo.usp_GeneratePassword
--
DECLARE @.TRAN1 varchar(50)
SELECT @.TRAN1 = 'AddAffiliateApplication'
BEGIN TRAN @.TRAN1
WITH MARK 'Insert-AAA'
--
INSERT INTO dbo.affiliateApplications (applicantWebsiteName, applicantURL,
affiliateCategoryID,
applicantFirstName,
applicantLastName, applicantEmailAddress,
applicantAreaCode,
applicantPhoneNumber, businessStructureID,
applicantPayName,
applicantAddress1, applicantAddress2,
applicantCity, stateID,
applicantZipCode, countryID,
applicantPassword, applicantIP,
applicantDateApplied)
VALUES (@.web, @.url, @.cat, @.first, @.last, @.email, @.area, @.phone,
@.structure, @.pay, @.add1,
@.add2, @.city, @.state, @.zip, @.country, @.pass, @.ip, GETDATE())
--
IF @.@.ERROR = 0
BEGIN
COMMIT TRAN AddAffiliateApplication
END
ELSE
BEGIN
ROLLBACK TRAN AddAffiliateApplication
END
END
ELSE
BEGIN
DECLARE @.days int
SELECT @.days = DATEDIFF(d, aa.applicantDateApplied, GETDATE())
FROM dbo.affiliateApplications aa
WHERE (aa.applicantURL = @.url) AND ((aa.
applicantApprovalStatus = 'N')
OR (aa.
applicantApprovalStatus = 'P'))
IF @.days <= 90
BEGIN
SET @.send = 'X'
END
ELSE
BEGIN
SET @.send = 'Y'
DECLARE @.TRAN2 varchar(50)
SELECT @.TRAN2 = 'AddAffiliateApplication'
BEGIN TRAN @.TRAN2
WITH MARK 'Update-AAA'
--
UPDATE dbo.affiliateApplications
SET applicantDateApplied = GETDATE(), applicantApprovalStatus = 'P'
WHERE (applicantURL = @.url)
--
IF @.@.ERROR = 0
BEGIN
COMMIT TRAN AddAffiliateApplication
END
ELSE
BEGIN
ROLLBACK TRAN AddAffiliateApplication
END
END
END
GO
--
Code for SP2:
--
CREATE PROCEDURE dbo.usp_GeneratePassword
(
@.passLength int = 8,
@.passType char(7) = 'complex'
)
AS
BEGIN
DECLARE @.password varchar(20)
DECLARE @.type tinyint
DECLARE @.bitmap char(12)
SET @.password = ''
SET @.bitmap = 'aeiouy!#$-_+'
WHILE @.passLength > 0
BEGIN
IF @.passType = 'simple'
BEGIN
IF (@.passLength%2) = 0
SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
(RAND() * (11)),0)),1)
ELSE
SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
END
ELSE
BEGIN
SET @.type = ROUND(1 + (RAND() * (3)),0)
IF @.type = 1
SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
ELSE IF @.type = 2
SET @.password = @.password + CHAR(ROUND(65 + (RAND() * (25)),0))
ELSE IF @.type = 3
SET @.password = @.password + CHAR(ROUND(48 + (RAND() * (9)),0))
ELSE IF @.type = 4
SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
(RAND() * (11)),0)),1)
END
SET @.passLength = @.passLength - 1
END
SELECT @.password OUTPUT
END
GO
Other than this issue, in which I guess I'm not going about setting or
getting the output parameter in the right way, each of the SPs work properly
by themselves.
Any help or suggestions appreciated.
Thanks.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1> EXEC @.pass = dbo.usp_GeneratePassword
This statement will assign the stored procedure return code to the @.pass
variable. However you are returning the password generated by
usp_GeneratePassword as a result set (SELECT statement).
A stored procedure return code (returned via a RETURN statement) is an
integer usually used to indicate success or failure. Data can be returned
via OUTPUT parameters or in a result set. It's easier to process OUTPUT
parameters in Transact-SQL rather than a result set.
To return the password value as an output parameter, change the
usp_AddAffiliateApplication code to:
EXEC dbo.usp_GeneratePassword @.password = @.pass OUTPUT
Remove the SELECT from usp_GeneratePassword and change the header as
follows:
CREATE PROCEDURE dbo.usp_GeneratePassword
(
@.passLength int = 8,
@.passType char(7) = 'complex',
@.password varchar(20) OUTPUT
)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"thegekkster" <u6631@.uwe> wrote in message news:613dacf27c8fd@.uwe...
> Hi all,
> I have a procedure (sp1) that needs to execute another procedure (sp2);
> need
> to set a variable (@.pass) in sp1 with a value generated within sp2. SP1 is
> called, executed from within ASP. As it is currently I get a value of 0
> [zero]
> in the table.
> --
> Code for SP1:
> --
> CREATE PROCEDURE dbo.usp_AddAffiliateApplication
> (
> @.web varchar(200), @.url varchar(200), @.cat int, @.first varchar(100),
> @.last
> varchar(100),
> @.email varchar(100), @.area char(3), @.phone char(7), @.structure int, @.pay
> varchar(200),
> @.add1 varchar(200), @.add2 varchar(200), @.city varchar(100), @.state int,
> @.zip char(5),
> @.country int, @.pass varchar(20), @.ip varchar(15), @.send char(1) OUTPUT
> )
> AS
> SET NOCOUNT ON
> SET @.send = 'N'
> IF @.url NOT IN ( SELECT a.applicantURL
> FROM dbo.affiliateApplications a )
> BEGIN
> SET @.send = 'Y'
> --
> EXEC @.pass = dbo.usp_GeneratePassword
> --
> DECLARE @.TRAN1 varchar(50)
> SELECT @.TRAN1 = 'AddAffiliateApplication'
> BEGIN TRAN @.TRAN1
> WITH MARK 'Insert-AAA'
> --
> INSERT INTO dbo.affiliateApplications (applicantWebsiteName,
> applicantURL,
> affiliateCategoryID,
> applicantFirstName,
> applicantLastName, applicantEmailAddress,
> applicantAreaCode,
> applicantPhoneNumber, businessStructureID,
> applicantPayName,
> applicantAddress1, applicantAddress2,
> applicantCity, stateID,
> applicantZipCode, countryID,
> applicantPassword, applicantIP,
> applicantDateApplied)
> VALUES (@.web, @.url, @.cat, @.first, @.last, @.email, @.area, @.phone,
> @.structure, @.pay, @.add1,
> @.add2, @.city, @.state, @.zip, @.country, @.pass, @.ip, GETDATE())
> --
> IF @.@.ERROR = 0
> BEGIN
> COMMIT TRAN AddAffiliateApplication
> END
> ELSE
> BEGIN
> ROLLBACK TRAN AddAffiliateApplication
> END
> END
> ELSE
> BEGIN
> DECLARE @.days int
> SELECT @.days = DATEDIFF(d, aa.applicantDateApplied, GETDATE())
> FROM dbo.affiliateApplications aa
> WHERE (aa.applicantURL = @.url) AND ((aa.
> applicantApprovalStatus = 'N')
> OR (aa.
> applicantApprovalStatus = 'P'))
> IF @.days <= 90
> BEGIN
> SET @.send = 'X'
> END
> ELSE
> BEGIN
> SET @.send = 'Y'
> DECLARE @.TRAN2 varchar(50)
> SELECT @.TRAN2 = 'AddAffiliateApplication'
> BEGIN TRAN @.TRAN2
> WITH MARK 'Update-AAA'
> --
> UPDATE dbo.affiliateApplications
> SET applicantDateApplied = GETDATE(), applicantApprovalStatus = 'P'
> WHERE (applicantURL = @.url)
> --
> IF @.@.ERROR = 0
> BEGIN
> COMMIT TRAN AddAffiliateApplication
> END
> ELSE
> BEGIN
> ROLLBACK TRAN AddAffiliateApplication
> END
> END
> END
> GO
> --
> Code for SP2:
> --
> CREATE PROCEDURE dbo.usp_GeneratePassword
> (
> @.passLength int = 8,
> @.passType char(7) = 'complex'
> )
> AS
> BEGIN
> DECLARE @.password varchar(20)
> DECLARE @.type tinyint
> DECLARE @.bitmap char(12)
> SET @.password = ''
> SET @.bitmap = 'aeiouy!#$-_+'
> WHILE @.passLength > 0
> BEGIN
> IF @.passType = 'simple'
> BEGIN
> IF (@.passLength%2) = 0
> SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
> (RAND() * (11)),0)),1)
> ELSE
> SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
> END
> ELSE
> BEGIN
> SET @.type = ROUND(1 + (RAND() * (3)),0)
> IF @.type = 1
> SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
> ELSE IF @.type = 2
> SET @.password = @.password + CHAR(ROUND(65 + (RAND() * (25)),0))
> ELSE IF @.type = 3
> SET @.password = @.password + CHAR(ROUND(48 + (RAND() * (9)),0))
> ELSE IF @.type = 4
> SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
> (RAND() * (11)),0)),1)
> END
> SET @.passLength = @.passLength - 1
> END
> SELECT @.password OUTPUT
> END
> GO
> Other than this issue, in which I guess I'm not going about setting or
> getting the output parameter in the right way, each of the SPs work
> properly
> by themselves.
> Any help or suggestions appreciated.
> Thanks.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1|||Thanks, Dan.
This helped clarify the issue for me, and your suggestions worked perfectly.
Appreciate your help...
Dan Guzman wrote:
>> EXEC @.pass = dbo.usp_GeneratePassword
>This statement will assign the stored procedure return code to the @.pass
>variable. However you are returning the password generated by
>usp_GeneratePassword as a result set (SELECT statement).
>A stored procedure return code (returned via a RETURN statement) is an
>integer usually used to indicate success or failure. Data can be returned
>via OUTPUT parameters or in a result set. It's easier to process OUTPUT
>parameters in Transact-SQL rather than a result set.
>To return the password value as an output parameter, change the
>usp_AddAffiliateApplication code to:
>EXEC dbo.usp_GeneratePassword @.password = @.pass OUTPUT
>Remove the SELECT from usp_GeneratePassword and change the header as
>follows:
>CREATE PROCEDURE dbo.usp_GeneratePassword
>(
> @.passLength int = 8,
> @.passType char(7) = 'complex',
> @.password varchar(20) OUTPUT
>)
>> Hi all,
>[quoted text clipped - 180 lines]
>> Thanks.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1|||I'm glad I was able to help you out.
--
Dan Guzman
SQL Server MVP
"thegekkster via SQLMonster.com" <u6631@.uwe> wrote in message
news:61403d2080344@.uwe...
> Thanks, Dan.
> This helped clarify the issue for me, and your suggestions worked
> perfectly.
> Appreciate your help...
>