Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

where statement as variable

Posted on 2002-06-07
2
Medium Priority
?
211 Views
Last Modified: 2008-03-06
Dear all,
    If I now want to pass some condition from ASP to store just like:

in ASP:
  sqlquery="where clientid = 3 and status='R'"
  callStoreProcedure(SPname, sqlquery)
  pass in to Store procedure

in Store procedure:

Create  StoreProcedure1(@sqlquery varchar(30))
BEGIN
   Select *
   From Client
   @sqlquery
GO

How can I do that?
0
Comment
Question by:lemontree
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 39

Expert Comment

by:appari
ID: 7061347
Create  StoreProcedure1(@sqlquery varchar(30))
BEGIN
exec('Select *  From Client' +   @sqlquery)
GO
0
 
LVL 9

Accepted Solution

by:
miron earned 450 total points
ID: 7061405
another way, nice for both security and performance on database side, is to structure ASP and stored procedure this way

ASP
...
oCmd.Parameters.Append(oCmd.CreateParameter("@where_criteria", adVarChar, 8000, adParamInput));
oCmd.Parameters.Append(oCmd.CreateParameter("@WithWhere", adInteger, adParamInput));
oCmd( "@where_criteria" ) = "abcd"
oCmd( "@WithWhere" ) = 1
...


create procedure my_procedure
                       @where_criteria varchar(8000)
                      , @WithWhere int = 0 -- by default where clause is omitted
AS
BEGIN
...
IF( @WithWhere = 0 )
BEGIN
   select * from my_table
END

IF( @WithWhere = 1 )
BEGIN
   select * from myt_table where my_col = @where_criteria
END
...
END --END of procedure

GO

my two cents
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

664 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