Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creating dynamic Stored Procedures(changing dates)


Creating dynamic Stored Procedures(changing dates)

Author
Message
prasan.dash83
prasan.dash83
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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
t.brown 89142
t.brown 89142
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 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.
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8957 Visits: 19016
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
prasan.dash83
prasan.dash83
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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
t.brown 89142
t.brown 89142
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 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.
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8957 Visits: 19016
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

“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
prasan.dash83
prasan.dash83
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 7
Thanks a lot, will try this....
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8957 Visits: 19016
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

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search