Using exec on stored procs w/ variable args
-
So, I need to be able to execute a variety of stored procedures in another stored procedure. Obviously, the stored procs I'm executing have a variable number of args. If I do something like:
exec @ret = @proc @args
I get all the args submitted to the first arg regardless of how I format the string (ie set @args = '@param1=''asdf'',' + '@param2=''fdas'',' + '@param3 = 1' or simply putting them in ordinal order...)
Basically, the proc names are stored in one table, the args for each instance of the procs are stored in another table and in the controlling proc I'm simply running a cursor through the args table to try and execute each proc with the appropriate args.
Any ideas how I can do this? Thanks in advance!
-
Hey... tk, check at BOL the correct syntax of EXEC...
You musn't pass a single string with parameters, you have to pass parameter's values for each parameter...
so EXEC @rc = SomSP @P1, @P2
or
EXEC @rc = @Proc @P1, @P2 (but i`m not sure aboout this last...)
I'd prefer using a system's sp... sp_executesql... check this...
Use a varchar variables to hold the PARAM's definition string...
so
SET @params = ' @Param1 INT, @Param2 VARCHAR(10), @Param3 DATETIME '
After, use a variable for each parameter... (just to be clear, you can actually use the real values instead of variables but I always prefer variables, also, I've always had trouble passing dattime values as parameters)
so...
SET @P1 = 1
SET @P2 = 'HOLA'
SET @P3 = GetDate()
and finally... execute the SP...
EXECUTE @RESULT = sp_executesql @PROC, @params, @P1, @P2, @P3
an that's all... don't forget to DECLARE the variables...
-
Thanks for the reply Nicolas. But, I think you're just confirming what I was afraid of. I have no problem making it work if the number of params is known. However, the environmnet I've been dealt has several hundred procs with anywhere from 0 to 50+ params.
I guess I'll either have to create a controlling proc that has templates for calls to procs with 0 to 50+ params, or come up with a different solution. Unfortunately, I don't have a lot of flexibility as to what I can implement with this particular customer.
-
Not knowing how your system is setup but you could try something like this
DECLARE @sql varchar(8000)
SET @sql = null ' Need to do this at top of each loop
SELECT @sql = COALESCE(@sql+','+param,param)
FROM [paramtable]
WHERE procname = 'proc1'
SET @sql = 'proc1 '+@sql
EXEC(@sql)
Far away is close at hand in the images of elsewhere.
Anon. -
Yup, so, you can put it all into one single string and execute it using EXEC(@MyVar)
And that, would by simple just using a select statment
Something like that, obvoiously into a cursor that iterates over your PROCS Defintion table
WHILE 1=1
FETCH NEXT FROM MyCursor INTO @ProcId, @ProcName
IF @@FETCH_STATUS <> 0 BREAK
SET @MyVar = @ProcName
SELECT @Procname = @ProcName + ISNULL( ',' + ParamName + ' = ' + Value, '')
FROM ParamsDefinitionTable
WHERE ProcID = @ProcID
EXEC(@MyVar)
END
I don´t know if you values are on the same table or not... but, well, it's just an example...
Hope you've luck
Bye
Nicolas
-
Ok, I see the error of my ways now... What I've been trying to do is what is being suggest, I've just been using the wrong syntax. Newbie mistake I guess...
As in my original post, I've been trying to do:
exec @ret = @proc @params
What I failed to mention was that I also tried many permutations like:
exec @procAndParams
exec @ret = @procAndParams
etc...
What I didn't try was:
exec(@procAndParams)
and the other permutations using the parens....
The original code I wrote will work fine if I add parens to the exec call. I guess I don't remember seeing this documented anywhere, but I suppose if I really knew what I were doing, I would already know this.
For example (and others who may not know this), if I have a proc named aaa_sptest and the strings below represent the strings built in the loop that retrieves the proc name, the params and the values:
declare @a varchar(200)
declare @b-2 varchar(200)
set @a = 'aaa_sptest @param1=''a s d f'',' + '@param2=''f d a s'',' + '@param3 = 1'
set @b-2 = 'aaa_sptest ''a s d f'',' + '''f d a s'',' + '1'
These both work:
exec(@a)
exec(@b)
These don't:
exec @a
exec @b-2
Thanks for the help!
-
I'll post this, and label it, the 0:N optional parameter problem.
There is a small performance hit to use this method. I usually use this method for reporting, since the User may or may not select alot of different items.
If you're a dot net developer, you can use a strongly typed dataset to create the Xml for you, which is what I do.
This will create 3 tables
Emp, Dept, and the link table Emp_Dept
--drop table emp_dept
--drop table dept
--drop table emp
create table dbo.emp(empid int not null primary key, lastname varchar(35) not null , firstname varchar(35) not null , dateofbirth datetime null )
create table dbo.dept(deptid int not null primary key, deptname varchar(35) not null unique )
create table dbo.emp_dept(empid int not null references emp(empid), deptid int not null references dept(deptid), primary key(empid,deptid))
raiserror('Now add data ....',0,1)
GO
set nocount on
insert dbo.dept(deptid,deptname) values(101,'finance')
insert dbo.dept(deptid,deptname) values(102,'netops')
insert dbo.dept(deptid,deptname) values(103,'software development')
insert dbo.dept(deptid,deptname) values(104,'custodial')
insert dbo.dept(deptid,deptname) values(105,'sales')
insert dbo.emp(empid,lastname,firstname,dateofbirth) values(1,'turnip','tommy','1/1/1910')
insert dbo.emp(empid,lastname,firstname,dateofbirth) values(2,'jones','mary','2/2/1920')
insert dbo.emp(empid,lastname,firstname,dateofbirth) values(3,'tucker','dan','3/3/1930')
insert dbo.emp(empid,lastname,firstname,dateofbirth) values(4,'tucker','dan','4/4/1940')
insert dbo.emp(empid,lastname,firstname,dateofbirth) values(5,'cucumber','cindy','5/5/1950')
insert dbo.emp(empid,lastname,firstname,dateofbirth) values(6,'radish','randy','6/6/1960')
insert dbo.emp(empid,lastname,firstname,dateofbirth) values(7,'potato','patti','7/7/1970')
insert dbo.emp(empid,lastname,firstname,dateofbirth) values(8,'tomato','teddy','7/7/1980')
insert dbo.emp(empid,lastname,firstname,dateofbirth) values(9,'beet','burt','7/7/1990')
insert dbo.emp_dept(empid,deptid) values(1,101)
insert dbo.emp_dept(empid,deptid) values(1,102)
insert dbo.emp_dept(empid,deptid) values(1,103)
insert dbo.emp_dept(empid,deptid) values(2,102)
insert dbo.emp_dept(empid,deptid) values(2,103)
insert dbo.emp_dept(empid,deptid) values(3,101)
insert dbo.emp_dept(empid,deptid) values(3,103)
insert dbo.emp_dept(empid,deptid) values(4,105)
insert dbo.emp_dept(empid,deptid) values(4,104)
insert dbo.emp_dept(empid,deptid) values(5,102)
insert dbo.emp_dept(empid,deptid) values(5,105)
insert dbo.emp_dept(empid,deptid) values(6,101)
insert dbo.emp_dept(empid,deptid) values(7,105)
insert dbo.emp_dept(empid,deptid) values(8,102)
insert dbo.emp_dept(empid,deptid) values(9,104)
--select
-- *
--from
-- emp e inner join emp_dept ed on e.empid = ed.empid inner join dept d on ed.deptid = d.deptid
raiserror('Now at the indexes ....',0,1)
GO
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'IX_emp_empid')
DROP INDEX emp.IX_emp_empid
CREATE INDEX IX_emp_empid ON emp (empid)
GO
--**********************
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'IX_dept_deptid')
DROP INDEX dept.IX_dept_deptid
CREATE INDEX IX_dept_deptid ON dept (deptid)
GO
--**********************
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'IX_emp_dept_empid')
DROP INDEX emp_dept.IX_emp_dept_empid
CREATE INDEX IX_emp_dept_empid ON emp_dept (empid)
GO
--**********************
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'IX_emp_dept_deptid')
DROP INDEX emp_dept.IX_emp_dept_deptid
CREATE INDEX IX_emp_dept_deptid ON emp_dept (deptid)
GO
--**********************
raiserror('Done ....',0,1)
GO
raiserror('Now at the stored procedure ....',0,1)
GO
if exists (select * from sysobjects
where id = object_id('dbo.uspEmpDeptGet_OneToNParamsExample') and sysstat & 0xf = 4)
drop procedure dbo.uspEmpDeptGet_OneToNParamsExample
GO
--notice the procedure name
--"usp" prefix
--the "Entity" name is first (instead of the word "Get" (here, the entity name is EmpDept)
--then the action "Get"
CREATE Procedure dbo.uspEmpDeptGet_OneToNParamsExample( --the _OneToNParamsExample can be omitted from the usp name, its just there for example reasons
@parametersXML Text
)
AS
SET NOCOUNT ON
DECLARE @hdoc INT -- handle to XML doc
-- build a table (variable) to store the xml-based result set
DECLARE @empCount int
DECLARE @emps TABLE ( --used to track with employees you want
empid int
)
DECLARE @deptCount int
DECLARE @depts TABLE ( --used to track with depts you want
deptid int
)
declare @dateOfBirthAfter datetime --used to track with employees with birthdate after you want
declare @dateOfBirthBefore datetime --used to track with employees with birthdate before you want
--Start XML usage --
-- Only incur the penalty of XML parsing, if XML was specified
if (@parametersXML IS NOT NULL) AND (Datalength(@parametersXML) > 10 ) -- Only process the xml If the xml exists, and it has at least 10 chars. 10 is just a somewhat arbritrary number, saying, that an xml doc with <10 chars doesn't have a whole lot going for it || DataLength is used for Text datatype
BEGIN
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @parametersXML
INSERT INTO @emps
SELECT empid
FROM
OPENXML (@hdoc, '/ParametersDS/Emp', 2) WITH (
empid int 'EmpID')
INSERT INTO @depts
SELECT deptid
FROM
OPENXML (@hdoc, '/ParametersDS/Dept', 2) WITH (
deptid int 'DeptID')
SELECT @dateOfBirthBefore = DateOfBirthBefore
FROM
OPENXML (@hdoc, '/ParametersDS/SingleValueParam', 2) WITH (
DateOfBirthBefore datetime 'DateOfBirthBefore')
SELECT @dateOfBirthAfter = DateOfBirthAfter
FROM
OPENXML (@hdoc, '/ParametersDS/SingleValueParam', 2) WITH (
DateOfBirthAfter datetime 'DateOfBirthAfter')
--Remove the handle to the XML document, since we're done with using the xmlDoc
EXEC sp_xml_removedocument @hdoc
END
--End XML usage
select @empCount = count(*) from @emps
select @deptCount = count(*) from @depts
--select * from @emps
--select * from @depts
--print @dateOfBirthBefore
--print @dateOfBirthAfter
-------------------- Above are the variables and variable-tables for parameters
-------------------- Below is the actual query we're interested in
select
e.empid ,
e.lastname ,
e.firstname ,
e.dateofbirth ,
d.deptid ,
d.deptname
from
dbo.emp e inner join dbo.emp_dept ed on e.empid = ed.empid inner join dbo.dept d on ed.deptid = d.deptid
WHERE
((@empCount = 0) OR (e.empid IN ( Select empid from @emps ) ))
AND
((@deptCount = 0) OR (d.deptid IN ( Select deptid from @depts ) ))
AND
(( @dateOfBirthBefore IS NULL ) OR (e.dateofbirth <= @dateOfBirthBefore ))
AND
(( @dateOfBirthAfter IS NULL ) OR (e.dateofbirth >= @dateOfBirthAfter ))
ORDER BY
e.lastname , e.firstname
GO
if exists (select * from sysobjects
where id = object_id('dbo.uspEmpColorGet') and sysstat & 0xf = 4)
drop procedure dbo.uspEmpColorGet
GO
--notice the procedure name
--"usp" prefix
--the "Entity" name is first (instead of the word "Get" (here, the entity name is EmpDept)
--then the action "Get"
CREATE Procedure dbo.uspEmpColorGet--()
----( --the _OneToNParamsExample can be omitted from the usp name, its just there for example reasons
--)
AS
SET NOCOUNT ON
SELECT empid as EmpID, colorid as ColorID, isUsedOnEmpHouse as isUsed
FROM emp_color as EmpColor
SET NOCOUNT OFF
GO
--================= START EXAMPLES (below code are examples)
--no parameters
print 'No Filters, just give me all the data'
EXEC uspEmpDeptGet_OneToNParamsExample '
<ParametersDS>
</ParametersDS>
'
GO
--birth day (after)
print 'Filter on the birthday being after the input date'
EXEC uspEmpDeptGet_OneToNParamsExample '
<ParametersDS>
<SingleValueParam>
<DateOfBirthAfter>2/2/1950</DateOfBirthAfter>
</SingleValueParam>
</ParametersDS>
'
GO
--birth day (before)
print 'Filter on the birthday being before the input date'
EXEC uspEmpDeptGet_OneToNParamsExample '
<ParametersDS>
<SingleValueParam>
<DateOfBirthBefore>2/2/1950</DateOfBirthBefore>
</SingleValueParam>
</ParametersDS>
'
GO
--birth day (both)
print 'Filter on the birthday being (before and after) the input dates'
EXEC uspEmpDeptGet_OneToNParamsExample '
<ParametersDS>
<SingleValueParam>
<DateOfBirthBefore>12/31/1960</DateOfBirthBefore>
<DateOfBirthAfter>1/1/1940</DateOfBirthAfter>
</SingleValueParam>
</ParametersDS>
'
GO
--Emps and Depts
print 'Specific Emps and Depts'
EXEC uspEmpDeptGet_OneToNParamsExample '
<ParametersDS>
<Emp>
<EmpID>1</EmpID>
</Emp>
<Emp>
<EmpID>2</EmpID>
</Emp>
<Dept>
<DeptID>101</DeptID>
</Dept>
<Dept>
<DeptID>103</DeptID>
</Dept>
</ParametersDS>
'
GO
--just Emps
print 'Filter on specific employees'
EXEC uspEmpDeptGet_OneToNParamsExample '
<ParametersDS>
<Emp>
<EmpID>2</EmpID>
</Emp>
<Emp>
<EmpID>1</EmpID>
</Emp>
</ParametersDS>
'
GO
--just Depts
print 'Filter on specific depts'
EXEC uspEmpDeptGet_OneToNParamsExample '
<ParametersDS>
<Dept>
<DeptID>101</DeptID>
</Dept>
<Dept>
<DeptID>103</DeptID>
</Dept>
<Dept>
<DeptID>105</DeptID>
</Dept>
<SingleValueParam>
<DateOfBirthBefore>12/31/2010</DateOfBirthBefore>
<DateOfBirthAfter>1/1/1900</DateOfBirthAfter>
</SingleValueParam>
</ParametersDS>
'
GO
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply