|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Passing entire SELECT and WHERE clause to a stored procedureI have an application that allows the user to select various fields to
display and/or use as the search criteria to locate a record and I then build the entire SQL clause in code and execute the SQL clause from code. The records returned are actually a UNION of 4 different VIEWS. Is it possible to pass in the SELECT and WHERE clauses as the parameters to the stored procedure like; SELECT @Select FROM View1 WHERE @Where UNION SELECT @Select FROM View2 WHERE @Where .... I am using SQL Server 2000. Thanks, Pass in sql query string, and run it in the SP with
execute sp_executesql @sql Show quote "Ray" <R**@discussions.microsoft.com> wrote in message news:487EA85C-3809-48D5-ADCD-7021B8FC4AFF@microsoft.com... > I have an application that allows the user to select various fields to > display and/or use as the search criteria to locate a record and I then build > the entire SQL clause in code and execute the SQL clause from code. The > records returned are actually a UNION of 4 different VIEWS. > > Is it possible to pass in the SELECT and WHERE clauses as the parameters to > the stored procedure like; > > SELECT @Select FROM View1 WHERE @Where > UNION > SELECT @Select FROM View2 WHERE @Where > ... > > I am using SQL Server 2000. > > Thanks, Thanks David, this solved my problem.
Show quote "davidw" wrote: > Pass in sql query string, and run it in the SP with > > execute sp_executesql @sql > > > "Ray" <R**@discussions.microsoft.com> wrote in message > news:487EA85C-3809-48D5-ADCD-7021B8FC4AFF@microsoft.com... > > I have an application that allows the user to select various fields to > > display and/or use as the search criteria to locate a record and I then > build > > the entire SQL clause in code and execute the SQL clause from code. The > > records returned are actually a UNION of 4 different VIEWS. > > > > Is it possible to pass in the SELECT and WHERE clauses as the parameters > to > > the stored procedure like; > > > > SELECT @Select FROM View1 WHERE @Where > > UNION > > SELECT @Select FROM View2 WHERE @Where > > ... > > > > I am using SQL Server 2000. > > > > Thanks, > > > Before you implement this... read on.
http://www.sommarskog.se/ especially the chapters "The curse and blessings of dynamic SQL" and "Dynamic search conditions". Show quote "Ray" <R**@discussions.microsoft.com> wrote in message news:487EA85C-3809-48D5-ADCD-7021B8FC4AFF@microsoft.com... >I have an application that allows the user to select various fields to > display and/or use as the search criteria to locate a record and I then > build > the entire SQL clause in code and execute the SQL clause from code. The > records returned are actually a UNION of 4 different VIEWS. > > Is it possible to pass in the SELECT and WHERE clauses as the parameters > to > the stored procedure like; > > SELECT @Select FROM View1 WHERE @Where > UNION > SELECT @Select FROM View2 WHERE @Where > ... > > I am using SQL Server 2000. > > Thanks, Thanks Raymond, the article was very helpful.
Show quote "Raymond D'Anjou" wrote: > Before you implement this... read on. > http://www.sommarskog.se/ > especially the chapters "The curse and blessings of dynamic SQL" and > "Dynamic search conditions". > > "Ray" <R**@discussions.microsoft.com> wrote in message > news:487EA85C-3809-48D5-ADCD-7021B8FC4AFF@microsoft.com... > >I have an application that allows the user to select various fields to > > display and/or use as the search criteria to locate a record and I then > > build > > the entire SQL clause in code and execute the SQL clause from code. The > > records returned are actually a UNION of 4 different VIEWS. > > > > Is it possible to pass in the SELECT and WHERE clauses as the parameters > > to > > the stored procedure like; > > > > SELECT @Select FROM View1 WHERE @Where > > UNION > > SELECT @Select FROM View2 WHERE @Where > > ... > > > > I am using SQL Server 2000. > > > > Thanks, > > > Yes, you can do kludges to fake it. But you should not. You do not
know what you want to get back and you don't how you are going to get it. Think about what you would call this thing. That means a simple <verb><object> kind of name. Have you ever had a basic Software Engineering course? Coupling? Cohesion? Have you ever done any comerrcial out of the lab programming?
Think - dynamic front end, not a report writer but the user having the ability to select the columns they want and add their own filtering. Would you refer them to using SQL or would you create a nice UI so they can drag/drop, let me answer that one as you quite obviously haven't the experience to understand that, but its the latter. In order to do this in a stored procedure you would need hundreds of IF ELSE statements and code for each possible occurrance - that would not scale, it would also make coding more complex and less maintainable. Get back to basics - go and get some programming experience, its not enough just to read a book or do a course, you need experience too; and that doesn't mean playing with the language. Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1134690251.906075.325440@o13g2000cwo.googlegroups.com... > Yes, you can do kludges to fake it. But you should not. You do not > know what you want to get back and you don't how you are going to get > it. Think about what you would call this thing. That means a simple > <verb><object> kind of name. Have you ever had a basic Software > Engineering course? Coupling? Cohesion? >  |
|||||||||||||||||||||||
Other interesting topics