Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Concatenate Where Clause for Stored Proc using ASP

Posted on 2004-11-16
7
Medium Priority
?
318 Views
Last Modified: 2008-03-17
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?
0
Comment
Question by:dba123
7 Comments
 
LVL 6

Expert Comment

by:mcp111
ID: 12595985
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
 
LVL 1

Author Comment

by:dba123
ID: 12596010
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
 
LVL 8

Expert Comment

by:bukko
ID: 12596225
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:bukko
ID: 12596278
Can you provide table structure(s) and possible selection criteria?
Maybe we can provide an SP to do what you want.
0
 
LVL 13

Expert Comment

by:KarinLoos
ID: 12596692
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1000 total points
ID: 12596915
>>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
 
LVL 8

Assisted Solution

by:bukko
bukko earned 1000 total points
ID: 12598099
...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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question