Post reply

Creating dynamic SP(date changes)

  • September 25, 2012 at 1:26 am

    #262194

    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

  • September 25, 2012 at 1:40 am

    #1541682

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

  • September 25, 2012 at 1:53 am

    #1541689

    prasan.dash83 (9/25/2012)

    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

    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

  • September 25, 2012 at 2:06 am

    #1541692

    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

  • September 25, 2012 at 2:13 am

    #1541695

    prasan.dash83 (9/25/2012)

    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

    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

    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

  • September 25, 2012 at 3:26 am

    #1541722

    prasan.dash83 (9/25/2012)

    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

    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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply