February 1, 2012 at 2:13 am
Hi guys,
I'm really struggling with this one - I generally use SQL2K8 and i'm used to the format on it. Unfortunately the company i'm working for are on 2K5 and they're not quite ready to migrate (to my dismay).
Basically the stored procedure I am trying to create creates the stored procedure that will allow the user to search on any fields within an already created table.
Here is what i've written so far - any help would be EXTREMELY appreciated as we are on a pretty tight schedule:
USE SAAM
GO
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'sp_Search')
AND type in (N'P', N'PC'))
DROP PROCEDURE sp_Search
GO
CREATE PROCEDURE sp_Search (
@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),
@debug bit = 0)
AS
SET NOCOUNT ON;
DECLARE @sql NVARCHAR(4000);
DECLARE @ParameterDefinition NVARCHAR(4000)
DECLARE @SubscriberParameter NVARCHAR (4000)
;
SELECT@ParameterDefinition = '
@SAAMSubscriberParameter INT,
@SAAMSubscriberParameter INT
';
SELECT@sql = N'
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
';
IF @SubId IS NOT NULL
AND SubId = @SAAMSubscriberParameter ';
IF @CustId IS NOT NULL
AND CustID = @SAAMSubscriberParameter ';
IF @Reference IS NOT NULL
AND Reference = @SAAMSubscriberParameter ';
IF @CustOrderNo IS NOT NULL
AND CustOrderNo = @SAAMSubscriberParameter ';
IF @Title IS NOT NULL
AND Title = @SAAMSubscriberParameter ';
IF @FirstName IS NOT NULL
AND FirstName = @SAAMSubscriberParameter ';
IF @MiddleInitial IS NOT NULL
AND MiddleInitial = @SAAMSubscriberParameter ';
IF @Surname IS NOT NULL
AND Surname = @SAAMSubscriberParameter ';
IF @Suffix IS NOT NULL
AND Suffix = @SAAMSubscriberParameter ';
IF @JobTitleDescription IS NOT NULL
AND JobTitleDescription = @SAAMSubscriberParameter ';
IF @JobTitleCategory IS NOT NULL
AND JobTitleCategory = @SAAMSubscriberParameter ';
IF @Department IS NOT NULL
AND Department = @SAAMSubscriberParameter ';
IF @Company IS NOT NULL
AND Company = @SAAMSubscriberParameter ';
IF @Add1 IS NOT NULL
AND Add1 = @SAAMSubscriberParameter ';
IF @Add2 IS NOT NULL
AND Add2 = @SAAMSubscriberParameter ';
IF @Add3 IS NOT NULL
AND Add3 = @SAAMSubscriberParameter ';
IF @Add4 IS NOT NULL
AND Add4 = @SAAMSubscriberParameter ';
IF @City IS NOT NULL
AND City = @SAAMSubscriberParameter ';
IF @County IS NOT NULL
AND County = @SAAMSubscriberParameter ';
IF @PostZip IS NOT NULL
AND PostZip = @SAAMSubscriberParameter ';
IF @Country IS NOT NULL
AND Country = @SAAMSubscriberParameter ';
IF @HomeTelNo IS NOT NULL
AND HomeTelNo = @SAAMSubscriberParameter ';
IF @WorkTelNo IS NOT NULL
AND WorkTelNo = @SAAMSubscriberParameter ';
IF @FaxNo IS NOT NULL
AND FaxNo = @SAAMSubscriberParameter ';
IF @Email IS NOT NULL
AND Email = @SAAMSubscriberParameter ';
IF @mobile IS NOT NULL
AND Mobile = @SAAMSubscriberParameter ';
IF @Industry IS NOT NULL
AND Industry = @SAAMSubscriberParameter ';
IF @RenewelEmail IS NOT NULL
AND RenewellEmail = @SAAMSubscriberParameter ';
IF @EmailRenewel IS NOT NULL
AND GoneAwayReason = @SAAMSubscriberParameter ';
IF @sms IS NOT NULL
AND SMS = @SAAMubscriberParameter ';
IF @Gender IS NOT NULL
AND Gender = @SAAMSubscriberParameter ';
IF @Language IS NOT NULL
AND Language = @SAAMSubscriberParameter ';
IF @JobLevel IS NOT NULL
AND JobLevel = @SAAMSubscriberParameter ';
IF @IsGift IS NOT NULL
AND IsGift = @SAAMSubscriberParameter ';
IF @Giver IS NOT NULL
AND Giver = @SAAMSubscriberParameter ';
IF @debug = 1
PRINT @sql
EXEC sp_executeSQL
@sql,
@ParameterDefinition,
@SubscriberParameter,
@SubscriberParameter;
GO
EXEC SAAMSubscriber @debug = 1,
@Subid = 1,
@CustID = -1,
@Reference = '',
@CustOrderNo = '',
@Title = '',
@FirstName = '',
@MiddleInitial = '',
@Surname '',
@Suffix '',
@JobTitleDescription = '',
@JobTitleCategory = 1,
@Department = 'Systems',
@Company = '',
@Add1 = '',
@Add2 = '',
@Add3 = '',
@Add4 = '',
@City = '',
@County = '',
@PostZip = '',
@Country = 1,
@HomeTelNo = '',
@WorkTelNo = '',
@FaxNo = '',
@Email = '',
@mobile = '',
@Industry = 1,
@RenewelEMail = '',
@EMailRenewel = 1,
@sms = 1,
@Gender = 1,
@Language = 1,
@JobLevel = 1,
@IsGift = 0,
@Giver = ''
Thank You 🙂
February 1, 2012 at 2:19 am
What is there about your proc which works on 2008 and not 2005?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 1, 2012 at 2:22 am
Sorry I'm kind of new to Forums (first post) - I haven't tried the sp on 2008 so I wouldn't know.
February 1, 2012 at 2:28 am
I did go in yesterday and try a different method and declared the following (below) to use as @parameters i.e SubId = @SubIdParamter but no luck there either:
@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),
@debug bit = 0)
getting rid of the @SubscriberParameter from all the sections:
IF @SubId IS NOT NULL
AND SubId = @SAAMSubscriberParameter ';
and replacing with:
IF @SubId IS NOT NULL
AND SubId = @SubIdParameter ';
but No luck!
:angry:
February 1, 2012 at 2:28 am
No problem.
I was responding to this statement:
I generally use SQL2K8 and i'm used to the format on it. Unfortunately the company i'm working for are on 2K5 and they're not quite ready to migrate (to my dismay).
Which suggests that you would be happy doing this job on 2008 but not 2005 - I was trying to determine the reason.
If that is a red herring, can you be more specific about what your problem is - based on what you have posted, I am not sure what is going wrong.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 1, 2012 at 2:33 am
That's fine - I'm just glad I'm getting such quick responses from you! 😀
I'm recieving the following error when I run the SQL:
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ''.
a really simple error but I can't seem to find it!
I can't believe I'm stumped by syntax!! :crazy:
February 1, 2012 at 2:50 am
Once you've built your dynamic SQL string, try doing a straight
select @sql
before executing it. That should allow you to see where things are going wrong.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 1, 2012 at 2:53 am
I think the syntax error is because you've wrapped your parameter declarations in parantheses. However, I'm confused. You say your stored procedure creates a stored procedure, but there is no CREATE PROCEDURE statement in your dynamic SQL. And the @SAAMSubscriberParameter parameter isn't declared anywhere. Finally, you shouldn't give a user stored procedure a name beginning with "sp_", since it's reserved for system stored procedures.
Regardless of that, are you sure you need to use dynamic SQL at all? It seems like just a simple SELECT statement. You can get rid of all those IF clauses something like this:
...
AND FirstName = COALESCE(@FirstName,FirstName)
...
Here's some further reading in case you need convincing why you should avoid dynamic SQL where possible. Pay particular attention to the dangers of SQL injection.
http://www.sommarskog.se/dynamic_sql.html
John
February 1, 2012 at 3:24 am
Thanks for everyone's help - it's certainly solved a lot of problems!
I'm now getting the message 'command(s) completed succesfully which I feel is a good start!! 😀
In response to John,
'You say your stored procedure creates a stored procedure, but there is no CREATE PROCEDURE statement in your dynamic SQL. And the @SAAMSubscriberParameter parameter isn't declared anywhere. Finally, you shouldn't give a user stored procedure a name beginning with "sp_", since it's reserved for system stored procedures.
Regardless of that, are you sure you need to use dynamic SQL at all? It seems like just a simple SELECT statement. You can get rid of all those IF clauses something like this:'
Thanks for the advice on the naming of the sp. The reason I thought I needed to use a stored procedure is that this sp is going to link to a website where the user will be able to bring up a subscriber (or any other data) from any of the other fields on the page without having to enter data into every field - if this can be done with a select GREAT(!) but my programmer boss recommended Dynamic SQL.
Any further advice would be much appreciated! 🙂
February 1, 2012 at 3:31 am
I would seriously recommend that your boss read through the link I posted. It's heavy going, but rewarding. If he stills thinks dynamic SQL is the way to go, that's fine. But since you're putting this on a website, you really do need to think about how you're going to avoid SQL injection. I've given you a way to do it in a (fairly) simple SELECT statement, so give it a try. It may be that I've misunderstood or overlooked one of your requirements and that my suggestion won't therefore be suitable. But you won't know that until you try. Good luck!
John
February 1, 2012 at 3:36 am
Thanks John! 🙂
February 1, 2012 at 3:47 am
So as a (very simple) select, could I create a stored proc using a select satement such as the following without using Dynamic SQL (i've read up on the risks - not good), and would this allow the user to search on any field and bring up another page giving the results (for example everybody with the first name 'Jon') - just for the record I'm programming in Flex if that makes any difference? Example below:
SELECT FirstName
FROM SAAMSubscriber
WHERE 1 = 1
AND FirstName = COALESCE(@FirstName,FirstName)
February 1, 2012 at 3:48 am
Programming the Website that is - it pulls the data from the DB i'm creating.
February 1, 2012 at 3:55 am
Flex? Never heard of it. But create a stored procedure on the SQL server and it's easy to call from any language. The code you posted looks as if it'll work, but you'll only know by doing a few tests. And then doing a few more (and so on).
John
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply