Build SQL Statement in Stored Procedure by Passing Variables

Build SQL Statement in Stored Procedure by Passing Variables

Post by Scot » Fri, 07 Feb 2003 05:38:39



Can you create a query by passing variables to a stored procedure - e.g. -

CREATE PROC p_FINAPRO_GetAgentAgreementProd



AS
Select Id,Name,AgencyState,


Not sure if i can do this or not.  I am trying to keep the query in a stored
procedure rather than building it in an ASP page

Thoughts anyone?

 
 
 

Build SQL Statement in Stored Procedure by Passing Variables

Post by Dennis Forbe » Fri, 07 Feb 2003 06:36:54


While it won't have the efficiency of a pure stored procedure, you can
accomplish this via EXEC or sp_executesql. i.e.



Cheers


> Can you create a query by passing variables to a stored procedure - e.g. -

> CREATE PROC p_FINAPRO_GetAgentAgreementProd




> AS
> Select Id,Name,AgencyState,
>  from company




> Not sure if i can do this or not.  I am trying to keep the query in a
stored
> procedure rather than building it in an ASP page

> Thoughts anyone?


 
 
 

Build SQL Statement in Stored Procedure by Passing Variables

Post by Keith Kratochvi » Fri, 07 Feb 2003 06:49:42


Something like this:


as
select * from orders




 ELSE OrderID END
go

exec foo 4, 1
exec foo 4, 2
exec foo 4, 4

--this one causes an error
exec foo 4, 3

go
drop proc foo
go

The ORDER BY will only work on like datatypes.  Try executing while passing in 4,3 and you will get an error

--
Keith, SQL Server MVP


> Can you create a query by passing variables to a stored procedure - e.g. -

> CREATE PROC p_FINAPRO_GetAgentAgreementProd




> AS
> Select Id,Name,AgencyState,
>  from company




> Not sure if i can do this or not.  I am trying to keep the query in a stored
> procedure rather than building it in an ASP page

> Thoughts anyone?

 
 
 

Build SQL Statement in Stored Procedure by Passing Variables

Post by Keith Kratochvi » Fri, 07 Feb 2003 06:54:38


By the way, I forgot to include

USE Northwind
GO

in my previous post

--
Keith, SQL Server MVP

Something like this:


as
select * from orders




 ELSE OrderID END
go

exec foo 4, 1
exec foo 4, 2
exec foo 4, 4

--this one causes an error
exec foo 4, 3

go
drop proc foo
go

The ORDER BY will only work on like datatypes.  Try executing while passing in 4,3 and you will get an error

--
Keith, SQL Server MVP


> Can you create a query by passing variables to a stored procedure - e.g. -

> CREATE PROC p_FINAPRO_GetAgentAgreementProd




> AS
> Select Id,Name,AgencyState,
>  from company




> Not sure if i can do this or not.  I am trying to keep the query in a stored
> procedure rather than building it in an ASP page

> Thoughts anyone?

 
 
 

Build SQL Statement in Stored Procedure by Passing Variables

Post by Joe Celk » Fri, 07 Feb 2003 09:01:49


The short answer is, yes, you can kludge it together with dynamic SQL.

The real answer is stop programming this way before you kill somebody.
What you are saying is that your application is so poorily designed and
understood that you are giving up and letting ANY user, present or
future, do prety much anything he or she wishes to do to your database.

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Build SQL Statement in Stored Procedure by Passing Variables

Post by Bill Hollinshead [MSFT » Fri, 07 Feb 2003 15:50:11


Hello All,

There is a well-known security problem with the use of Dynamic SQL (EXEC or
sp_executesql): It is possible for someone to inject their own malicious

'[somecolumn]= 1 GO DROP DATABASE [MyImportantDatabase]', or, even worse
SQL could be injected... Be extremely careful and make sure to check the
value being passed in before using it....

For performance reasons, I think it best to write multiple stored
procedures (and thus deliberately limit the user's choices, as Joe
suggests). Or, for perhaps less code maintenance,  I think it better to use
the case statement (Keith suggests). In the bigger picture, I think the
goal of writing less code is admirable, but perhaps is an insignificant
consideration <g>. In addition, that code is still being written, just not
at the ASP client <g>.

Thanks,

Bill Hollinshead
Microsoft, SQL Server

This posting is provided "AS IS" with no warranties, and confers no
rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

 
 
 

Build SQL Statement in Stored Procedure by Passing Variables

Post by Scot » Fri, 07 Feb 2003 22:15:37


Thanks Keith - The Order By Case Statment worked well.  Can I do this same
thing for a Where Clause?  I am trying, but getting errors back related to
the Group By clause I have before the Order By


Something like this:


as
select * from orders




 ELSE OrderID END
go

exec foo 4, 1
exec foo 4, 2
exec foo 4, 4

--this one causes an error
exec foo 4, 3

go
drop proc foo
go

The ORDER BY will only work on like datatypes.  Try executing while passing
in 4,3 and you will get an error

--
Keith, SQL Server MVP


> Can you create a query by passing variables to a stored procedure - e.g. -

> CREATE PROC p_FINAPRO_GetAgentAgreementProd




> AS
> Select Id,Name,AgencyState,
>  from company




> Not sure if i can do this or not.  I am trying to keep the query in a
stored
> procedure rather than building it in an ASP page

> Thoughts anyone?

 
 
 

Build SQL Statement in Stored Procedure by Passing Variables

Post by Keith Kratochvi » Sat, 08 Feb 2003 01:59:58


You can, however the query plan might be horrible and performance might not be good either.

Consider this example:

USE northwind
GO


/*some of the different params that you can pass*/



SELECT * FROM orders



--
Keith, SQL Server MVP


> Thanks Keith - The Order By Case Statment worked well.  Can I do this same
> thing for a Where Clause?  I am trying, but getting errors back related to
> the Group By clause I have before the Order By


> Something like this:

> create proc foo


> as
> select * from orders




>  ELSE OrderID END
> go

> exec foo 4, 1
> exec foo 4, 2
> exec foo 4, 4

> --this one causes an error
> exec foo 4, 3

> go
> drop proc foo
> go

> The ORDER BY will only work on like datatypes.  Try executing while passing
> in 4,3 and you will get an error

> --
> Keith, SQL Server MVP



> > Can you create a query by passing variables to a stored procedure - e.g. -

> > CREATE PROC p_FINAPRO_GetAgentAgreementProd




> > AS
> > Select Id,Name,AgencyState,
> >  from company




> > Not sure if i can do this or not.  I am trying to keep the query in a
> stored
> > procedure rather than building it in an ASP page

> > Thoughts anyone?

 
 
 

1. Build SELECT statements from variables in Stored Procedure

I have an application where I am developing a large library of stored
procedures.  At the heart of the application is a table, against which
queries can be performed based on a large number of columns.  One of
the primary stored procedures accepts these criteria as input
parameters.  Then, based on whether the parameter has a non-Null
value, dynamically builds a select statement.  Here is a small sample
of the where_clause being generated:


BEGIN

        BEGIN

        END
    ELSE
        BEGIN

        END



END


BEGIN


        BEGIN

        END
    ELSE
        BEGIN

        END


END

Ultimately, at the end of the stored proc, I execute a Select
statement as:


My concern, based on the number of input parameters, is that this
single stored proc, may issue a variety of different SELECT statements
(great from a programming perspective).  Is it true that, the first
SELECT statement issued through the stored proc is the means by which
SQL Server will establish an execution plan, such that other SELECT
statements issued through this stored proc will use the wrong
execution plan.

I am using this approach for building many of the stored procs.  It
really cuts down on the number of stored procs I would otherwise need
to implement the DB.  But, brevity does me no good if the applications
that use the DB perform horribly.

Thanks,
Randy

2. SQL question

3. Case Statement and variables passed to stored procedures

4. Visual Objects -> Delphi Question

5. Trouble passing variable in SELECT statement in Stored Procedure

6. Technical Support Specialists *LONDON*

7. Executing a sql statement stored as a variable in a stored Procedure

8. Database can't start

9. build sql-statement dynamically within a stored procedure

10. building SQL statement in a stored procedure

11. SQL 7 Stored Procedure errors when using a passed variable in FROM clause

12. SQL 2000 - Passing comma separated string variables into a stored procedure

13. Passing variables to SQL Server with Stored Procedures