Concatenate Where Clause for Stored Proc using ASP

Is it doable to create a stored procedure without a where portion and then in my ASP designate the where clause by concatinating that string at the end using ASP?

so in othe words, something like this where I'm using ASP to finish out the where clause of my SP:


strSQL_Wherepart = "thisid =" & rID

strSQL = "sp_mystored_proc @SomeID=" & TheID & " WHERE " & strSQL_Wherepart

objConnection.Execute(strSQL)

then in my stored proc I just wouldn't specify a where clause.  Is this possible?
LVL 1
dba123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Partha MandayamTechnical DirectorCommented:
IT would be better to generate the whole sql statement in asp if your whereclause is going to be dynamic.
What is your where clause? Can it not be coded with just several parameters? Doing it all in ASP would be very costly and you would not be able to take advantage of a stored procedure.

0
dba123Author Commented:
well, it is based on a search form I'm creating.  So the search parameters would vary and be dynamic.   I don't know how I'd add all parameters in the sp, then turn around and tell it not to use certain parameters depending on what the user did or didn't select in the search form.
0
bukkoCommented:
You would be better off coding for all eventualities.
Obviously this isn't always possible.
If you want to do this in a stored proc, you will have to pass in the WHERE clause as a parameter value.
But then that means you either need:
    (1) select permissions on the table(s)
    (2) to copy the data into local temp tables before you EXEC the dynamic SQL.
Generally, not a good thing to do.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

bukkoCommented:
Can you provide table structure(s) and possible selection criteria?
Maybe we can provide an SP to do what you want.
0
KarinLoosCommented:
Well.. you could create a UDF (user defined function) for the select without a where clause
then in the ASP page call a select * from UDF  and add on the where string
0
Anthony PerkinsCommented:
>>then in my stored proc I just wouldn't specify a where clause.  Is this possible?<<
Sure you can using dynamic SQL.  Should you do it?  The answer is probably not.  Instead use default parameters and check for those default parameters in the WHERE clause.  So for example, if you are querying an address:

Create Procedure usp_GetAddress
                   @Address1 varchar(50) = Null,
                   @Address2 varchar(50) = Null,
                   @City varchar(50) = Null,
                   @State char(2) = Null,
                   @ZIP char(10) = Null


As

SET NOCOUNT ON

Select *
From Table1
Where (@Address1 Is Null Or Address1 = @Address1)
           And (@Address2 Is Null Or Address1 = @Address2)
           And (@City Is Null Or City = @City)
           And (@State Is Null Or State = @State)
           And (@ZIP Is Null Or ZIP = @ZIP)



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bukkoCommented:
...or,

Select *
From Table1
Where Address1 = ISNULL( @Address1, Address1 )
           And Address2 = ISNULL( @Address2, Address2)
           And City = ISNULL( @City, City)
           And State = ISNULL( @State, State )
           And ZIP = ISNULL( @ZIP, ZIP)


IMPORTANT!
Remember that if you pass in parameter values from a form, you need to pass NULL rather than a blank string if no value is specified for a field. Otherwise a blank string will be searched for in your query.
If you don't want to have to worry about this, code for it as the beginning or your proc as follows:

Create Procedure usp_GetAddress
                   @Address1 varchar(50) = Null,
                   @Address2 varchar(50) = Null,
                   @City varchar(50) = Null,
                   @State char(2) = Null,
                   @ZIP char(10) = Null
As

SET NOCOUNT ON

IF @Address1 = '' SET @Address1 = NULL
IF @Address2 = '' SET @Address2 = NULL
IF @Address3 = '' SET @Address3 = NULL

etc...

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.