October 9, 2007 at 10:50 am
I have the following SP:
CREATE PROCEDURE proc
@param1 varchar(20),
@param2 varchar(20),
@param3 varchar(20),
@param4 varchar(20),
@param5 varchar(20),
@param6 varchar(20)
AS
INSERT INTO table1 VALUES (1, @param1)
GO
INSERT INTO table1 VALUES (2, @param2)
GO
INSERT INTO table1 VALUES (3, @param3)
GO
INSERT INTO table1 VALUES (4, @param4)
GO
INSERT INTO table1 VALUES (5, @param5)
GO
I tried to do all the INSERT statements at the same time using a WHILE but my problem is that I don´t know how to make the system take a concatenation of @param and the variable @i (number incremented in the while) as the value of @param1 (having @i=1). Is this possible?
My real problem is that the SP I gave here is an example, the one I have to do receives 30 parameters like the ones in the example and they must be called @param1, ..., @param30. Is there any chance of not having to do 30 INSERTs?
Thank you
October 9, 2007 at 11:41 am
If I understand the problem correctly, what about:
CREATE PROCEDURE x
(@ParamList XML)
AS
DECLARE @iDoc int
EXEC dbo.sp_xml_preparedocument @iDoc OUTPUT, @ParmList
INSERT INTO dbo.MyTable
(Id, Value)
SELECT
(Id, Value)
FROM OPENXML(@iDoc, '/MyParameterList',1)
WITH(Id int '@Id',
Value varchar(50) '@Value')
EXEC dbo.sp_xml_removedocument @iDoc
GO
That will allow you to build a list of parameters in XML. You can pass as many values as you want. Obviously this doesn't have error trapping or other stuff. Also, I see you inserting 1,2,3 for the ID. Assuming you want an autoincrement, why not use the IDENTITY property for a column on the table?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 9, 2007 at 12:17 pm
Thanks for your answer. I think I understand what you say but I don´t know if you understood what I meant because I didn´t explain it clearly.
I must create a SP to run on a database that already exists and I can´t modify (for example I can´t use the IDENTITY).
That SP will be executed by a program developed by other person, I must tell him how to call the SP.
Regarding my 30 parameters (my SP has a lot more, I only mention these cause they are the problem), they exist because the SP must allow to insert up to 30 rows like the ones I mentioned in the example (number, data) in a table. This means I must check one by one every parameter to see if it is not NULL and if so insert a row into the table. Here is my problem, do I have to write 30 IF @param IS NOT NULL INSERT...?
I don´t think I can receive an XML as a parameter, I must receive the 30 parameters (they can be null).
Is there a way of refering to the parameter by the position in which it is defined in the SP?
Thanks
October 9, 2007 at 12:37 pm
Sorry that wasn't helpful.
You've got a lot of very odd restrictions on how you can do this work. Why is the behavior so peculiar?
However, if you must have 30 different parameters, then you're going to have to refer to 30 different parameters within your code. You don't have to have 30 different IF clauses:
INSERT INTO Table
(Columnlist)
SELECT 1, @Param1
WHERE @Param1 IS NOT NULL
INSERT INTO Table
(ColumnList)
SELECT 2, @Param2...
WHERE @Parame2 IS NOT NULL
You'll still have 30 distinct insert statements, but you won't have to worry about IF statements. It'll just run all 30 every time, but some of the 30 won't do anything because they have null values.
However, this is still a very kludgy way to go about working with SQL Server. SQL is designed to support sets of data, mulitple rows. This row-by-row approach can really mess things up both in terms of the code necessary (as you're seeing here) and the performance and maintenance for the system.
Going back to the XML solution that I offered, if you didn't pass some values, you wouldn't get some inserts instead of having to do the check as you seee above. That's part of what sets offer.
Actually, based on what you're proposing, you'd be almost better off doing all the checks inside the application and writing a single INSERT statement from the client for each parameter that has a value. Again, the restrictions you've placed around this are kind of odd.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 9, 2007 at 12:50 pm
The restrictions are because the SP is for the database of a customer. The customer will provide the application that executes the SP. Perhaps I can tell the customer to pass an XML parameter. Which will be the solution in that case? (remember I have other parameters in the SP).
So it is impossible to concatenate a string to form the name of a variable and tell the compiler it is a variable and not a string? Is there a way of fetching the value of the parameter by knowing the position in the SP?
October 9, 2007 at 1:16 pm
Gonzalo Faral (10/9/2007)
So it is impossible to concatenate a string to form the name of a variable and tell the compiler it is a variable and not a string?
Unless you're talking about doing sort of ad hoc sql that builds a string and then executes it, no, nothing like this is possible within TSQL.
Is there a way of fetching the value of the parameter by knowing the position in the SP?
Not within the procedure, no. Each parameter is a database object identifier and has to be referenced by its name, not its ordinal position. Outside the procedure, when calling it, you can refer to the parameters by their ordinal position, but that's not what you're looking for.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 9, 2007 at 1:24 pm
OK. Thanks a lot. I will consider the XML way or do the 30 INSERTs
October 9, 2007 at 2:28 pm
doesn't look like dynamic SQL would buy you any advantage whatsoever. You might try this just slightly friendlier syntax
INSERT INTO Table
SELECT 1, @Param1 WHERE @Param1 IS NOT NULL
UNION ALL SELECT 2, @Param2 WHERE @Parame2 IS NOT NULL
UNION ALL SELECT 3, @Param3 WHERE @Parame3 IS NOT NULL
.....
The insert would then be a single operation. Shouldn't be a huge deal though, unless you have a LOT hitting that table. That being said - XML WOULD be a better way to go.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 10, 2007 at 5:19 am
Good one. I hadn't thought it through that far.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 10, 2007 at 10:02 am
That´s much better, thanks Matt
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply