Creating dynamic Stored Procedures(changing dates)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Creating dynamic Stored Procedures(changing...

Creating dynamic Stored Procedures(changing dates)

Rate Topic

Display Mode

Topic Options

Author

Message

prasan.dash83

Posted Tuesday, September 25, 2012 1:12 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 15, 2013 12:27 AM
Points: 6, Visits: 7

Hi Guys,

I have an MIS system where I have to run 15 queries and update 15 sheets which takes a lot of time as I need to make changes in the dates present in the queries, say 3 date variables in each query which have to be updated to the current date.

I understand that creating dynamic SP can solve my problem but I need some guidance on them.

Can any of you suggest on my problem?

Thanks,
Prasanna

Post #1363822

t.brown 89142

Posted Tuesday, September 25, 2012 1:28 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 9:02 AM
Points: 114, Visits: 140

I'm sure there would be a way of passing the required dates as a variable to the stored procedures., or, if it is always the current date, why not use the GETDATE() function.
If these two ideas can't work, then post one of your simpler stored procs, with an explanation of the dates you need to change.

Post #1363826

ChrisM@Work

Posted Tuesday, September 25, 2012 1:55 AM

SSCertifiable

Group: General Forum Members
Last Login: Thursday, May 23, 2013 9:27 AM
Points: 5,618, Visits: 10,990

Duplicate post - please reply to
http://www.sqlservercentral.com/Forums/FindPost1363825.aspx

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps

Post #1363843

prasan.dash83

Posted Tuesday, September 25, 2012 2:05 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 15, 2013 12:27 AM
Points: 6, Visits: 7

Thanks for the quick reply t.brown.

I don't have stored procedures, what I have are a set of create table statements having Select queries with multiple conditions. Some of the conditions include filters for the current date like:

'Create table emp_hrs as
(select count(emp_hours) from Emp_table where emp_name='BOB' and func_date='2012-09-19')

And there are multiple insert statements to create one table by pulling out the data from multiple tables as created above.

I have to use a 'select * from' statement to pull out the data from the final table.

I hope you get a picture of this.

Thanks,
Prasanna

Post #1363844

t.brown 89142

Posted Tuesday, September 25, 2012 2:54 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 9:02 AM
Points: 114, Visits: 140

Yes dynamic SQL would seem appropriate.

Basically you create your entire SQL Statement in a string then call EXEC on it.

e.g.


DECLARE @SQL Varchar(MAX);

DECLARE @TODAY Varchar(10);
SELECT @TODAY = CONVERT( VARCHAR(10),GETDATE(), 120);

SET @SQL = 'Create table emp_hrs as 
(select count(emp_hours) from Emp_table where emp_name=''BOB'' and func_date=''' + @TODAY + ''')'

EXEC (@SQL)

You can then chain all 15 of these together into a single stored procedure.

Post #1363863

ChrisM@Work

Posted Tuesday, September 25, 2012 3:07 AM

SSCertifiable

Group: General Forum Members
Last Login: Thursday, May 23, 2013 9:27 AM
Points: 5,618, Visits: 10,990

t.brown 89142 (9/25/2012)

Yes dynamic SQL would seem appropriate.

Basically you create your entire SQL Statement in a string then call EXEC on it.

e.g.


DECLARE @SQL Varchar(MAX);

DECLARE @TODAY Varchar(10);
SELECT @TODAY = CONVERT( VARCHAR(10),GETDATE(), 120);

SET @SQL = 'Create table emp_hrs as 
(select count(emp_hours) from Emp_table where emp_name=''BOB'' and func_date=''' + @TODAY + ''')'

EXEC (@SQL)

You can then chain all 15 of these together into a single stored procedure.

Post #1363868

prasan.dash83

Posted Tuesday, September 25, 2012 3:26 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 15, 2013 12:27 AM
Points: 6, Visits: 7

Thanks a lot, will try this....

Post #1363878

ChrisM@Work

Posted Tuesday, September 25, 2012 3:34 AM

SSCertifiable

Group: General Forum Members
Last Login: Thursday, May 23, 2013 9:27 AM
Points: 5,618, Visits: 10,990

ChrisM@Work (9/25/2012)

t.brown 89142 (9/25/2012)

Yes dynamic SQL would seem appropriate.

Basically you create your entire SQL Statement in a string then call EXEC on it.

e.g.


DECLARE @SQL Varchar(MAX);

DECLARE @TODAY Varchar(10);
SELECT @TODAY = CONVERT( VARCHAR(10),GETDATE(), 120);

SET @SQL = 'Create table emp_hrs as 
(select count(emp_hours) from Emp_table where emp_name=''BOB'' and func_date=''' + @TODAY + ''')'

EXEC (@SQL)

You can then chain all 15 of these together into a single stored procedure.

Duplicate post - please reply to
http://www.sqlservercentral.com/Forums/FindPost1363825.aspx

I give up!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

Post #1363884

« Prev Topic | Next Topic »

Permissions