Solved

where statement as variable

Posted on 2002-06-07
2
184 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
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 150 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

770 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