February 1, 2012 at 4:01 am
I managed to eplain that completey wrong - how embarrasing!
Here's a better explanation:
Adobe Flex is a software development kit (SDK) released by Adobe Systems for the development and deployment of cross-platform rich Internet applications based on the Adobe Flash platform. Flex applications can be written using Adobe Flash Builder or by using the freely available Flex compiler from Adobe.
The release in March 2004 by Macromedia included an SDK, an Integrated development environment (IDE), and a Java EE integration application known as Flex Data Services. Since Adobe purchased Macromedia in 2005, subsequent releases of Flex no longer require a license for Flex Data Services, which has become a separate product rebranded as LiveCycle Data Services. An alternative to Adobe LiveCycle Data Services is BlazeDS, an open-source project that started with code contributed in 2007 by Adobe.
In February 2008, Adobe released the Flex 3 SDK under the open source Mozilla Public License and so Flex applications can be developed using any standard IDE, for example Eclipse.
🙂
February 1, 2012 at 5:21 am
Hi John,
I ran the select with the following query:
SELECT SubId,
CustId,
Reference,
CustOrderNo,
Title,
FirstName,
MiddleInitial,
Surname,
Suffix,
JobTitleDescription,
JobTitleCategory,
Department,
Company,
Add1,
Add2,
Add3,
Add4,
City,
County,
PostZip,
Country,
HomeTelNo,
WorkTelNo,
FaxNo,
Email,
Mobile,
Industry,
RenewelEMail,
EMailRenewel,
SMS,
Gender,
[Language],
JobLevel,
IsGift,
Giver
FROM SAAMSubscriber
WHERE 1 = 1
AND SubId = COALESCE(@SubId,SubId)
AND CustId = COALESCE(@CustId,CustId)
AND Reference = COALESCE(@Reference,Reference)
AND CustOrderNo = COALESCE(@CustOrderNo,CustOrderNo)
AND Title = COALESCE(@Title,Title)
AND FirstName = COALESCE(@FirstName,FirstName)
AND MiddleInitial = COALESCE(@MiddleInitial,MiddleInitial)
AND Surname = COALESCE(@Surname,Surname)
AND Suffix = COALESCE(@Suffix,Suffix)
AND JobTitleDescription = COALESCE(@JobTitleDescription,JobTitleDescription)
AND JobTitleCategory = COALESCE(@JobTitleCategory,JobTitleCategory)
AND Department = COALESCE(@Department,Department)
AND Company = COALESCE(@Company,Company)
AND Add1 = COALESCE(@Add1,Add1)
AND Add2 = COALESCE(@Add2,Add2)
AND Add3 = COALESCE(@Add3,Add3)
AND Add4 = COALESCE(@Add4,Add4)
AND City = COALESCE(@City,City)
AND County = COALESCE(@County,County)
AND PostZip = COALESCE(@PostZip,PostZip)
AND Country = COALESCE(@Country,Country)
AND HomeTelNo = COALESCE(@HomeTelNo,HomeTelNo)
AND WorkTelNo = COALESCE(@WorkTelNo,WorkTelNo)
AND FaxNo = COALESCE(@FaxNo,FaxNo)
AND Email = COALESCE(@Email,Email)
AND Mobile = COALESCE(@Mobile,Mobile)
AND Industry = COALESCE(@Industry,Industry)
AND RenewelEMail = COALESCE(@RenewelEMail,RenewelEMail)
AND EMailRenewel = COALESCE(@EMailRenewel,EMailRenewel)
AND SMS = COALESCE(@SMS,SMS)
AND Gender = COALESCE(@Gender,Gender)
AND [Language] = COALESCE(@Language,[Language])
AND JobLevel = COALESCE(@JobLevel,JobLevel)
AND IsGift = COALESCE(@IsGift,IsGift)
AND Giver = COALESCE(@Giver,Giver)
(declaring @SubId and @CustId as they don't allow nulls)
and I recieved the following error:
Msg 137, Level 15, State 2, Line 38
Must declare the scalar variable "@SubId".
Any ideas?
February 1, 2012 at 5:45 am
Ignore that last statement - I got it working, but I ran the following query and it gave me the following results:
DECLARE
@SubId int,
@CustId int,
@Reference varchar(50),
@CustOrderNo varchar(50),
@Title varchar(50),
@FirstName varchar(50),
@MiddleInitial varchar(50),
@Surname varchar(50),
@Suffix varchar(10),
@JobTitleDescription varchar(80),
@JobTitleCategory int,
@Department varchar(50),
@Company varchar(80),
@Add1 varchar(50),
@Add2 varchar(50),
@Add3 varchar(50),
@Add4 varchar(50),
@City varchar(50),
@County varchar(50),
@PostZip varchar(50),
@Country int,
@HomeTelNo varchar(50),
@WorkTelNo varchar(50),
@FaxNo varchar(50),
@Email varchar(50),
@mobile varchar(50),
@Industry int,
@RenewelEMail varchar(200),
@EMailRenewel smallint,
@sms smallint,
@Gender smallint,
@Language smallint,
@JobLevel smallint,
@IsGift smallint,
@Giver varchar(50)
SET NOCOUNT ON;
SET XACT_ABORT ON;
SELECT SubId,
CustId,
Reference,
CustOrderNo,
Title,
FirstName,
MiddleInitial,
Surname,
Suffix,
JobTitleDescription,
JobTitleCategory,
Department,
Company,
Add1,
Add2,
Add3,
Add4,
City,
County,
PostZip,
Country,
HomeTelNo,
WorkTelNo,
FaxNo,
Email,
Mobile,
Industry,
RenewelEMail,
EMailRenewel,
SMS,
Gender,
[Language],
JobLevel,
IsGift,
Giver
FROM SAAMSubscriber
WHERE 1 = 1
AND SubId = COALESCE(@SubId,SubId)
AND CustId = COALESCE(@CustId,CustId)
AND Reference = COALESCE(@Reference,Reference)
AND CustOrderNo = COALESCE(@CustOrderNo,CustOrderNo)
AND Title = COALESCE(@Title,Title)
AND FirstName = COALESCE(@FirstName,FirstName)
AND MiddleInitial = COALESCE(@MiddleInitial,MiddleInitial)
AND Surname = COALESCE(@Surname,Surname)
AND Suffix = COALESCE(@Suffix,Suffix)
AND JobTitleDescription = COALESCE(@JobTitleDescription,JobTitleDescription)
AND JobTitleCategory = COALESCE(@JobTitleCategory,JobTitleCategory)
AND Department = COALESCE(@Department,Department)
AND Company = COALESCE(@Company,Company)
AND Add1 = COALESCE(@Add1,Add1)
AND Add2 = COALESCE(@Add2,Add2)
AND Add3 = COALESCE(@Add3,Add3)
AND Add4 = COALESCE(@Add4,Add4)
AND City = COALESCE(@City,City)
AND County = COALESCE(@County,County)
AND PostZip = COALESCE(@PostZip,PostZip)
AND Country = COALESCE(@Country,Country)
AND HomeTelNo = COALESCE(@HomeTelNo,HomeTelNo)
AND WorkTelNo = COALESCE(@WorkTelNo,WorkTelNo)
AND FaxNo = COALESCE(@FaxNo,FaxNo)
AND Email = COALESCE(@Email,Email)
AND Mobile = COALESCE(@Mobile,Mobile)
AND Industry = COALESCE(@Industry,Industry)
AND RenewelEMail = COALESCE(@RenewelEMail,RenewelEMail)
AND EMailRenewel = COALESCE(@EMailRenewel,EMailRenewel)
AND SMS = COALESCE(@SMS,SMS)
AND Gender = COALESCE(@Gender,Gender)
AND [Language] = COALESCE(@Language,[Language])
AND JobLevel = COALESCE(@JobLevel,JobLevel)
AND IsGift = COALESCE(@IsGift,IsGift)
AND Giver = COALESCE(@Giver,Giver)
Gave me the results:
SubId|CustId
3 |-1
etc...
any idea why as I don't remember specifying those parameters?
Thanks,
Jon
3
February 1, 2012 at 5:53 am
I forgot to mention there is other data in the table - it starts at 1 and increments by 1!
February 1, 2012 at 5:56 am
Hi everone,
I created the select statement that John advides but when I ran it i recieved the following results (after query):
DECLARE
@SubId int,
@CustId int,
@Reference varchar(50),
@CustOrderNo varchar(50),
@Title varchar(50),
@FirstName varchar(50),
@MiddleInitial varchar(50),
@Surname varchar(50),
@Suffix varchar(10),
@JobTitleDescription varchar(80),
@JobTitleCategory int,
@Department varchar(50),
@Company varchar(80),
@Add1 varchar(50),
@Add2 varchar(50),
@Add3 varchar(50),
@Add4 varchar(50),
@City varchar(50),
@County varchar(50),
@PostZip varchar(50),
@Country int,
@HomeTelNo varchar(50),
@WorkTelNo varchar(50),
@FaxNo varchar(50),
@Email varchar(50),
@mobile varchar(50),
@Industry int,
@RenewelEMail varchar(200),
@EMailRenewel smallint,
@sms smallint,
@Gender smallint,
@Language smallint,
@JobLevel smallint,
@IsGift smallint,
@Giver varchar(50)
SET NOCOUNT ON;
SET XACT_ABORT ON;
SELECT SubId,
CustId,
Reference,
CustOrderNo,
Title,
FirstName,
MiddleInitial,
Surname,
Suffix,
JobTitleDescription,
JobTitleCategory,
Department,
Company,
Add1,
Add2,
Add3,
Add4,
City,
County,
PostZip,
Country,
HomeTelNo,
WorkTelNo,
FaxNo,
Email,
Mobile,
Industry,
RenewelEMail,
EMailRenewel,
SMS,
Gender,
[Language],
JobLevel,
IsGift,
Giver
FROM SAAMSubscriber
WHERE 1 = 1
AND SubId = COALESCE(@SubId,SubId)
AND CustId = COALESCE(@CustId,CustId)
AND Reference = COALESCE(@Reference,Reference)
AND CustOrderNo = COALESCE(@CustOrderNo,CustOrderNo)
AND Title = COALESCE(@Title,Title)
AND FirstName = COALESCE(@FirstName,FirstName)
AND MiddleInitial = COALESCE(@MiddleInitial,MiddleInitial)
AND Surname = COALESCE(@Surname,Surname)
AND Suffix = COALESCE(@Suffix,Suffix)
AND JobTitleDescription = COALESCE(@JobTitleDescription,JobTitleDescription)
AND JobTitleCategory = COALESCE(@JobTitleCategory,JobTitleCategory)
AND Department = COALESCE(@Department,Department)
AND Company = COALESCE(@Company,Company)
AND Add1 = COALESCE(@Add1,Add1)
AND Add2 = COALESCE(@Add2,Add2)
AND Add3 = COALESCE(@Add3,Add3)
AND Add4 = COALESCE(@Add4,Add4)
AND City = COALESCE(@City,City)
AND County = COALESCE(@County,County)
AND PostZip = COALESCE(@PostZip,PostZip)
AND Country = COALESCE(@Country,Country)
AND HomeTelNo = COALESCE(@HomeTelNo,HomeTelNo)
AND WorkTelNo = COALESCE(@WorkTelNo,WorkTelNo)
AND FaxNo = COALESCE(@FaxNo,FaxNo)
AND Email = COALESCE(@Email,Email)
AND Mobile = COALESCE(@Mobile,Mobile)
AND Industry = COALESCE(@Industry,Industry)
AND RenewelEMail = COALESCE(@RenewelEMail,RenewelEMail)
AND EMailRenewel = COALESCE(@EMailRenewel,EMailRenewel)
AND SMS = COALESCE(@SMS,SMS)
AND Gender = COALESCE(@Gender,Gender)
AND [Language] = COALESCE(@Language,[Language])
AND JobLevel = COALESCE(@JobLevel,JobLevel)
AND IsGift = COALESCE(@IsGift,IsGift)
AND Giver = COALESCE(@Giver,Giver)
;
Gave me the results:
SubId|CustId
3 |-1
4 |-1
etc for the table...
any idea why as I don't remember specifying those parameters?
Thanks,
Jon
February 1, 2012 at 6:16 am
Jon
I'm afraid I don't know what you mean. Please will you post a CREATE TABLE statement along with some sample data from the table? One problem I can see with the query is if some of the columns in your table are nullable. You will, in that case, be doing a NULL = NULL comparison, which equates to False, since if a value is unknown, you can't say that it's equal to some other unknown value.
John
February 1, 2012 at 10:07 am
Hi John, sory for the last reply,
Here's my query (with the create table etc)
it appears to work now but I would appreciate it if you could have a look?
Best Regards
February 2, 2012 at 4:08 am
Hi John,
will the following query allow me search on any of the fields in the table?
DECLARE
@SubId int,
@CustId int,
@Reference varchar(50),
@CustOrderNo varchar(50),
@Title varchar(50),
@FirstName varchar(50),
@MiddleInitial varchar(50),
@Surname varchar(50),
@Suffix varchar(10),
@JobTitleDescription varchar(80),
@JobTitleCategory int,
@Department varchar(50),
@Company varchar(80),
@Add1 varchar(50),
@Add2 varchar(50),
@Add3 varchar(50),
@Add4 varchar(50),
@City varchar(50),
@County varchar(50),
@PostZip varchar(50),
@Country int,
@HomeTelNo varchar(50),
@WorkTelNo varchar(50),
@FaxNo varchar(50),
@Email varchar(50),
@mobile varchar(50),
@Industry int,
@RenewelEMail varchar(200),
@EMailRenewel smallint,
@sms smallint,
@Gender smallint,
@Language smallint,
@JobLevel smallint,
@IsGift smallint,
@Giver varchar(50)
SET NOCOUNT ON;
SET XACT_ABORT ON;
SELECT SubId,
CustId,
Reference,
CustOrderNo,
Title,
FirstName,
MiddleInitial,
Surname,
Suffix,
JobTitleDescription,
JobTitleCategory,
Department,
Company,
Add1,
Add2,
Add3,
Add4,
City,
County,
PostZip,
Country,
HomeTelNo,
WorkTelNo,
FaxNo,
Email,
Mobile,
Industry,
RenewelEMail,
EMailRenewel,
SMS,
Gender,
[Language],
JobLevel,
IsGift,
Giver
FROM SubscribersTest
WHERE 1 = 1
AND SubId = COALESCE(@SubId,SubId)
AND CustId = COALESCE(@CustId,CustId)
AND Reference = COALESCE(@Reference,Reference)
AND CustOrderNo = COALESCE(@CustOrderNo,CustOrderNo)
AND Title = COALESCE(@Title,Title)
AND FirstName = COALESCE(@FirstName,FirstName)
AND MiddleInitial = COALESCE(@MiddleInitial,MiddleInitial)
AND Surname = COALESCE(@Surname,Surname)
AND Suffix = COALESCE(@Suffix,Suffix)
AND JobTitleDescription = COALESCE(@JobTitleDescription,JobTitleDescription)
AND JobTitleCategory = COALESCE(@JobTitleCategory,JobTitleCategory)
AND Department = COALESCE(@Department,Department)
AND Company = COALESCE(@Company,Company)
AND Add1 = COALESCE(@Add1,Add1)
AND Add2 = COALESCE(@Add2,Add2)
AND Add3 = COALESCE(@Add3,Add3)
AND Add4 = COALESCE(@Add4,Add4)
AND City = COALESCE(@City,City)
AND County = COALESCE(@County,County)
AND PostZip = COALESCE(@PostZip,PostZip)
AND Country = COALESCE(@Country,Country)
AND HomeTelNo = COALESCE(@HomeTelNo,HomeTelNo)
AND WorkTelNo = COALESCE(@WorkTelNo,WorkTelNo)
AND FaxNo = COALESCE(@FaxNo,FaxNo)
AND Email = COALESCE(@Email,Email)
AND Mobile = COALESCE(@Mobile,Mobile)
AND Industry = COALESCE(@Industry,Industry)
AND RenewelEMail = COALESCE(@RenewelEMail,RenewelEMail)
AND EMailRenewel = COALESCE(@EMailRenewel,EMailRenewel)
AND SMS = COALESCE(@SMS,SMS)
AND Gender = COALESCE(@Gender,Gender)
AND [Language] = COALESCE(@Language,[Language])
AND JobLevel = COALESCE(@JobLevel,JobLevel)
AND IsGift = COALESCE(@IsGift,IsGift)
AND Giver = COALESCE(@Giver,Giver)
Also did you get my create table query yesterday?
Regards,
Jon
February 2, 2012 at 4:10 am
Clearly you didnt get the query as I didn't send it accross:
/****** Object: Table [dbo].[Subscriberstest] Script Date: 02/01/2012 13:26:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE SubscribersTest(
[SubId] [int] IDENTITY(1,1) NOT NULL,
[CustId] [int] NOT NULL,
[Reference] [varchar](50) NULL,
[CustOrderNo] [varchar](50) NULL,
[Title] [varchar](50) NULL,
[FirstName] [varchar](50) NULL,
[MiddleInitial] [varchar](50) NULL,
[Surname] [varchar](50) NULL,
[Suffix] [varchar](10) NULL,
[JobTitleDescription] [varchar](80) NULL,
[JobTitleCategory] [int] NULL,
[Department] [varchar](50) NULL,
[Company] [varchar](80) NULL,
[Add1] [varchar](50) NULL,
[Add2] [varchar](50) NULL,
[Add3] [varchar](50) NULL,
[Add4] [varchar](50) NULL,
[City] [varchar](50) NULL,
[County] [varchar](50) NULL,
[PostZip] [varchar](50) NULL,
[Country] [int] NULL,
[HomeTelNo] [varchar](50) NULL,
[WorkTelNo] [varchar](50) NULL,
[FaxNo] [varchar](50) NULL,
[Email] [varchar](50) NULL,
[Mobile] [varchar](50) NULL,
[Industry] [int] NULL,
[RenewelEMail] [varchar](200) NULL,
[EMailRenewel] [smallint] NULL,
[SMS] [smallint] NULL,
[Gender] [smallint] NULL,
[Language] [smallint] NULL,
[JobLevel] [smallint] NULL,
[IsGift] [smallint] NULL,
[Giver] [varchar](50) NULL,
CONSTRAINT [PK_SubscribersTest_1] PRIMARY KEY CLUSTERED
(
[SubId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
INSERT INTO [SubscribersTest] (CustID,Reference)
values (-1, 99999)
INSERT INTO [SubscribersTest] (CustID,Reference)
values (-2, 11111)
INSERT INTO [SubscribersTest] (CustID,Reference)
values (-3,'') ;
select * from subscriberstest
;
create proc sp_testing AS
DECLARE
@SubId int,
@CustId int,
@Reference varchar(50),
@CustOrderNo varchar(50),
@Title varchar(50),
@FirstName varchar(50),
@MiddleInitial varchar(50),
@Surname varchar(50),
@Suffix varchar(10),
@JobTitleDescription varchar(80),
@JobTitleCategory int,
@Department varchar(50),
@Company varchar(80),
@Add1 varchar(50),
@Add2 varchar(50),
@Add3 varchar(50),
@Add4 varchar(50),
@City varchar(50),
@County varchar(50),
@PostZip varchar(50),
@Country int,
@HomeTelNo varchar(50),
@WorkTelNo varchar(50),
@FaxNo varchar(50),
@Email varchar(50),
@mobile varchar(50),
@Industry int,
@RenewelEMail varchar(200),
@EMailRenewel smallint,
@sms smallint,
@Gender smallint,
@Language smallint,
@JobLevel smallint,
@IsGift smallint,
@Giver varchar(50)
SET NOCOUNT ON;
SET XACT_ABORT ON;
SELECT SubId,
CustId,
Reference,
CustOrderNo,
Title,
FirstName,
MiddleInitial,
Surname,
Suffix,
JobTitleDescription,
JobTitleCategory,
Department,
Company,
Add1,
Add2,
Add3,
Add4,
City,
County,
PostZip,
Country,
HomeTelNo,
WorkTelNo,
FaxNo,
Email,
Mobile,
Industry,
RenewelEMail,
EMailRenewel,
SMS,
Gender,
[Language],
JobLevel,
IsGift,
Giver
FROM SubscribersTest
WHERE 1 = 1
AND SubId = COALESCE(@SubId,SubId)
AND CustId = COALESCE(@CustId,CustId)
AND Reference = COALESCE(@Reference,Reference)
AND CustOrderNo = COALESCE(@CustOrderNo,CustOrderNo)
AND Title = COALESCE(@Title,Title)
AND FirstName = COALESCE(@FirstName,FirstName)
AND MiddleInitial = COALESCE(@MiddleInitial,MiddleInitial)
AND Surname = COALESCE(@Surname,Surname)
AND Suffix = COALESCE(@Suffix,Suffix)
AND JobTitleDescription = COALESCE(@JobTitleDescription,JobTitleDescription)
AND JobTitleCategory = COALESCE(@JobTitleCategory,JobTitleCategory)
AND Department = COALESCE(@Department,Department)
AND Company = COALESCE(@Company,Company)
AND Add1 = COALESCE(@Add1,Add1)
AND Add2 = COALESCE(@Add2,Add2)
AND Add3 = COALESCE(@Add3,Add3)
AND Add4 = COALESCE(@Add4,Add4)
AND City = COALESCE(@City,City)
AND County = COALESCE(@County,County)
AND PostZip = COALESCE(@PostZip,PostZip)
AND Country = COALESCE(@Country,Country)
AND HomeTelNo = COALESCE(@HomeTelNo,HomeTelNo)
AND WorkTelNo = COALESCE(@WorkTelNo,WorkTelNo)
AND FaxNo = COALESCE(@FaxNo,FaxNo)
AND Email = COALESCE(@Email,Email)
AND Mobile = COALESCE(@Mobile,Mobile)
AND Industry = COALESCE(@Industry,Industry)
AND RenewelEMail = COALESCE(@RenewelEMail,RenewelEMail)
AND EMailRenewel = COALESCE(@EMailRenewel,EMailRenewel)
AND SMS = COALESCE(@SMS,SMS)
AND Gender = COALESCE(@Gender,Gender)
AND [Language] = COALESCE(@Language,[Language])
AND JobLevel = COALESCE(@JobLevel,JobLevel)
AND IsGift = COALESCE(@IsGift,IsGift)
AND Giver = COALESCE(@Giver,Giver)
EXEC sp_testing @SubId=1
February 2, 2012 at 5:05 am
Jon
I take it you didn't run the final EXEC statement, since that generates an error? The reason is that you have DECLAREd all your variables instead of making them parameters to your stored procedure. Should be easy to fix - remove the DECLARE statement and move the AS statement down. Check out the CREATE PROCEDURE syntax if you're not sure.
Also, please will you describe in words what you want the procedure to return. For instance, if you ran the following:
EXEC @sp_testing
@Surname = 'Jones'
,@Add4 = 'Camberwick Green'
what would you expect to get back?
John
February 2, 2012 at 8:01 am
You must absolutely NOT do this if you expect any type of performance and concurrency:
AND SubId = COALESCE(@SubId,SubId)
AND CustId = COALESCE(@CustId,CustId)
AND Reference = COALESCE(@Reference,Reference)
AND CustOrderNo = COALESCE(@CustOrderNo,CustOrderNo)
This will lead to table scans for EVERY call of the sproc. Dynamic SQL is by far the best solution here because you can only include the inputs for which you get values and you will have explicit values for best optimization. This becomes even more important if you start (conditionally) joining to other tables for filtering because you can completely avoid the joins if you don't filter on a column of that table (and don't need another column on output).
I have used dynamic SQL for open-ended searches like this at a number of clients over the years and made non-functional systems fly. FIVE orders of magnitude perf improvement is possible!!
Be SURE TO GUARD AGAINST SQL INJECTION IF YOU DO THIS!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply