Creating dynamic sql stored procedure

Question

Creating dynamic sql stored procedure

chandrashekar.2512

SSC Eights!

Points: 965
More actions
January 4, 2010 at 1:04 am

#98435

Dear all,
I need to create dynamic stored procedure in which,
i need to select id from one table,should be passed to where condition of another sql statement. This should work until all the id from first sql statement should be passed to the next sql statement, means second sql statement work inside the looping of first sql statement.
Please somebody tell me how do i loop first table reocrd and pass the value to the next sql statement for where condition.
Regards
Chandrashekar

Viewing 12 posts - 1 through 11 (of 11 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Can you provide some sample table structures, data, and code you have tried? See the first link in my signature line for how to do this.

Looping is usually not the best way to do things SQL Server so if you provide what I asked for you may get a better performing set-based solution.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

chandrashekar.2512 (1/4/2010)
Dear all,
I need to create dynamic stored procedure in which,
i need to select id from one table,should be passed to where condition of another sql statement. This should work until all the id from first sql statement should be passed to the next sql statement, means second sql statement work inside the looping of first sql statement.
Please somebody tell me how do i loop first table reocrd and pass the value to the next sql statement for where condition.
Regards
Chandrashekar

What's wrong with using a JOIN?

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Agree I do, with Jack.

Please provide the table structure, sample data, expected results, and what you have done so far to solver your problem.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Lynn Pettis (1/4/2010)
Agree I do, with Jack.
Please provide the table structure, sample data, expected results, and what you have done so far to solver your problem.

oot thgir etiuq!

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

chandrashekar.2512 SSC Eights! Points: 965 More actions · Answer 5

Thanks Jack,

I have attached my table and structure details in as txt file. please find it.

As you told i have created tables structure, since i have 5 to 6 tables as per my knowledge i have assigned the values.

Thing is, i have to select records of one bank whose transaction happened before 31st march 2009 from transaction table based on the account no,branchcode,district code,statecode.

And the details of the process, what exactly need to happen is written in helpchanges.txt file.

Please help me in creating dynamic stored procedure for finacial year transaction.

Thanks & Regards

Chandrashekar

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 6

Would i be correct in assuming that this is course/school work ?

What exactly do you mean by 'dynamic' stored procedure ? How does that differ from a normal stored procedure.

Clear Sky SQL
My Blog[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

chandrashekar.2512 (1/5/2010)
Thanks Jack,
I have attached my table and structure details in as txt file. please find it.
As you told i have created tables structure, since i have 5 to 6 tables as per my knowledge i have assigned the values.
Thing is, i have to select records of one bank whose transaction happened before 31st march 2009 from transaction table based on the account no,branchcode,district code,statecode.
And the details of the process, what exactly need to happen is written in helpchanges.txt file.
Please help me in creating dynamic stored procedure for finacial year transaction.
Thanks & Regards
Chandrashekar

This is a reasonable starting point. Now, how about upping the stakes by designing a single query which will return, from your transactions table, a single period's worth of data which you want to INSERT into the tran history table. You should include joins to other tables where necessary.

What's the point of doing this? Well, there are two really.

Firstly it will allow respondents to roughly assess your knowledge of TSQL so answers to your questions are scoped appropriately.

Secondly it will demonstrate your own commitment to the project. There are at least two steps here, an extract and an insert, with some conditional processing in between i.e. it's ETL (Extract, Transform, Load). It's more than just a simple query and will take several stages of help.

Cheers

ChrisM

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

chandrashekar.2512 SSC Eights! Points: 965 More actions · Answer 8

Hi Chris,

i am really sorry i did not get your point, But one thing i would like to tell you is i am not an expert in sql, i am not having adequate knowledge but i am inspired and eager to to work on this technology. i am asking just an help to how i can proceed to create a procedure which accomplishes my task.

I am gaining knowledge from this website, and putting my quires to experts and getting inspired by there statements.

I want just an hint to proceed my work, and i hope experts like you will teach me the steps in solving my problem.

if i am going wrong, please guide me.

Thanks

Chandrashekar

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 9

Chander ,

frankly i have got you requirement clearly but if you want to know about dynamic sql building and iteration with WHILE loop here is the simple example .

declare @counter int, @txt_StateCode int, @txt_DistrictName varchar(200), @maxcount int

select @maxcount = max(txt_StateCode) from [Tbl_State]

select top 1 @txt_StateCode = txt_StateCode from [Tbl_State]

while @txt_StateCode < = @maxcount

begin

select txt_DistrictName from [Tbl_District] where txt_StateCode = @txt_StateCode

set @txt_StateCode = @txt_StateCode + 1

end

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

chandrashekar.2512 (1/5/2010)
Hi Chris,
i am really sorry i did not get your point, But one thing i would like to tell you is i am not an expert in sql, i am not having adequate knowledge but i am inspired and eager to to work on this technology. i am asking just an help to how i can proceed to create a procedure which accomplishes my task.
I am gaining knowledge from this website, and putting my quires to experts and getting inspired by there statements.
I want just an hint to proceed my work, and i hope experts like you will teach me the steps in solving my problem.
if i am going wrong, please guide me.
Thanks
Chandrashekar

Hi Chandrashekar

Thanks for the explanation, it's very helpful to anybody who is inclined to help you. Let's do this step by step as you suggest. You can study each step to better understand it.

Here's the first step: Extract the data you want to work with from the transactions table.

Start with a simple SELECT ... FROM ... WHERE ...

Do this yourself, and post the code back here so people can see it. Make your own comments about the results.

Cheers

ChrisM

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 11

In addition to what Chris has said, you also need to know that in the FROM you can have multiple tables JOIN'ed together, typically this is on a Foreign Key column like in your case you could do something like:

FROM

dbo.Tbl_State AS S INNER JOIN

dbo.Tbl_District AS D ON

S.txt_StateCode = D.txt_StateCode INNER JOIN

dbo.Tbl_AccountNoMaster AS TANM ON

D.txt_DistrictCode = TANM.txt_DistrictCode INNER JOIN

dbo.Tbl_Transaction AS TT ON

TANM.int_BranchCode = TT.int_BranchCode AND

TANM.int_RID = TT.int_RID AND

TANM.txt_AccountNO = TT.txt_AccountNO AND

TANM.txt_DistrictCode = TT.txt_DistrictCode

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question