mikeandbee
asked on
asp & dynamic stored procedures
I want to use a dynamic stored procedure in SQL Server but am not sure how to use it with multiple dynamic values in the querystring. For example, a basic query where there is one dynamic value passed into the query eg
CREATE PROCEDURE sp_simple ( @Surname varchar(100) )
AS
EXEC('Select Surname FROM List WHERE
Surname = ''%' + @Surname + '%'' ')
and calling this in asp page like so:
strSQL = "sp_simple '" & request("Surname") & "'
but what whenyou have an INSERT INTO query with say 5 inserts, all being passed dynamic data? Can someone show me an example of a multiple dynamic SP and calling it form an ASP page?
thanks
CREATE PROCEDURE sp_simple ( @Surname varchar(100) )
AS
EXEC('Select Surname FROM List WHERE
Surname = ''%' + @Surname + '%'' ')
and calling this in asp page like so:
strSQL = "sp_simple '" & request("Surname") & "'
but what whenyou have an INSERT INTO query with say 5 inserts, all being passed dynamic data? Can someone show me an example of a multiple dynamic SP and calling it form an ASP page?
thanks
edemcs
is the insert in the stored procedure? Where are the 5 values coming from and are they being passed?
You could do this one of two ways.
1. You could loop for each item you are inserting. For instance, if you had a list box that allowed multiple selections, then you would do it like this:
strListBox = Request.Form("lstValues")
arrListBox = Split(strListBox)
for each item in arrListBox
objConn.Execute("sp_insert
'or if inserting strings
'objConn.Execute("sp_inser
next
2. You could pass in the comma-delimited string as your parameter and manipulate it there like this:
objConn.Execute("sp_Insert
Create Procedure sp_insertSomething
@Input varchar(255)
AS
Declare @CharPosition int
Declare @InsertValue int
Declare @StartPosition int
Set @CharPosition = CharIndex( ' , ' , @Input,1)
If @CharPosition > 0
While @CharPosition > 0
Begin
Set @InsertValue = Convert(int, Substring(@Input, @StartPosition, @CharPosition - @StartPosition - 1) )
Insert Into tblSomeTable (Field1) Values (@InsertValue)
@StartPosition = @CharPosition + 1
Set @CharPosition = CharIndex( ' , ' , @Input, @StartPosition
End
Else
Insert Into tblSomething (Field1) Values (Convert(int, @InsertValue))
End
Return
GO
1. You could loop for each item you are inserting. For instance, if you had a list box that allowed multiple selections, then you would do it like this:
strListBox = Request.Form("lstValues")
arrListBox = Split(strListBox)
for each item in arrListBox
objConn.Execute("sp_insert
'or if inserting strings
'objConn.Execute("sp_inser
next
2. You could pass in the comma-delimited string as your parameter and manipulate it there like this:
objConn.Execute("sp_Insert
Create Procedure sp_insertSomething
@Input varchar(255)
AS
Declare @CharPosition int
Declare @InsertValue int
Declare @StartPosition int
Set @CharPosition = CharIndex( ' , ' , @Input,1)
If @CharPosition > 0
While @CharPosition > 0
Begin
Set @InsertValue = Convert(int, Substring(@Input, @StartPosition, @CharPosition - @StartPosition - 1) )
Insert Into tblSomeTable (Field1) Values (@InsertValue)
@StartPosition = @CharPosition + 1
Set @CharPosition = CharIndex( ' , ' , @Input, @StartPosition
End
Else
Insert Into tblSomething (Field1) Values (Convert(int, @InsertValue))
End
Return
GO
Left of a closing parenthesis. Sorry.
Create Procedure sp_insertSomething
@Input varchar(255)
AS
Declare @CharPosition int
Declare @InsertValue int
Declare @StartPosition int
Set @CharPosition = CharIndex( ' , ' , @Input,1)
If @CharPosition > 0
While @CharPosition > 0
Begin
Set @InsertValue = Convert(int, Substring(@Input, @StartPosition, @CharPosition - @StartPosition - 1) )
Insert Into tblSomeTable (Field1) Values (@InsertValue)
@StartPosition = @CharPosition + 1
Set @CharPosition = CharIndex( ' , ' , @Input, @StartPosition)
End
Else
Insert Into tblSomething (Field1) Values (Convert(int, @InsertValue))
End
Return
GO
Create Procedure sp_insertSomething
@Input varchar(255)
AS
Declare @CharPosition int
Declare @InsertValue int
Declare @StartPosition int
Set @CharPosition = CharIndex( ' , ' , @Input,1)
If @CharPosition > 0
While @CharPosition > 0
Begin
Set @InsertValue = Convert(int, Substring(@Input, @StartPosition, @CharPosition - @StartPosition - 1) )
Insert Into tblSomeTable (Field1) Values (@InsertValue)
@StartPosition = @CharPosition + 1
Set @CharPosition = CharIndex( ' , ' , @Input, @StartPosition)
End
Else
Insert Into tblSomething (Field1) Values (Convert(int, @InsertValue))
End
Return
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ASPGuru: Yes, i thought it might not be worth the hassle because a 'dynamic' stored procedure somewhat defeats the purpose of using SP's.
Please award/close or advise what else is needed.
Moondancer
Community Support Moderator @ Experts Exchange
Moondancer
Community Support Moderator @ Experts Exchange
This question appears to have been abandoned, all your open questions are posted below.
Your options are:
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you. You must tell the participants why you wish to do this, and allow for Expert response.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question. Again, you must tell the other participants why you wish to do this.
For special handling needs, please post a zero point question in the link below, include the question QID/link.
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
Please click this Help Desk link for Member Guidelines, Member Agreement and the Question/Answer process: Click you Member Profile to view your question history and keep them all current with updates as the collaboration effort continues.
https://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp
PLEASE DO NOT AWARD THE POINTS TO ME.
------------> EXPERTS:
Please leave any comments regarding this question here on closing recommendations if this item remains inactive another three days.
Thank you everyone.
Open Qs:
https://www.experts-exchange.com/jsp/qShow.jsp?qid=11677198
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20136244
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20138104
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20139384
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20141051
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20141266
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20147138
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20175597
https://www.experts-exchange.com/jsp/qShow.jsp?qid=11188236
Moondancer
Moderator @ Experts Exchange
P.S. For year 2000 question, special attention is needed to ensure the first correct response is awarded, since they are not in the comment date order, but rather in Member ID order.
Your options are:
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you. You must tell the participants why you wish to do this, and allow for Expert response.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question. Again, you must tell the other participants why you wish to do this.
For special handling needs, please post a zero point question in the link below, include the question QID/link.
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
Please click this Help Desk link for Member Guidelines, Member Agreement and the Question/Answer process: Click you Member Profile to view your question history and keep them all current with updates as the collaboration effort continues.
https://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp
PLEASE DO NOT AWARD THE POINTS TO ME.
------------> EXPERTS:
Please leave any comments regarding this question here on closing recommendations if this item remains inactive another three days.
Thank you everyone.
Open Qs:
https://www.experts-exchange.com/jsp/qShow.jsp?qid=11677198
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20136244
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20138104
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20139384
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20141051
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20141266
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20147138
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20175597
https://www.experts-exchange.com/jsp/qShow.jsp?qid=11188236
Moondancer
Moderator @ Experts Exchange
P.S. For year 2000 question, special attention is needed to ensure the first correct response is awarded, since they are not in the comment date order, but rather in Member ID order.