Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

where statement as variable

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
lemontree
Asked:
lemontree
1 Solution
 
appariCommented:
Create  StoreProcedure1(@sqlquery varchar(30))
BEGIN
exec('Select *  From Client' +   @sqlquery)
GO
0
 
mironCommented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now