Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2009
    Posts
    168

    Unanswered: stored procedure loop

    Hi, I have 2 variables @query1, @query2. I want to use a loop to select the query as the loop goes round, for instance, when x=1, I want @query to be 'select name'. When am using this code, its outputting '@query1', '@query2' as strings but not assigning the variable @query to @query1 or @query2. Any idea how to do that please? Thanks.

    Code:
    set @query1 = 'select name'
    set @query2 = 'select address'
    BEGIN TRAN
    		while (@x<=2)
    		begin
    		select @query = '@query' + cast(@x as varchar)
    		print @query
    		set @x = @x + 1
    		print @x
    		end

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you clarify what you by providing some sample data and required output please?

    Your code as posted is a bit of a mess and doesn't show what you're realy aiming for.
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2009
    Posts
    168
    thanks for the reply.

    sorry I don't have sample data or output at the moment. I just need to know how to assign the select statements to variable @query in the loop so that I can create dynamic SQL queries. Its a bit along these lines that am looking except that am not passing any parameters to the procedure but am creating the queries dynamically using the loop: basically i need a way to create dynamic sql statements to use in queries.
    4GuysFromRolla.com - Running Dynamic Stored Procedures

    the question simplified is how do i make these dynamically:
    @query = @query1 = 'select address from aTable'
    @query = @query2 = 'select name from aTable'

    Am i a bit clearer or just totally messed up here

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Where has the loop come from?

    Why are you writing dyanmic SQL? Normal SQL is nicer
    George
    Home | Blog

  5. #5
    Join Date
    Jul 2009
    Posts
    168
    BEGIN TRAN
    set @newStudentID = 1
    set @financeEntityID = (SELECT financeEntityID FROM FinanceEntity WHERE (name LIKE N'payment plan'))

    well i have a statement like the above. Now I need to do similar searches for 5 statements:
    - SELECT financeEntityID FROM FinanceEntity WHERE (name LIKE N'payment method')
    - SELECT financeEntityID FROM FinanceEntity WHERE (name LIKE N'deposit')
    - SELECT financeEntityID FROM FinanceEntity WHERE (name LIKE N'debit order')

    I will have to write all these 5 times plus the insert statements that come after so i thought why not build the query like we normally do in programming languages. So that's why I thought of creating the loop to go through the different keys but using the same basic sql statement. Any other way you might suggest?

    2) Also, I have another problem with the stored procedure. I have one stored procedure that gets the newly inserted row id of a table but have another procedure that needs this value. How do you transfer this value between each other?

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    --Find your financeEntityIDs:
    SELECT financeEntityID
    FROM   FinanceEntity
    WHERE  name IN (N'payment plan', N'payment method', N'deposit', N'debit order')
    
    --Now I assume that you're going to take these values and insert them in to a new table alongside the @newStudentID
    -- So how does this look?
    DECLARE @newStudentID int
        SET @newStudentID = 1
    
    INSERT INTO dbo.your_table (student_id, finance_entity_id)
    SELECT 1 As student_id
         , financeEntityID
    FROM   FinanceEntity
    WHERE  name IN (N'payment plan', N'payment method', N'deposit', N'debit order')
    As for your second question, I advise you either wait till we've solved the first problem or start a separate thread
    George
    Home | Blog

  7. #7
    Join Date
    Jul 2009
    Posts
    168
    Thanks for your prompt reply. Great piece of code although am not too familiar with TSQL. So what this does I understand is:

    - insert '1' as student_id and the appropriate ids into finance_entity_id

    yes you assumed it correctly that they are to be inserted in another table. So at the moment only the value '1' is inserted as the student_id right? I guess one problem is solved. Let me try out the code you pointed out. Many thanks.

    I was thinking of using functions in sql server to store the parameter @newStudentID or in vb.net try getting the output parameter then passing it back to the other procedure. I am not sure if that is how is normally done. Let me know if I need to post a new thread on it. thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •