Creating dynamic SP(date changes)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Creating dynamic SP(date changes)

Creating dynamic SP(date changes)

Rate Topic

Display Mode

Topic Options

Author

Message

prasan.dash83

Posted Tuesday, September 25, 2012 1:26 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 #1363825

saltpepo

Posted Tuesday, September 25, 2012 1:40 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, December 02, 2012 6:21 AM
Points: 16, Visits: 64

I think using sp_executesql can help out.Sp_executesql mainly used for dynamic store procedure.

Post #1363833

ChrisM@Work

Posted Tuesday, September 25, 2012 1:53 AM

SSCertifiable

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

prasan.dash83 (9/25/2012)

Can you post one of the queries? It would hugely increase your chance of a good solution.

^{“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 #1363842

prasan.dash83

Posted Tuesday, September 25, 2012 2:06 AM

Forum Newbie

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

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 #1363845

ChrisM@Work

Posted Tuesday, September 25, 2012 2:13 AM

SSCertifiable

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

prasan.dash83 (9/25/2012)

Not really, it's still vague.

prasan.dash83 (9/25/2012)

...I have an MIS system where I have to run 15 queries ...

Can you please post one of the 15 queries? All of it if you can. The CREATE TABLE statement above won't parse - it's not valid SQL syntax.

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

Post #1363848

OTF

Posted Tuesday, September 25, 2012 3:26 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 23, 2013 10:37 AM
Points: 257, Visits: 3,722

prasan.dash83 (9/25/2012)

Your question is not as clear as it could be but could it be that you are actually referring to creating a Stored Procedure with Parameters as opposed to the use of dynamic Sql?

If its the former, then some pseudo code follows:

Create Procedure [ProcedureName]
@Parameter [DataType]
AS
SELECT FROM TABLE WHERE [ColumnName]=@Parameter

Post #1363879

« Prev Topic | Next Topic »

Permissions