Create Store Procedure to create View

Question

Post reply

Create Store Procedure to create View

SQLIndia

Ten Centuries

Points: 1073
More actions
December 2, 2002 at 5:24 am

#80418

Hi folks
I hope you help me out in this....
I want create view via store procedure and call(use) through ASP script in regard to display view's result which is part of a report.
could you please let me know how could I create view via store procedure and call from ASP..
thanks in advance guys..
swarn
SqlIndia

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Klaas-Jan SSCrazy Points: 2297 More actions · Answer 1

Hi, i tink you don't need a view you can directly use the recordset you create in the stored procedure.

for example

you have this stored procedure:

create usp_test

(

@name varchar(50)

)

as

select * from authors

where au_lname=@name

In the asp page you do something like this:

dim conn

dim rs

dim strSql

dim strInput

strInput ="white"

Set cnn = Server.CreateObject("ADODB.Connection")

cnn.Connectionstring="yourconnection"

strsql="usp_test '" & strinput & "'"

set rs = cnn.execute (strsql)

or better to create command object with parameter

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Walk us thru what your are trying to do so we can offer a solution for your needs.

SQLIndia Ten Centuries Points: 1073 More actions · Answer 3

first of all thanks guys for quick reply. really appreciate it.

Ok antares here it is the details:

suppose users want to see total sales by manufacturers with in given dates (enter by users on web page using ASP script). there are two ways to do it either you can do directly on asp page using T-SQL OR create basic view (which have done)and use this view further in your T-SQL in ASP page..but in second way view always sitting as an object in database. OR third way is create SP for query and call this SP whenever user entered search criteria in report interface which could be better solution in term of security and avoiding extra objects sitting in database.

please advice am I doing right way or any other better ways do it???

I hope this would be clear the question. if you need to know further more plz do ask..

swarn

SqlIndia

Andy Jones, DBA Hall of Fame Points: 3641 More actions · Answer 4

For the performance of the report I think the stored procedure method oultined above is the best solution. If you create a view and then

filter it you will be grouping information that you will never use. You can create a sp with input parameters for date range, first filter the resultset,

then group it server side only returning the information that is required.

Regards,

Andy Jones

Edited by - andyj93 on 12/02/2002 07:51:42 AM

.

SQLIndia Ten Centuries Points: 1073 More actions · Answer 5

hi andy

I agree but you have sample code for it where i can refer to it..

thanks

swarn

SqlIndia

Andy Jones, DBA Hall of Fame Points: 3641 More actions · Answer 6

Hi, see the post of Klaas-Jan above to get you started.

Regards,

Andy Jones

.

don1941 SSCarpal Tunnel Points: 4183 More actions · Answer 7

The accepted format is to use a stored procedure and pass it the parameters you need. Generating SQL statements from user-supplied input is inefficient and risky (from a security point of view.)

Antares686 SSC Guru Points: 125444 More actions · Answer 8

quote:
first of all thanks guys for quick reply. really appreciate it.
Ok antares here it is the details:
suppose users want to see total sales by manufacturers with in given dates (enter by users on web page using ASP script). there are two ways to do it either you can do directly on asp page using T-SQL OR create basic view (which have done)and use this view further in your T-SQL in ASP page..but in second way view always sitting as an object in database. OR third way is create SP for query and call this SP whenever user entered search criteria in report interface which could be better solution in term of security and avoiding extra objects sitting in database.
please advice am I doing right way or any other better ways do it???
I hope this would be clear the question. if you need to know further more plz do ask..
swarn

As everyone else I agree you should use the Stored Procedure approach. Create an SP for each needed query and save. Overall you will get better performance this way.

And so refer back to Klaas-Jan as a starting point for calling. However, you should look at using a Command Object instead and set to adStoredProc to get maximum performance.

Also look here for some good info on using a Stored Procedure with ASP.

http://www.asp101.com/samples/viewasp.asp?file=storedprocs.asp