Vipin Ellat
asked on
Pass the result of sql query as a parameter
Hi Experts,
I have one stored procedure to update the control table which contains two fields (Table_Name & Timestamp) to know the details of tables updated through SSIS . But now,I have a new requirement to add one more field (ie.Latest Data) which is a date field to get the max(date ) from each table everyday.These are for daily sheduled job.After the execution of each job,the stored procedure for update control table is called through "EXECUTE SQL TASK" in SSIS.So now,I want to pass the result of an sql query (select max(date) from order) as a parameter through execute sql task .Already a parameter is there to pass the table name to stored procedure.
Thanks in Advance
I have one stored procedure to update the control table which contains two fields (Table_Name & Timestamp) to know the details of tables updated through SSIS . But now,I have a new requirement to add one more field (ie.Latest Data) which is a date field to get the max(date ) from each table everyday.These are for daily sheduled job.After the execution of each job,the stored procedure for update control table is called through "EXECUTE SQL TASK" in SSIS.So now,I want to pass the result of an sql query (select max(date) from order) as a parameter through execute sql task .Already a parameter is there to pass the table name to stored procedure.
Thanks in Advance
Vitor Montalvão
If you already passing the table name then let the SP to retrieve the max(date) so you won't need to pass the query.
agreed with Vitor, from my point of view, you no need to do much thing in your SQL task. But instead do your all stuffs in your stored procedure is good enough, including the one to update the max date.
ASKER
I am using the same stored procedure for several tables( ie. Several SSIS Packages).So I need to pass the query as a parameter from execute sql task while calling stored procedure
So what other tables will pass for this parameter?
ASKER
For eg : select max(date) from sales for sales table
select max(date) from credit for credit table
When I execute the sql task for each package, I need to update the corresponding table to know the latest data date from each table.
Ie. If sales table package is executed,then the task update control is executed where we call our stored procedure for updating the control table.
I already passed one parameter to send the table name,but now I want to send the max (date ) of that table for that day.
The job is running as daily shedule.
select max(date) from credit for credit table
When I execute the sql task for each package, I need to update the corresponding table to know the latest data date from each table.
Ie. If sales table package is executed,then the task update control is executed where we call our stored procedure for updating the control table.
I already passed one parameter to send the table name,but now I want to send the max (date ) of that table for that day.
The job is running as daily shedule.
That's why I told you. If you already have the table name passed into the SP, just use it to execute the select max(date). Example:
CREATE PROCEDURE sp_name (@tablename varchar(32))
declare @maxdate datetime
declare @sql varchar(max)
set @sql = 'select max(date) from ' + @tablename
exec(@sql)
GOASKER
Ok.It may work.I will check .I need to pass the date field also since date field is different for each table.Thanks
ASKER
I am not getting the query result for the third parameter.Sending the SP here. I need to insert the third field as result of query ( select max(sales_date) from sales)
ALTER PROCEDURE [dbo].[SP_CONTROL_LOAD] (@Table_Name varchar(50),@sales_date varchar(50))
AS
BEGIN
Declare @sql varchar(max)
set @sql = 'select max('+@sales_date+') from ' + @Table_Name
exec(@sql)
INSERT dbo.CONTROL
VALUES (@Table_Name,getdate(),------------------------)
END
ALTER PROCEDURE [dbo].[SP_CONTROL_LOAD] (@Table_Name varchar(50),@sales_date varchar(50))
AS
BEGIN
Declare @sql varchar(max)
set @sql = 'select max('+@sales_date+') from ' + @Table_Name
exec(@sql)
INSERT dbo.CONTROL
VALUES (@Table_Name,getdate(),------------------------)
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Recommendation to close this question by accept the above comment as solution.