Dynamic parameters in a SP - 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

Transact-SQL (2000)

Dynamic parameters in a SP

Author

Topic

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-05-16 : 15:56:37

Hi All,
I am working on a course registration form that accepts users dynamic user input.

E.G
A user could choose to register from 1 to a maximum of 30 courses.

However on the part of the SP, i am wondering if i should list out all the parameters e.g.

@course1
@course2
@course3

now i am wondering if it would be ok, to declare all 30 variables or do declare them based on dynamic values

e.g.

-- Create a variable counter to hold the courses and the total number of records
set @total = --total number of courses to be registered
set @counter = 0
set @courses = @courses + @counter

--Capture the total number of courses in a dynamic variable
--How do i capture the list of courses sent in my ASP page ?

while @counter < @total
begin
select @coursesubjectcode_8 = @course+@counter

INSERT INTO [unibenportal].[dbo].[StudentCourseSubjectReg]
( [entrydate],
[refcode],
[currentsession],
[studentlevel],
[semester],
[matricno],
[entryregno],
[coursesubjectcode],
[coursesubjectname],
[coursesubjectunits],
[notes])

VALUES
( CURRENT_TIMESTAMP,
@refcode_2,
@currentsession_3,
@studentlevel_4,
@semester_5,
@matricno_6,
@entryregno_7,
@coursesubjectcode_8,
@coursesubjectname_9,
@coursesubjectunits_10,
@notes_11)

select @counter = @counter + 1
if @counter = @total
break

end

Is this efficient and how do i capture the dynamic course list or is it efficient to do so manually ?

Afrika

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-05-16 : 16:07:32

Looks like you are trying to pre-set all the courses in the table when you dont know what they are.

I have done this for a friend of mine but I segrigated it into two tables a semesterinfo table and a courseinfo table.

Then on the entry form I subbed out the courseinfo in a subform this removes all the redundent info and allows selection of each course.
At the form close I tally up the number of courses and post in the semesterinfo table.

Just a thought.

Jim
Users <> Logic

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-05-18 : 12:59:17

Quite a good idea Jim, am still working on it, but your idea is noted

Afrika

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-19 : 04:17:55

hey dude try this:

IN ASP:
its up to you on how you can formulate data, you can use a function to
construct in this kind of format: (NOTE: If a course has a comma e.g. "Computer Science, IT" it should be replace by another character on you specification, just convert it again after the array splitter.

Chemistry, Biology, I.T., Criminology, Law, Computer Science, Etc, Etc, Etc, Etc

This SP should be supplied by a comma "," per items to execute..

CREATE PROCEDURE CoursesInsProc
(
@refcode_2 as varchar(150) ,
@currentsession_3 as varchar(150),
@studentlevel_4 as varchar(150),
@semester_5 as varchar(150),
@matricno_6 as varchar(150),
@entryregno_7 as varchar(150),
@coursesubjectcode_8 as varchar(150),
@coursesubjectname_9 as varchar(150),
@coursesubjectunits_10 as varchar(150),
@notes_11 as varchar(150),
@cslist VARCHAR(8000)
)
AS
-- create tmp table
CREATE TABLE #temp ([id] [int] IDENTITY (1, 1) NOT NULL , course varchar(150))
--- this is the Array Splitter BEGIN
--- splits the courseList
BEGIN
DECLARE @spot SMALLINT, @str VARCHAR(8000), @sql VARCHAR(8000)

WHILE @cslist <> ''
BEGIN
SET @spot = CHARINDEX(',', @cslist)
IF @spot>0
BEGIN
SET @str = CAST(LEFT(@cslist, @spot-1) AS INT)
SET @cslist = RIGHT(@cslist, LEN(@cslist)-@spot)
END
ELSE
BEGIN
SET @str = CAST(@cslist AS INT)
SET @cslist = ''
END
IF (len(@str) > 1 and @str <> null)
BEGIN
SET @sql = 'INSERT INTO #temp (course) VALUES('+@str+')'
EXEC(@sql)
END
END
END
--- end Array Splitter
------------ end populating #temp table
DECLARE @total INT, @counter INT
DECLARE @scourse VARCHAR(150)
select @total= max([id]) from #temp
---- looping using DYNAMIC SQL
SET @counter = 1--- first primary id is 1

while @counter <= @total
begin
SELECT @scourse = course from #temp where [id] = @counter ---- selects the course from temp table
DECLARE @tSQL VARCHAR(6000) -- change this to NVARCHAR if you are using different charset
SET @tSQL = ' INSERT INTO [unibenportal].[dbo].[StudentCourseSubjectReg] ' +
'( [entrydate],[refcode],[currentsession],[studentlevel],[semester],[matricno],[entryregno],[coursesubjectcode],[coursesubjectname],[coursesubjectunits],[notes]) ' +
'VALUES ' +
'( CURRENT_TIMESTAMP, ' + cast( @refcode_2 as varchar) + ', ' + cast(@currentsession_3 as varchar) +',' + cast(@studentlevel_4 as varchar) +',' +
cast(@semester_5 as varchar) + ',' +
cast(@matricno_6 as varchar) + ',' +
cast(@entryregno_7 as varchar) + ',' +
cast(@coursesubjectcode_8 as varchar) + ',' +
cast(@coursesubjectname_9 as varchar) + ',' +
cast(@coursesubjectunits_10 as varchar) + ',' +
cast(@notes_11 as varchar) + ')'
EXEC(@tSQL)
END

DROP TABLE #temp

Cursors are for those who doesn't know how to use SQL

raclede™

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-05-19 : 08:23:04

Amazing,
seems llike a lot of work Raclede, would try it and see

thanks
Afrika

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-05-19 : 08:28:41

If i may ask
whats wrong with cursors ?

quote:
Cursors are for those who doesn't know how to use SQL

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-19 : 21:37:54

cursors are slow and hurts performance

Cursors are for those who doesn't know how to use SQL

raclede™

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-05-19 : 21:50:40

Cursors are tweezers, SQL is a spoon:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47319

Hey, if you think THAT's a bad title, then talk to this multi-millionaire putz:

http://www.amazon.com/exec/obidos/ASIN/006016848X/qid=1116553815/sr=2-1/ref=pd_bbs_b_2_1/002-0304997-1637613

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-19 : 21:51:17

http://support.microsoft.com/?kbid=888799

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-05-20 : 04:27:18

thanks for the references.

Whats the SQL alternatives to working without cursors ?

Please advice
Afrika

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-20 : 04:34:01

know the basic sql command like
select + insert,update + from,subqueries

Cursors are for those who doesn't know how to use SQL
K.I.S.S. - Keep it simple stupid
raclede™

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-20 : 04:35:03

quote:
http://support.microsoft.com/?kbid=888799

I counted the word cursor 14 times in the buglist...

quote:
If i may ask
whats wrong with cursors ?

Haven't this been asked already, You've been around for some time now Afrika...
How can You not have noticed that cursors are avoided by all

s here!

I think You are taking on too much work here Afrika.
You let the sp accept lots of dynamic input, and it's a very messy to sort it out.
Don't allow the user input be quite so dynamic, try to keep it more structured.

Maybe You will find this useful: Arrays in sql server

quote:
Cursors are for those who doesn't know how to use SQL

And while loops are the cursors cousin

rockmoose

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-05-20 : 04:38:41

I know this is deviating from the original topic. But....

When i first got into web db, i learnt to code from scratch with wrox publication, ASP 3.0.

SECONDLY, I also worked with dreamweaver extensively on my first serious web app (My personal project) and used its code, which is below. I am migrating to VB.net and SQL 2k, however below is a sample of my old code

Please advice on it

Dim users
Dim users_numRows

Set users = Server.CreateObject("ADODB.Recordset")
users.ActiveConnection = MM_mary_STRING
users.Source = "SELECT Username, First_Name, Last_Name, Phone FROM dbo.Users WHERE Username = '" + Replace(users__MMColParam, "'", "''") + "'"
users.CursorType = 0
users.CursorLocation = 2
users.LockType = 1
users.Open()

users_numRows = 0

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-05-20 : 04:40:51

quote:
Originally posted by raclede
know the basic sql command like
select + insert,update + from,subqueries

thats a piece of cake

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-05-20 : 04:44:29

quote:
I counted the word cursor 14 times in the buglist...

Yes, i counted them as well, I have heard so much negative things about cursors and also a few hints about reducing the amount of triggers used as well.

Its just that when I read books, a lot of praise is giving to cursors, esp for interactive online applications

And whats wrong with while loops

Funny enough i have a counter that increments a value to return values for dynamic content passed to my SP

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-20 : 06:11:39

>> Its just that when I read books, a lot of praise is giving to cursors, esp for interactive online applications

I don't think that is appropriate, in almost all cases there are better set-based solutions to a problem.
(compared to the cursor solution)

>> a few hints about reducing the amount of triggers used as well
They have a negative impact on perf, can be hard to debug, can cause hard to trace problems.
Naughty little things running secretly deep down under the covers of the database.

They absolutely do have their place though, for some tasks triggers are a very good solution (imo)
E.g. Auditing, Enforcing Data Integrity constraints, Cascading delete, Updateable views...

I personally try to avoid putting any complex logic in triggers. I just let them do "dumb/simple" auto-magic stuff.
More complex processing is done in stored procedures + udfs.

rockmoose

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-05-20 : 06:19:07

Thanks Rockmoose,
you however didnt say anything about your earlier comment.

Whats wrong with while loops and how do you go about it.

Secondly, what would you advice on the above code i wrote above, that dreamweaver generates ?

Please advice on this


Dim users
Dim users_numRows

Set users = Server.CreateObject("ADODB.Recordset")
users.ActiveConnection = MM_mary_STRING
users.Source = "SELECT Username, First_Name, Last_Name, Phone FROM dbo.Users WHERE Username = '" + Replace(users__MMColParam, "'", "''") + "'"
users.CursorType = 0
users.CursorLocation = 2
users.LockType = 1
users.Open()

users_numRows = 0

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-20 : 06:28:36

quote:
Originally posted by afrika
And whats wrong with while loops

Funny enough i have a counter that increments a value to return values for dynamic content passed to my SP

There is nothing wrong with while loops per se.
Everyone programmer needs and uses them.

It's the different programming principles:
set-based contra procedural.
SQL manipulates data in sets (or batches), You perform the operations on many rows at a time.
This is magnitudes faster compared to looping through rows one by one

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

Let's say You have a cursor, and You want to rewrite it because You heard that they are bad.
Now if You replace the cursor with a while loop, You have just switched from one procedural row-by-row solution to another.
If on the other hand You replace the cursor with SQL statements that manipulate all the rows at once, then You have a set-based solution.

But teach, I only loop through 5 rows, does it matter ?
Yes! it's different ways of thinking.

When You take spagetti from a pot,
You take a whole bunch out, You don't take them out 1 by 1.

But teach, I only want 3 meatballs !?
Your choice, take 3 once, or take 1 thrice.

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-20 : 06:38:27

Afrika,

Of course You have to use while loops almost every day.
Just start to worry if You process 1 database row per iteration in the loop.

How about some sample input data that You are expecting to recieve from the clients.
Also the table(s) DDL could be helpful.

You have shown us some code, but I feel that it would be easier to help if You gave us some more information.

I am allergic to VB and dynamic SQL, (but that's me

)
So I have no comments on the code snippet You posted.

rockmoose

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-20 : 06:45:41

use Disconnected RS when displaying records and COM+ for SQL Operations other than displaying..

also you can use Server.CreateObject("ADOR.Recordset") instead of
Server.CreateObject("ADODB.Recordset") for read-only (just displaying)
for classic ASP... so much for classical times, Im now working with C# and .NET
Components/COM+ .. i think you should switch too.

Cursors are for those who doesn't know how to use SQL
K.I.S.S. - Keep it simple stupid
raclede™

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-05-20 : 07:13:22

Now this is getting very interesting. I actually never saw it that way.

quote:

Originally posted by rockmoose
There is nothing wrong with while loops per se.
Everyone programmer needs and uses them.

It's the different programming principles:
set-based contra procedural.
SQL manipulates data in sets (or batches), You perform the operations on many rows at a time.
This is magnitudes faster compared to looping through rows one by one

quote:
Let's say You have a cursor, and You want to rewrite it because You heard that they are bad.
Now if You replace the cursor with a while loop, You have just switched from one procedural row-by-row solution to another.
If on the other hand You replace the cursor with SQL statements that manipulate all the rows at once, then You have a set-based solution.

But teach, I only loop through 5 rows, does it matter ?
Yes! it's different ways of thinking.

When You take spagetti from a pot,
You take a whole bunch out, You don't take them out 1 by 1.

But teach, I only want 3 meatballs !?
Your choice, take 3 once, or take 1 thrice

Sounds reasonable to me, considering the power of SQL server and reason behind batch processing.

thanks for the advice

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources