stored procedure question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

stored procedure question

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-19 : 09:24:56

Hans writes "Hi,
Relatively new to sqlserver/stproc and in doubt with the following:

I have 1 table with about 10 columns that contain text (some of them more than 8000 chars, so they have the text datatype, the others having varchar). From a navbar people can pick their article.
I made the sp:

CREATE PROCEDURE sp_myproc
@cid int,
@eid int
AS
SELECT COLUMN FROM TABLE WHERE cid = @cid AND eid = @eid
GO

Now,
1. this works, but does it make any sense or will a normal recordset perform better/faster
2. is it possible to pass a COLUMN parameter from the navbar to dynamically pick the right column (so for example if I pass parameter " @sport " the sp should replace column for " sport ". I experimented with the sp but didn't get it to work). What should be the syntax?
3. If 2 is possible, will performance slow down because all navbar items lead to one ASP page (will serving 1 asp page for every navbar item a better choice?)

Thanks in advance,
cheers,
Hans Kan
hans@multisite.nl"

Nazim
A custom title

1408 Posts

Posted - 2002-02-19 : 10:10:10

you can do it using dynamic Sql .Search for Dynamic Sql in Article search of this Site. there are some pretty good articles on it.

CREATE PROCEDURE sp_myproc
@cid int,
@eid int,
@mcolumnname varchar(30)
AS
set nocount on
@mSelect varchar(8000)
set @mSelect=" SELECT "+ @mcolumnname + "FROM TABLE WHERE cid ="+ @cid +"AND eid ="+ @eid
exec (mSelect)
GO

yeah there certainly will be performance issues . because when you use Dynamic Sql there will be no chache plan for the stored procedure so everytime the sp will be executed afresh.

HTH

--------------------------------------------------------------

hanzzz
Starting Member

5 Posts

Posted - 2002-02-20 : 07:19:45

Thanks nazim,
So you mean that when using a stored procedure for this I can better point every navbar item to a seperate page?

Nazim
A custom title

1408 Posts

Posted - 2002-02-20 : 07:33:36

That is a UI issue and its not possible to tell without actually having a look at your current design and the data you want to display. it all depends on how the info fits in your current or new page.
you can try displaying the data in current page and as well as new page. and compare the UI design. compare the look, the clarity. which ever gives you better result you can opt for that method.

quote:

So you mean that when using a stored procedure for this I can better point every navbar item to a seperate page?

--------------------------------------------------------------

hanzzz
Starting Member

5 Posts

Posted - 2002-02-22 : 04:44:55

nazim,
I'll explain a bit more. there will be about 50 usergroups that can login to the site and update insert and delete articles. most off them will do that probably on the same day and time. I have 4 tables and all users see their data filtered by userid.
Now, I could use only one set of view, update, insert etc asp-pages for all of them, but I can imagine that that can give quit a heavy load on the server and performance will be slow?
Will it be better to serve a different set of pages to every user? Will it be better to use 4 tables for every user intead of 4 tables for all users (which means a total of 200 tables vs 4)?
Thanks for any advise!

Lou
Yak Posting Veteran

59 Posts

Posted - 2002-02-22 : 18:20:55

Hans,

4 tables is much better than 200 tables. 1 set of pages is much better than 50 sets of pages. If you're working with text, SQL server can multiple connections/transactions with ease. The problem you will run into is with locking. Update and insert with put different locks on your table. Your users will probably get a bunch of connection timeouts if they try to do it all at once.

One solution is a batch job queue. See this thread
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=13080

HTH

Edited by - lou on 02/22/2002 18:37:04

hanzzz
Starting Member

5 Posts

Posted - 2002-03-01 : 09:50:59

thanks for the advise,
cheers,
hanzzz

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources