| Subject: | Variable number of parameters into stored procedure |
| Posted by: | John Friel III (jo…@frieltek.com) |
| Date: | Thu, 29 Apr 2010 |
I apologize up front for the length of this message.
I am trying to pass a variable number of parameters into a MS SQL 2008
stored procedure using Delphi 2009.
The procedure (and stored procedure) are:
procedure AddTagToPartID(PartID : Integer; Tag, TagDescription : String;
CreateNew : Boolean;
TagCodeGUID : TGUID;
NewTagCode : String;
AssignRelated : Boolean; RelatedTagGUID : TGUID);
var
RS : TADOStoredProc;
begin
RS := TADOStoredProc.Create(nil);
try
RS.Connection := DM.ADOConnection1;
RS.ProcedureName := 'IC0010_AddTagForPartID';
//
// PROCEDURE [dbo].[IC0010_AddTagForPartID]
// @PartID int
// , @Tag nvarchar(max)
// , @TagDescription nvarchar(max)
// , @CreateNewTagCode bit
// , @TagCodeID uniqueidentifier = null
// , @TagCode varchar(32) = null
// -- Optional parameters:
// , @AssignToRelatedTag bit = 0
// , @RelatedTagID uniqueidentifier = null
// AS
// BEGIN
// SET NOCOUNT ON;
//
// DECLARE @temp AS TABLE (
// TagID uniqueidentifier DEFAULT newsequentialid()
// , TagCodeID uniqueidentifier
// , Tag nvarchar(max)
// , TagDescription nvarchar(max)
// );
//
// IF @CreateNewTagCode = 1 BEGIN
// DECLARE @InsertedTagCode TABLE (
// TagCodeID uniqueidentifier
// );
//
// INSERT PartTagCode (TagCode)
// OUTPUT Inserted.TagCodeID INTO @InsertedTagCode
// SELECT @TagCode
//
// SET @TagCodeID = (SELECT TOP 1 TagCodeID FROM @InsertedTagCode);
// END
//
// INSERT @temp (TagCodeID, Tag, TagDescription)
// SELECT @TagCodeID, @Tag, @TagDescription
//
// INSERT PartTag (TagID, TagCodeID, Tag, TagDescription)
// SELECT TagID, TagCodeID, Tag, TagDescription
// FROM @temp
//
// INSERT PartTagParts
// SELECT @PartID, TagID
// FROM @temp
//
// IF ((@AssignToRelatedTag = 1) AND (@RelatedTagID IS NOT NULL))
BEGIN
// INSERT PartTagParts (PartID, TagID)
// SELECT PartTagParts.PartID, [@temp].TagID
// FROM @temp
// , PartTagParts
// WHERE PartTagParts.TagID = @RelatedTagID
// AND PartTagParts.PartID != @PartID
// END
// END
//
RS.Parameters.Clear;
RS.Parameters.CreateParameter('@PartID',ftInteger,pdInput,4,PartID);
RS.Parameters.CreateParameter('@Tag',ftString,pdInput,24,Tag);
RS.Parameters.CreateParameter('@TagDescription',ftString,pdInput,24,TagDescription);
RS.Parameters.CreateParameter('@CreateNewtagCode',ftBoolean,pdInput,1,CreateNew);
if NOT CreateNew then
begin
RS.Parameters.CreateParameter('@TagCodeID', ftGUID,pdInput, 38,
GUIDToString(TagCodeGUID));
// RS.Parameters.CreateParameter('@TagCode', ftString,pdInput, 32,
null);
end
else
begin
// RS.Parameters.CreateParameter('@TagCodeID', ftGUID,pdInput, 38,
null);
RS.Parameters.CreateParameter('@TagCode', ftString,pdInput, 32,
NewTagCode);
end;
RS.Parameters.CreateParameter('@AssignToRelatedTag',ftBoolean,pdInput,1,AssignRelated);
if AssignRelated then
RS.Parameters.CreateParameter('@RelatedTagID',ftGUID,pdInput,38,GUIDToString(RelatedTagGUID));
RS.ExecProc;
finally
RS.Free;
end;
end;
When I viewed the execution of the stored procedure in the SQL Server
Profiler, I see the following line:
exec IC0010_AddTagForPartID
186777,'TEST123','TEST123',0,'32AE791B-1053-DF11-B9DE-00155DFF0119',0
What I was hoping to see was something like:
exec IC0010_AddTagForPartID @PartID=186777,
@Tag='TEST123',@TagDescription='TEST123', @CreateNewtagCode=0,
@TagCodeID='32AE791B-1053-DF11-B9DE-00155DFF0119', @AssignToRelatedTag=0
Because it appears that ADO builds the stored procedure without the name
field defined and just the values, I have to pass all the parameters even if
I don't need them. So in the above code, I have to uncomment the two rows
and pass NULL depending on what I really want.
Is there a command or setting in ADO that I can turn on that will tell it to
pass the parameter names defined in the CreateParameter call?